Posts tagged MS SQL Server 2008

Data warehousing and denormalization


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.

Counting rows using conditional dates in Reporting Service


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.

Truncate a table on a remote server in MS SQL Server 2008


I’ve been learning alot since I’ve been in my new job and I must say I love it.

I’m working with a completely different set of tools. For instance, most of the development I will be doing will be in .NET and looking in the occasional Delphi apps.

Over the years I’ve worked with MySQL alot and with MS SQL Server a bit (2000 and 2005).

In this position, I work with MS SQL Server 2008. And most of what I do is using stored procedures which is a switch from most of the solely web based apps I’ve done in the past.

I’ve been learning many aspects of MS SQL Server and how to work with link servers. While I’ve been learning, I’m only scratching the surface of what MS SQL can do.

So working with a linked server, I needed to truncate a table and fill it with data from another server.

I’m not sure this is the most elegant way, but this is what I came up with.

— Truncate the acuheader table
EXEC(‘TRUNCATE TABLE [Test_DB].dbo.thisTable’) AT [Link-Server]
— insert the data
INSERT OPENQUERY([Link-Server],‘SELECT * FROM [Test_DB].dbo.thisTable’)
FROM [Test_DB].dbo.thisTable

For those of you who haven’t used TRUNCATE, it is a much more efficient way to remove all table rows.

Basically, this combine with a Job allows to keep a table on another server in sync with another. Of course, you wouldn’t necessarily want to replicate your tables this way, the built in replication functionality is what you’d want for that.

But for the case this was used in, it seems to work fine and well.

I look forward to learning a lot more with MS SQL Server. It’s a powerful beast.

Go to Top