March 2012 Entries

Date Dimension: use date in YYYYMMDD format if your granular level is a single date

It still surprises me how many people set up the DateDimID as Identity(1, 1).If you use YYYYMMDD format, then on some reports, you can even display the date without navigating to the Date dimension to show label. In the same deal, you can use YYYYMM for a monthly date dimension, and YYYY for a yearly date dimension.[Update 2012-10-16]I now realize that what I said before was a clunky way to build dimension (hey I didn't say I was the expert. :D) And using only that argument going up against Kimball's ......

Posted On Wednesday, March 14, 2012 11:39 AM | Comments (0)

Data warehouse design principle, a very good post to always steer me back in the right direction

Whenever I start doubting my design of Data Mart or Cube, I always come back to this article and read it again.  It helps me keep my design simple.

http://dwbi1.wordpress.com/2010/06/17/one-or-two-dimensions/

Great post!

Related post:
http://dwbi1.wordpress.com/2011/02/26/denormalising-a-fact-table/

Posted On Wednesday, March 14, 2012 11:34 AM | Comments (0)

Database Hardware CPU Licensing vs Performance and Fast Track Data Warehouse Architecture

This is just one of those cool post which are not written as often as it should be. Hats off to Glenn Berry for stating it so clearly for all BI / database developers.http://sqlserver... read some Microsoft paper on "Fast Track Data Warehouse Architecture", and did not appreciate the benefit at the time. What a great way for developers to start with someone already figured out the licensing + cost + performance. There ......

Posted On Thursday, March 8, 2012 4:05 PM | Comments (0)

Database Model Diagram

Some very useful diagrams here.  It provides a basis point.  I see that it already does not really fit all project requirements, but this is a really nice starting point, and a good check on business entity relationship.

http://www.databaseanswers.org/data_models/index.htm

Posted On Wednesday, March 7, 2012 12:13 PM | Comments (0)

Ways to search for column name, table name, or stored procedure text in SQL

This is nothing new, just a way for me to organize the information better. Personally, I like the first 2 ways better just because I can order the result set any way I want.USE {Database Name}GO-- SQL 2008, search table nameSELECT IS_C.COLUMN_NAME , IS_C.* FROM INFORMATION_SCHEMA.COLUMNS AS IS_CWHERE IS_C.TABLE_NAME = '{Table Name}'ORDER BY IS_C.COLUMN_NAME -- , ORDINAL_POSITION, IS_NULLABLE, DATA_TYPE-- SQL 2008, search column nameSELECT IS_C.COLUMN_NAME , IS_C.* FROM INFORMATION_SCHEMA.COLUMNS ......

Posted On Tuesday, March 6, 2012 11:38 AM | Comments (0)

Enterprise Library for Windows Azure with Autoscaling

Hurray for the Enterprise Library developers!  Always a big fan of your work.

http://entlib.codeplex.com/wikipage?title=EntLib5Azure&referringTitle=Home
http://entlib.codeplex.com/releases/view/75025

Watch the video here:  http://channel9.msdn.com/posts/Autoscaling-Windows-Azure-applications

Posted On Friday, March 2, 2012 11:33 AM | Comments (0)

Experience with Indexed View

I had a bad experience working with Indexed view because, well, I didn't have much experience working with Indexed View. I hit 5 major issues before I finally gave up.Indexed view does not allow GETDATE() because it's a non-deterministic data type. I could not limit my result set, or put a flag column based on the age of the record.Indexed view does not allow self joinIndexed view does not allow OUTER JOINIndexed view does not do COUNT(*)Indexed view does not allow subqueriesIndexed view does not ......

Posted On Thursday, March 1, 2012 4:34 PM | Comments (0)

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski