Posts tagged MS SQL Server 2008
Greetings. It’s been so long since I’ve even touch this blog that I write this first line with a touch of shame.
Ok… I’m over it now. 😉
I’ve been working heavily with SQL Server 2008 and with DB2 for the last couple of years. I really love my job.
Lately we’ve been working on a data warehouse project which is structured for reporting. This presented a new concept in my lap which at first I didn’t find to hard to comprehend, we I still don’t really, but it threw a wrench into the wheel I’ve been accustomed to.
– Data denormalization –
Writing SQL code for many years, for me, has been about creating relational databases where data is divided up and joined together in queries. Denormalization brings forth the concept of doubling data for the purpose of having it readily available. (Fast)
So the thought process has been different. Writing SQL jobs that will suck in data in the format that we require to build our warehouse (CUBE – Ready). I guess what this method really seems to be is a space vs time trade off. You have much more data, doubled up… but the result is that you can query it very fast.
The cube my co-worker created was really really fast. Slicing and dicing the data at the blink of an eye.
Data is only as good as it’s usability.
I’ve been using Reporting Services a lot. As with many things I learn on a “needs” basis.
Something I’ve done quite a bit of is have to run counts on the data returned by datasets. Something that stumped me early on when I started was how to run a count against data that was returned based on dates.
It was actually nice and simple:
=SUM(IIF(Fields!DateField.Value = Today(),1,0))
In this example what we’re basically doing is checking if the date is equal to “today”, if it is it’s given a value of 1, if not then 0. And of course, it’s summed up. So this would give us the SUM() of dates equal to today.
A variation of this conditional counting (and something I’ve had to do) is to run a comparison like this against a date and a “non date” value.
So in this example our Fields!DateField.Value is actually stored as an integer. So we need to convert the integer to a date to have it count properly.
=SUM(IIF(new DateTime(Fields!WOODDT.Value / 10000, (Fields!WOODDT.Value / 100) mod 100, Fields!WOODDT.Value mod 100) = Today(),1,0))
So in the above example, we are creating a datetime type with our DateTime() function and then comparing it to the Today() value. If the newly created date value (which is again based off of the data returned from the dataset) is equal to today’s date it is given the value of 1, if not then 0. And this is summed up.