Post reply

Items to consider when creating new Data Warehouse

  • March 9, 2012 at 5:07 pm

    #149860

    Hi Folks

    I am MS-SQL Dev with knowldge & exp of SSAS Cubes & MDX queries.

    So most of time, when i got ready made SSAS-cube to work with.

    Now i got new assignment, in which i need to create/build Data Warehouse & then create SSAS-Cube which will be used by Excel Services and Reporting Services.

    I am using SQL Server 2008 ( 32 bit ) with Excel 2007, SharePoint 2007.

    So i see that this time i got work which needs lot of pre-thinking...oR Thinking about future.. ( Just like T-SQL DB design. )

    I mean initially i was looking about how to build Data Warehouse.

    So i got below article:

    http://www.sqlservercentral.com/articles/Analysis+Services+(SSAS)/69343/

    Then i was thinking about:

    A- How to handle Null-Values ?

    B- How to implement Slowly Changing Dimensions ?

    So on same path, what else i should think...

    Or what else i should consider/look-at...before building Data Warehouse ?

    Based on your experience, can you guys please post-a-topic ( OR ask Question...to me)...!!!

    This will be useful to everyone reading this post.

    I hope you guys are getting idea about what i am saying.

    Thanks in advance.

    Cheers

    DevSQL123

  • March 10, 2012 at 2:22 am

    #1457457

    Are u familiar with Kimball dimensional modelling. Its a good place to start in terms of designing the OLAP DW.

    A few things I have learned over the years are :-

    Make sure the data quality is perfect before you ever insert into your cube. Pay very close attention to the ETL process.

    You will encounter performance issues sooner than you think. Pay attention to ETL Load times and cube processing time.

    Have as much things in common as possible, cubes tend to get very complicated very fast. The more generic processes you have the better.

    When in doubt don't assume , get your questions clarified with your business before starting off.

    Know the data, BI is more than just building a cube. you need to know your data and its meaning in order to suggest as well as make improvements.

    If you not sure how to go about it , build a small cube with a few dimensions and facts first , don't go big bang

    Null handling etc are to be decided during etl and only handled as a last resort within the cube same thing goes for how you handle SCD

    Anticipate the size/Security requirements and make sure you create partitions and perspective early on.

    Stick to star schema where possible

    Always use a dedicated server for ETL, DM and DW when dealing with large volume of data.

    Jayanth Kurup[/url]

  • March 13, 2012 at 12:28 pm

    #1458750

    Awesome answer you got!!

    +1 on Kimball. Check out some of the books he has out. The Kimball component makes life sooo easy!!

    Nulls - we decided to not have null values in our DW, which makes my life easy. In the ETL processes, nulls are replaced:

    -1 for numeric, 0.00 for dollars, and specific "unknown" values that are dependent on size of text field ("U", "n/a", "None Specified" ... )

    These are the same values used for Special Member input to the Kimball component.

  • March 13, 2012 at 12:34 pm

    #1458760

    Just remember that Kimball isn't the only option. You may also want to look at other methodologies as well and then choose the one that best fits your environment.

    One size does not fit all.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply