February 20, 2006 at 5:47 pm
I'm about to lose my mind. Hopefully someone familiar with the 2005 BI studio and its use of dimension attributes and hierarchies can help me.
I have the following (simplified) scenerio:
Dimension Table: DimTrialBalance (PKID, Description, ParentPKID). This table is an unbalanced dimension table used for a hierarchy scenerio for parent and child accounts (essentially a self-join between PKID and ParentPKID). Roughly, the information looks like:
PKID Description ParentPKID
1 Trial Balance
2 Balance Sheet Accounts 1
3 Job Cost Detail 1
4 Income Statement Accounts 1
5 Job Cost Detail Sub1 3
6 Job Cost Detail Sub2 3
7 Job Cost Detail Sub3 3
8 Job Cost Detail Sub4 3
9 Job Cost Detail Sub5 3
10 Account#1 2
| 2
| 2
| 2
20 Account#10 2
21 Account#11 4
| 4
| 4
| 4
30 Account#20 4
31 Account#21 3
| 3
| 3
| 3
100+ Account#'s>21 5, 6, 7, 8 or 9
I have a fact table That looks like:
fctMonthlyAmounts (PKID, AccountDesc, RevenueType, MonthID, QuarterID)
sample data looks like:
1 111110 Electricity Expense 1 1
2 111110 Electricity Expense 4 2
3 623456 Dues Revenue 8 3
The Dimension table and Fact table are joined on dbo.DimTrialBalance.Description=dbo.fctMonthlyAmounts.AccountDesc
I am trying to show revenues by account rollup. So the account output should look something like this:
Trial Balance:
Balance Sheet Accounts $$$$$$
Job Cost Detail $$$$$$
Income Statement Accounts $$$$$$
Drilling into either Balance sheet accounts or Income statement accounts should yield an account number. Drilling into Job Cost Detail should yield a listing of the 5 sub accounts.
My problem is I cannot get the DimTrialBalance.Description column to show up in Excel. The only columns I can get to show up as dimension attributes in Excel are PKID and ParentPKID. I can't figure out what's going on.
Can someone tell me how to substitute the Description column for both the PKID and ParentPKID columns??? I don't ever want a user to see the actual key ID's.
Also, once I get the description to show up instead of the keys, how do I get that description to show up in a hierarchy. In other words: ParentPKID (DESCRIPTION VALUE SHOWN)-->PKID (DESCRIPTION VALUE SHOWN).
Thanks in advance.
Ryan
February 21, 2006 at 8:04 am
Ahh - this is actually starting to get comedic. Its been a while since I spun my wheels this much...
So, I've reviewed my dimension and it seems correct. I've even compared it against the AdventureWorks sample OLAP DB since it has a couple of parent hierarchies. One in particular is the "Employees" parent dimension that compares an the ParentEmployeeKey to the EmployeeKey to get an employees to supervisors relationship. When you "browse" this dimension you see the actual employee and supervisor full names and NOT the key integers.
In my situation I am getting the correct relationship, but I am only recieving key integers and NOT the [Description] column I need to see!
I have no idea what the deal is. I've tried to adjust the "NameColumn" property to the [Description] column of the same table and this doesn't work. **Please NOTE*** The AdventureWorks example DOES NOT use a different "NameColumn" value. In fact, it uses the KEY column with just a different datatype (WChar) - which I have also tried. WHAT!?!?
If anyone has any idea what's going on, please clue me in. It would be much appreciated. RH
February 21, 2006 at 8:24 am
Breakthrough! Moments after I posted above, I found the solution. I changed the Dimension view from "tree" to "Grid" and saw a very interesting column called "NameColumn" - which holds a different value than the "NameColumn" attribute under properties. This grid view showed either "Same as Key" or "Separate Column". I found that the Dimension Key had a modifed "NameColumn" Property that listed the full name as the display name.
So, I changed my DimTrialBalance key dimension (PKID) to NameColumn = dbo.DimTrialBalance.Description and then set the Parent Dimension to use the same as key.
My rollups and accounts are now coming through correctly. RH
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply