July 25, 2011 at 8:17 am
I am just getting into creating Dimension/Fact tables from our data warehouse using SQL Server 2008 R2, SSIS, & SSAS.
I currently have the following books:
The Data Warehouse ETL Toolkit - Kimball/Caserta
SQL Server 2008 Analysis Services - Step by Step - Cameron/Hitachi Consulting
I am having trouble designing dimension/fact tables from a relational database. Does anyone know of any other books that details how to 'think' dimensional from relational? Something for beginners?
Thanks.
July 26, 2011 at 3:05 pm
Sqlraider (7/25/2011)
I am just getting into creating Dimension/Fact tables from our data warehouse using SQL Server 2008 R2, SSIS, & SSAS.I currently have the following books:
The Data Warehouse ETL Toolkit - Kimball/Caserta
SQL Server 2008 Analysis Services - Step by Step - Cameron/Hitachi Consulting
I am having trouble designing dimension/fact tables from a relational database. Does anyone know of any other books that details how to 'think' dimensional from relational? Something for beginners?
Plenty of documentation in the net about Dimensional modeling.
The trick is, you do not translate relational into dimensional - you design dimensional based on business requirements then you design an ETL process that feeds your dimensional tables.
In a nut shell, think on Dimensional model at the Datamart level, for all practical purposes you are always designing a single Datamart to answer questions about a single domain then you need a single FACT table and as many DIMension tables as you are planning to dice and slice the FACTual data.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 27, 2011 at 7:21 am
PaulB-TheOneAndOnly (7/26/2011)
Sqlraider (7/25/2011)
I am just getting into creating Dimension/Fact tables from our data warehouse using SQL Server 2008 R2, SSIS, & SSAS.I currently have the following books:
The Data Warehouse ETL Toolkit - Kimball/Caserta
SQL Server 2008 Analysis Services - Step by Step - Cameron/Hitachi Consulting
I am having trouble designing dimension/fact tables from a relational database. Does anyone know of any other books that details how to 'think' dimensional from relational? Something for beginners?
Plenty of documentation in the net about Dimensional modeling.
The trick is, you do not translate relational into dimensional - you design dimensional based on business requirements then you design an ETL process that feeds your dimensional tables.
In a nut shell, think on Dimensional model at the Datamart level, for all practical purposes you are always designing a single Datamart to answer questions about a single domain then you need a single FACT table and as many DIMension tables as you are planning to dice and slice the FACTual data.
Hope this helps.
Ah, I was looking at it backwards plus I may have also had my FACT's & DIM's turned around also.
I've been looking on the net the last few days & bookmarking multiple sites.
All the examples are geared towards 'sales' with $$ & product when mine is based on students, their grades, and how they relate to the gender, ethnicity, any special programs they are, etc. Having a hard time translating the examples to my needs. Any chance you could translate those pieces into FACT & DIM or give me a starting point?
Anyway, thanks for the laymans translation - it does help.
July 27, 2011 at 9:19 am
Sqlraider (7/27/2011)
Any chance you could translate those pieces into FACT & DIM or give me a starting point?
In its most basic form, think of it as AGGREGATE FACT BY DIMENSION. So the standard examples include TOTAL (aggregate) SALES (fact) by MONTH (Dimension). So you might want to COUNT (aggregate) STUDENTS (fact) by GENDER (dimension).
That should at least get you started.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 27, 2011 at 9:56 am
drew.allen (7/27/2011)
In its most basic form, think of it as AGGREGATE FACT BY DIMENSION. So the standard examples include TOTAL (aggregate) SALES (fact) by MONTH (Dimension). So you might want to COUNT (aggregate) STUDENTS (fact) by GENDER (dimension).
That should at least get you started.
Drew
Yes, thanks for the starting point.
August 3, 2011 at 7:37 am
Does anyone know of any other books that details how to 'think' dimensional from relational? Something for beginners?
There weren't any other book recommendations, but I would recommend The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling by Kimball/Ross. Pay close attention to the concept of granularity.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply