February 14, 2010 at 12:34 pm
I'm using a date column as the primary key in a table called 'Period' and joining it with a table called 'CustomPeriod' with this date column as the foreign key.
However, in this 'CustomPeriod' table, the primary key is date + Timetype (ie., calendar, fiscal and distributor). Thus, in this table, the date gets repeated three times (Period has 50 rows, CustomerPeriod has 150 rows).
Now, I have a hierarchy in a dimension taken from CustomPeriod table which goes like this below:
Calendar (timetype column in CustomPeriod table)
Jan 08-Dec 09
Jan 09-Dec 10
Distributor
Jul 08- Jun 09 (yearname column in CustomPeriod table)
Jul 09 - Jun 10
Fiscal
Sep 08-Aug 09
Sep 09 -Aug 10
So, in my dimension I need the date column repeated three times so that I can choose the specific time type in conjunction.
The fact table is connected to Period table only through date column. I have the timetype column available only in CustomPeriod and not in any other tables in the database.
Is there a way to force SSAS to return these multiple rows of date (as opposed to the distinct date column)?
February 15, 2010 at 8:25 am
Not positive I know what you are asking for here. If you mean can Analysis Service be forced to make the join above the date level, the answer is yes, you can make this join when you add the date dimension. When you do, you will get a warning that the attribute relations need to be set correctly. You do this in the first tab. This is a subtle and poorly documented process, and don't be surprised if you find you have to redo your date dimension to be able to accomplish this.
Some possible concerns I would have based on your brief description:
1. The primary key for any dimension table should be an integer. The date can be a surrogate key, but ultimately this join should be reduced to an integer.
2. I'm not sure if you are mixing time and date in a single dimension, but if you are, you should seperate them into a date dimension and a time dimension. If you don't, your combination date/time table will have a large number of records. slowing down your query results. Seperating them will ensure the time dimension never grows (as hours won't be added to the day), your users will have more flexibilty (the time dimension can be placed on the axis opposite the date dimension to yield useful information), and your queries will perform faster.
3. I would have consider having a seperate time dimension for all your external customers.
February 15, 2010 at 10:34 am
Hi Ron,
Thank you very much for the reply. I am using the date column (datetime data type) to connect my dimension to the measure.
I have attached the document that shows the cube and dimension structure including the dimension usage tab on how I am setting up this dimension.
When I select a time type, I am supposed to get values for my measures. But the cube browser does not show anything.
When I do a test using a query in Management Studio based on my joins in the cube, I am getting the data.
Thank you.
February 15, 2010 at 11:17 am
I'm not sure what you're expecting with dimension tables connected to other dimension tables. There are a few times that would be okay, but a good BI solution normally uses the star schema. (Yes, some will disagree, but as I'm following Ralph Kimball's advice, I'm good with my view). You can query this as you would a normally OLTP structure, but I don't know how you'd try to make that work through analysis services.
I'm including a view of my tables that works well in a production system. You'll notice the same date table (dtblCalendar) connects directly to the fact table on multiple fields and the time table (dtblClock) connects directly to the fact tabel on two fields.
February 15, 2010 at 11:36 am
Have to agree with Ron here - if you simply took the additional attributes from your snow-flaked table and add them to the Period dimension, you can likely create all that you want.
The way you have all three 'date' types within a single hierarchy is a little 'funky' - a more standard approach that is proven to work is to have these three hierarchies as separate User Hierarchies within that single dimension. You'll still be asking he user to select a time type, the hprasing of that question just changes slightly in that they select the Hierarchy that suits their query.
Another issue that could be impeding your progress is that it appears that you have multiple keys with the same value (2005-09-01) - which do you think SSAS should chose? "all of them" is not an option 🙂
Steve.
February 15, 2010 at 12:04 pm
Thank you Ron and Steve.
I will change my hierarchy with your suggestions.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply