Can I configure multiple surrogate keys on a table?
I am working with a Time table and time intelligence in ssas 2008. I would like to assign surrogate keys to FIscal Year Fiscal Quarter Fiscal Month Fiscal Day.
Is there a way to perform this in column properties in the current table?
Is there a way to perform this in column properties in the current table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am building Fiscal YTD, QTD, MTD & DTD. It was suggested that I use surrogate keys in order to utilize a cumulative total for each period.
Can you please paste your scheme for better understanding?
ASKER
Schema attached.
SCHEMA.JPG
SCHEMA.JPG
Can you please give me the name of the table and column name you want to implement the key?
ASKER
DimTimeIn
Fiscal YTD, QTD, MTD & DTD
Fiscal YTD, QTD, MTD & DTD
ASKER
I am preparing to run the time intelligence tool on my cube.
The DimTimeIn Table was created with two calendars:
1. Calendar Year
2. Fiscal Year
I have built hierarchies in SSAS for Fiscal Year. I would like for this calendar to provide:
1.F Year To Date - When clicked on the year value It gives me a cumulative total from January 1
2. F Quarter To Date - When clicked on the quarter value it gives me cumulative total from the first day of that quarter.
etc......
Hope this helps
The DimTimeIn Table was created with two calendars:
1. Calendar Year
2. Fiscal Year
I have built hierarchies in SSAS for Fiscal Year. I would like for this calendar to provide:
1.F Year To Date - When clicked on the year value It gives me a cumulative total from January 1
2. F Quarter To Date - When clicked on the quarter value it gives me cumulative total from the first day of that quarter.
etc......
Hope this helps
Let me comment on you DimTimeIn table.
1. It appears that it would be better named DimDate as the granularity is date, not time. :>)
2. The table already has a surrogate key: Date_Key. To my way of thinking, PK_Date is also a surrogate key. I'm still not quite "getting" the rationale for two surrogate keys on the same table.
3. The columns are present to do the rollups you desire. I think what you are asking is there a way to combine the multi-column rollups into single-column rollups (for sake of simplicity, I guess). Sure, you can do that.
Let's say your FY column contains the 4-digit year and your FQ column contains the 1 digit quarter and your FM column contains the 1-2 digit month. To do a rollup by month, you need to involve all 3 columns. You can derive single columns: yyyyqq and yyyymm to enable single column rollups for FQ and FM. You can even do it in a view or computed columns so that there is no effort to populate them and no chance they will get out of sync.
4. Stiill not sure how "surrogate keys" apply to your situation. The only "stretch" that comes to mind is if you are considering moving from a star design to a snowflake design. In that case FY, FQ, FM are dimensions in their own right and THOSE dim tables would best have surrogate keys that are referenced by foreign keys in the dimTimeIn table.
5. I suppose I've confused you beyond hope, by now, so let me give the simple advice. Don't add anymore surrogate keys to dimTimeIn. Do not add more foreign keys to the dimTimeIn table. Either derive the new columns you need or if you need add FQ, FY dimensions and link them to your fact table.
1. It appears that it would be better named DimDate as the granularity is date, not time. :>)
2. The table already has a surrogate key: Date_Key. To my way of thinking, PK_Date is also a surrogate key. I'm still not quite "getting" the rationale for two surrogate keys on the same table.
3. The columns are present to do the rollups you desire. I think what you are asking is there a way to combine the multi-column rollups into single-column rollups (for sake of simplicity, I guess). Sure, you can do that.
Let's say your FY column contains the 4-digit year and your FQ column contains the 1 digit quarter and your FM column contains the 1-2 digit month. To do a rollup by month, you need to involve all 3 columns. You can derive single columns: yyyyqq and yyyymm to enable single column rollups for FQ and FM. You can even do it in a view or computed columns so that there is no effort to populate them and no chance they will get out of sync.
4. Stiill not sure how "surrogate keys" apply to your situation. The only "stretch" that comes to mind is if you are considering moving from a star design to a snowflake design. In that case FY, FQ, FM are dimensions in their own right and THOSE dim tables would best have surrogate keys that are referenced by foreign keys in the dimTimeIn table.
5. I suppose I've confused you beyond hope, by now, so let me give the simple advice. Don't add anymore surrogate keys to dimTimeIn. Do not add more foreign keys to the dimTimeIn table. Either derive the new columns you need or if you need add FQ, FY dimensions and link them to your fact table.
CREATE TABLE Calendar_DEMO
(
FIscal_Year INT NOT NULL UNIQUE,
Fiscal_Quarter INT NOT NULL UNIQUE,
Fiscal_Month INT NOT NULL UNIQUE,
Fiscal_Day INT NOT NULL UNIQUE
)
GO