FACT Table Creation
-
Hello:
I'm a newbie to Datawarehousing and using of the concepts of Dimensional
modeling. I read about Kimball's methodology too.
I started using Analysis Services, which is really great. I created tables
with the primary index values.
My scenario is like this.
I should be able to drill down to any of these reports and the details
also. I would like evaluate the sale of tools in different regions during
different times, with different customers, with different tools description
and with different types of purchases(in any fashion), the way users need.
I created 5 different tables with
Tool Description, Region, Time, Purchase Type, Customer.
All tables are linked by Tool number which is unique.
Tool Description has fields such as
Tools Description, Tool Type, Tool Group, Tool Number(The index has
multiple columns to make it unique).
Region - Region, State, Zip Code, Tool_No(Unique Record)
Time - Year,Quarter,Month,Week,Day,Tool_No
Purchase_Type - Purchase_Type(Lease/Sale), Sale_Type, Transaction_Type,
Tool_NO(Index values)
Customer(Customer age, Gender,Tool_No)
I created all these tables and keyed in values manually for my own testing
purposes. I would be populating the values using Stored PRocedures(probably
by DTS) as time goes.
But to create a FACT table with multiple dimensions, the following
questions arose.
Can I create a fact table with the following columns or do I need to add
Tool_No also in the FACT table.
Tool_Description,Year,Purchase_Type,Customer,Total_Sales.
I guess that I need to populate this fact table using T-SQL before
creating a cube .Am I correct? Please do let me know.
In the FACT TAble, do I need to go to the level of granularity of the
Tool_No also.
Once I create a fact table with T-SQL queries, can I go ahead and create a
cube with all 4 - 5 dimensions, the way I would really like to drill it down
to the reports.
Any help is greatly appreciated.
Thanks,
-
Hi,
I should be able to drill down to any of these reports and the details
also.
To drill down to the detail, it will depend on the front-end tool whether the feature allowed you to do so or not.
I would like evaluate the sale of tools in different regions during
different times, with different customers, with different tools description
and with different types of purchases(in any fashion), the way users need.
I created 5 different tables with
Tool Description, Region, Time, Purchase Type, Customer.
All tables are linked by Tool number which is unique.
Tool Description has fields such as
Tools Description, Tool Type, Tool Group, Tool Number(The index has
multiple columns to make it unique).
Region - Region, State, Zip Code, Tool_No(Unique Record)
Time - Year,Quarter,Month,Week,Day,Tool_No
Purchase_Type - Purchase_Type(Lease/Sale), Sale_Type, Transaction_Type,
Tool_NO(Index values)
Customer(Customer age, Gender,Tool_No)
In my opinion, I will considered that 5 dimensions (driving factors) required by you, you should not use Tool_No for other dimensions except "Tool Description", each of the dimension such as Customer shall be linked by Customer ID, Time by Time ID and so on.
I created all these tables and keyed in values manually for my own testing
purposes. I would be populating the values using Stored PRocedures(probably
by DTS) as time goes.
But to create a FACT table with multiple dimensions, the following
questions arose.
Can I create a fact table with the following columns or do I need to add
Tool_No also in the FACT table.
Tool_Description,Year,Purchase_Type,Customer,Total_Sales.
I guess that I need to populate this fact table using T-SQL before
creating a cube .Am I correct? Please do let me know.
In the FACT TAble, do I need to go to the level of granularity of the
Tool_No also.
Once I create a fact table with T-SQL queries, can I go ahead and create a
cube with all 4 - 5 dimensions, the way I would really like to drill it down
to the reports.
You will definitely need to include each IDs in your fact table, for example, Customer ID, Region ID, Time ID, Tool No and so on. You can write a stored procedure to populate your fact table and link the keys, however, make sure your source data provide you the linkage, otherwise, there is no way that you can do that. Just remember the key concept, if source data can give you whatever you want, you can do it, otherwise, the answer is no. BI is making use of existing database to provide the analysis but not to create it. Hope this may help you.
Regards,
chlow
-
Let me strongly recommend that instead of reading about Kimball's model, you actually take the time to read his book The Data Warehouse Toolkit to get a clearer concept of what you are trying to do with cubes in a data warehouse.
How would that help you resolve the questions you raise?
1. You would have a clearer sense of what level of granularity you need in your fact table. It sounds like you are interested in tracking sales of individual products by these five dimensions, which suggests your row level in your fact table is probably the line items on customer purchases that identify an item, the quantity and the price.
2. Read Kimball's discussion of why you might want to use surrogate keys instead of naturally occuring keys such as tool number. It is too long to post the complete explanation, but the short version is that it will help you keep your fact table as narrow as possible and it will allow you to more easily manage changes to the dimensions in the future.
3. If you go very far down the path of building this cube without thinking about how to set up conformed dimensions first you are likely to end up throwing away much of your current work if it is successful and your business users get excited and ask you to expand on your work to address related questions.
Read the book. It is well worth your time if you are seriously interested in dimensional data warehouse development.
Oh, and don't forget to have fun.
Bob
-
Bob:
Thank you very much for your response and your advise about reading the book. I started reading it and found it very interesting.
Meanwhile, just to let you know, I ceated some tables with the structures that I specified(but modified them a little) with the output that I expected.
I realized the importance of surrogate keys and am going to use it in the modeling too.
I would like to use MS-Excel Pivot Tables as front end for easy analysis.
Thanks,
Bobby.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply