In my relational model, I have a dimension table called ProductRelease with two foreign keys to a Time table. The Time table looks something like this:
DayDate (PK, datetime, not null)
YearId (int, not null)
MonthId (int not null)
The ProductRelease table looks something like this:
ReleaseId (PK, int, not null)
ScheduledDate (FK, datetime, not null)
ActualDate (FK, datetime, not null)
I'm simplifying things for this question, but for the Product Release dimension in Analysis Services, I want to have two attributes: one called Scheduled Month Id; and the other called Actual Month Id.
In the Visual Studio dimension editor, I drag the ActualDate column from the ProductRelease table in the Data Source View pane to the Attributes pane, and then do the same with the ScheduledDate column. This creates two attributes called Actual Date and Scheduled Date. I then process the dimension, go to the Browser tab, click Member Properties, and check the Actual Date and Scheduled Date attributes, and the correct dates are displayed in the browser. For the dimension member I am looking at, the values are:
Scheduled Date = 11/29/2006
Actual Date = 12/8/2006
So far so good -- this is what I expected.
Next, I drag the MonthId column from the Time table in the Data Source View pane to the Attributes pane. This creates a new attribute called Month Id, which I then rename to Actual Month Id. Then I create another attribute by dragging Time.MonthId again and rename it to Scheduled Month Id.
After processing the dimension, I go back to the Browser tab, reconnect, and check Actual Month Id and Scheduled Month Id in Member Properties. The result is that both columns have the same value:
Actual Month Id = 11
Scheduled Month Id = 11
For the Actual Month Id and Scheduled Month Id dimension attributes, they both have their KeyColumns property set to Time.MonthId (Integer), and NameColumn and ValueColumn are both set to (none). These were the defaults when I dragged the columns to the Attribute pane.
My question is, what do I need to change so Actual Month Id will be 12? I cannot seem to figure out the correct values that need to go into KeyColumns, NameColumn, and ValueColumn to get this to work.
Thanks for any help!
-Larry
You might need to create a database view or DSV named query which explicity joins [ProductRelease] twice to the [Time].
So that you have a [ProductRelease] table that looks like
ReleaseId (PK, int, not null)
ScheduledDate (FK, datetime, not null)
ScheduledMonthID (int, not null)
ActualDate (FK, datetime, not null)
ActualMonthID (int, not null)
The dimension designer won't recognize the multiple "roles" of the [Time] table and treat them accordingly like the cube designer will, which brings be to my next suggestion, that the [ProductRelease] might be used as a fact table. The measures might be
ReleaseCount = 1,
OnTimeCount = CASE WHEN SceduledDate <= ActualDate THEN 1 ELSE 0 END
etc.
Either that or move the the scheduled/actual dates to the relevant fact tables which reference the ProductRelease dimension.
My short story here is that Fact tables are for resolving many-to-many relationships. It can be done in dimensions but very seldom with one table.
Hope this helps (or even just makes sense).
|||
Hi John,
Thanks for the response. ProductRelease is actually a fact-dimension table, and I do have some measures like ReleaseDays and DeliverySlipDays. Furthermore, I am using Scheduled Date as a cube dimension and plan on using Actual Date as a cube dimension as well. So the Time dimension will be used as a role-playing dimension.
But I also want to use Scheduled Date and Actual Date in a dimension hierarchy so users can navigate through the releases by those two dates (e.g. all releases scheduled for Q1, all releases actually released in Q2, etc.).
Anyway, you answered my question -- thank you very much; i.e. the dimension designer won't recognize the multiple roles. So I guess I'll create a DSV named query as you suggest for the purpose of the dimension navigation.
As an aside, I'm curious if what I'm trying to do is achievable via AMO. In other words, could I write a C# app using the class library to define these multiple roles for the dimension attributes. Probably not what I want to do since that will get my db out of synch with my Visual Studio project, but just a curiosity.
-Larry
