April 10, 2008 at 11:21 am
This may be an extremely beginner's question but I'm stuck. Basically, I want to know how to design a fact table from a source table.
My source table comes in with the following column heading:
Date
Customer
Location
Product
Officer
then a ton of Financial Columns like Balances, Revenues, Expenses, etc.
My goal is to get these Financial Column headings into Rows so I can then do a lookup and assign KPI ID from my KPI Dimension.
Eventually I would like my Fact table to look like:
Date key
Customer Key
Location Key
Product Key
Officer Key
KPI Key
Amount
How do I go about changing my source table so I can assign KPI keys to Financial Columns? What am I missing?
Thanks for any help
April 10, 2008 at 1:52 pm
Hi WWL,
I would starting by creating several dim table's[Dimension].For example
a Time Dimension Table with columns [ Time id, Month,Year, Quater...],
a Location Dimension Table with columns [Location ID, Address,City,State,Zip],Office Dimension Table [Office ID, Name,Address].These Dimension tables would need to be prepopulated before you can start loading the fact table.You would also need to decide the schema [snow flake or star schema], the relationships between dimension and source tables.You would also need to decide which SSIS transformation to use[to populate fact tables,while grabbing data from Dim tables].Hope this gives you a brief idea.
Here are a few good sites
http://qa.sqlservercentral.com/articles/Design/2769/
http://qa.sqlservercentral.com/articles/Design/2657/
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
April 11, 2008 at 10:23 am
First off thanks for the reply!!!
I've got my Dimension Tables setup and in the Database and I understand how to load the keys into the fact table. My issue comes when I start looking at the KPIs(measures) Below is how my source table looks without the other column metrics (customer, product, location, officer, etc)
Avg_Bal End_Bal Interest_Amount Expense_Amount Other_Revenue etc etc etc
100000 80000 6000 300 50 25 10 75
I guess I just create multiple SQL to drop these in a Row format (instead of the current Column format) and assign my KPI Keys in the SQL???
Thanks again
May 28, 2008 at 2:38 am
Hi,
Not sure what you mean by KPI key. The fact as such can contain only dim keys and facts. You already have the dim keys in your fact table and you can simply bring the facts (like balance, expenses, etc) and create them as measures in the cube.
Am I clear or am I over-simplifying it?
Regards,
Vijay.
Warm Regards,
Neel aka Vijay.
June 3, 2008 at 10:04 am
How about using SQL Server 2005's UNPIVOT operator? See if something like the following might help:
SELECT [Date] AS DateKey, Customer AS CustomerKey,
Location AS LocationKey, Product AS ProductKey,
Officer AS OfficerKey, KPI_Type, KPI_Amount
FROM SOURCE_TABLE
UNPIVOT (KPI_Amount FOR KPI_Type IN (
[KPI Field 1],[KPI Field 2], etc... )) AS UNPVT
The [KPI Field n] field names would come from the actual KPI field names in your source table, and those field names would then become the actual data for the new column KPI_Type. I think that's what you're looking for.
Steve
(aka smunson)
:):):)
WWL (4/10/2008)
This may be an extremely beginner's question but I'm stuck. Basically, I want to know how to design a fact table from a source table.My source table comes in with the following column heading:
Date
Customer
Location
Product
Officer
then a ton of Financial Columns like Balances, Revenues, Expenses, etc.
My goal is to get these Financial Column headings into Rows so I can then do a lookup and assign KPI ID from my KPI Dimension.
Eventually I would like my Fact table to look like:
Date key
Customer Key
Location Key
Product Key
Officer Key
KPI Key
Amount
How do I go about changing my source table so I can assign KPI keys to Financial Columns? What am I missing?
Thanks for any help
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply