July 27, 2009 at 2:06 am
Hi all,
I am a newbie to SSAS ,I just want to know how can i relate sequence key to other attributes like cd and name from other dimension
In my case i want to relate gsm,rsm,asm,ss,sr from mon reln hist to sf detail (sf detail seq) cd and name . How do i relate both dimension.So that user can drill down by name or cd.
July 27, 2009 at 4:19 am
I am not quite sure of what each table represents. I am not quite sure of what you are trying to achieve.
I think you are trying to create a dimension based on the table sf_detail. I think you want to let people drill down by name or cd. In order to try and help I will assume that you want to let people interact with this dimension through the use of sf_cd and sf_short_name.
What I have done before in similar scenario is to use a view such as
create view vw_olap_sf_detail as
select sf_detail_seq,
sf_cd,
sf_short_name,
sf_short_name + ' - ' + sf_cd as sf_name
from sf_detail
The above view allows you to present the code and the name together in the dimension such as
bicycle red 32inch frame - BCR32 so that the users can see the words and the key together. Set the name property to the ne column in the view. However as you can only order the dimension by one attribute then you will order by code or by name which means it is not user friendly to everyone.
Alternatively the soultion that I think would work the best for you is to create two hierarchies in the dimension. Drag the code (sf_cd) into the Hierarchies and levels pane in the dimension editor. This will create one hierarchy (with only one level). Repeat with the sf name so that your dimension editor looks similar to the attached bmp. If your hierarchy needs more levels then add them to both of the hierarchies. This then appears to the user as two different dimensions in Excel 2003 and allows them to choose which way they want to drill down.
You could just create two dimension. I have used Hierachies to better effect is AS2000 but not yet got to grips with it in SSAS.
Should you wish for a neater answer:
http://blogs.technet.com/andrew/archive/2007/09/04/hierarchies-in-analysis-services.aspx
Hope this helps.
Ells
😎
July 27, 2009 at 5:02 am
Thx for the Reply
The dimension Mon Reln Hist (SR Detail Seq) ->Related to Fact(SF Detail Seq)
What i want is Instead of drilling through there sequence i want to drill down through there cd or name.
The Seq of Heirachy level (SR,SS,ASM..) are stored in SF Detail(Master table for all Sales Force) which i need to map to there code and name.
I tried to create seperate hierarchies for cd and name by mapping there seq with there respective cd and name through attribute relationship as per your suggestion.
But the Deployment fails.Hope my query is clear this time
July 27, 2009 at 5:23 am
Befor you deploy the solution could you take a screen shot of the dimension you are creating?
Do you get an explanation for why the deployment failed?
Thanks.
Ells.
July 27, 2009 at 5:47 am
While Deploying it states that duplicate key found,but the duplicate will exist as it is hierarchical.I have attached the screenshot for your reference
July 27, 2009 at 6:20 am
With the dimension editor the three noticeable issues are the three blue lines under the dimension name and the hierarchy names. If you move your mouse over them you may get more info.
Firs things first. Did the dimension build ok without the two hierarchies?
From looking at the screen shot of processing It looks like there are duplicat values for asm detail seq. Could on asm detail seq be linked to two or more rsm detail seq.
As an example of hierarchies you coul have a dimension for boxes that has two hierarchies one for size and one for weight.
for weight there would be a grouping level that would be values like <10 kg, 10KG to 99KG, 100KG+. then a lesser attribute that was the accurate weight 1kg,2kg.
for size again one level opf the hierarchy would be <1m, 1m to 9m, 10m to 19m, 20m and above.
These work as there is a relationship from the bottom up. So if I have a 1.5kg box the accurate weight is onr value and the weight group is only one weigh group <10kg.
As I am unsure how your attributes in the hierarchies work. They should be
each sr detail seq relates to one and one only ss detail seq
each ss detail seq relates to one and one only asm detail seq
each asm detail seq relates to one and one only rsm detail seq
each rsm detail seq relates to one and one only gsm detail seq
and the same for the other hierarchy
Hope this helps a bit.
Ells.
😎
July 27, 2009 at 6:35 am
yes the Dimesion builds properly with Only Seq.I am able to drill down and viceversa.
SR can have only one SS but SS can have more than one SR.(similar to employee manager).
the Blue Line Suggest to avoid visible Line Attribute & attribute level doesnot exist between 1 or more levels of heirarchy (For the newly created hierarchy Code wise).
July 27, 2009 at 7:41 am
All attributes in the dimension have a default attribute relation to the dimension key (primary key Mon Proc Setting Seq). You can see this in the left pane and if you expand the dimension key in this pane.
This is true even if you have no hierarchies. When you build user hierarchies in the middle pane you add attribute relations between the levels in this user hierarchy.
Each Employee works at an office, each office in a different country. Thus Employee has an attribute relationships with office, office to country etc.
So your primary key will need to an attribute relationship to the sr detail seq, this in turn needs an attribute relationship to sq detail seq, this in turn to the next and so on.
Hope this helps.
Ells
😎
July 27, 2009 at 7:52 am
Forgot to add this screen shot of relationships. Sorry most of it is blanked out. That is done as I do not wish to disclose all the details of the dimension. From this it can be seen that there are two attribuites C_ Dep Rank and Dep Rank - these are two unique scores (to multiple decimal places. These have a relationship to a Quntile and a quintile description. These give me two hierarchies from this dimension so people can browse by how good the score is, either based on D_ Dep or Dep scores.
There is also other hierarchies there , the primary key M Name is related to C Name. This is displayed as another hierarchy ion the dimension.
All of the hierarchies are clearly 1:m joins from the key up to the top level.
Hope this helps.
Ells
July 28, 2009 at 1:02 am
The Problem with the solution is SR->SS->ASm->RSM->Gsm is not always true.
Sometime SR->Asm or SS->RSM and so on,is there any way can we join the dimension as we do in sql (table)so that using key i can get the cd and name or any other solution.
As i am very new to this,I am not able to understand whether the dimension which i have designed is proper.
Whether the reference relationship will help me between (SF relation and SF detail ) between SR->sf detail ,SS ->sf detail and so on.
July 28, 2009 at 3:23 am
You may need to provide more details on the relationships. In addition a bit more detail on what you are hoping to see out of the other end.
This would help in trying to define a strategy. I think you may end up with several hierarchies so that one could have for the relationships that are one to many you have one hierarchy with just SR, one hierarchy with just SS, one hierarchy with just Asm, one hierarchy with just RSM.
Would that be suitable?
Ells.
😎
July 28, 2009 at 4:01 am
The RelationShip is like SR->SS->ASM>RSM->GSM
but the intermediate linkages like ss,asm,rsm or gsm might not always be present.
for eg some employee might be directly reporting to senior manager as ther no junior manager for that project or promotion of junior manager to senior.
On the appointment of the junior manager he may report to junior manager .
So there is a time gap for which there is no relationship between employee ->junior manager and after which it might be .
this relationship are maintained at monthly level ,which changes depending upon the requirement(location,designation etc).
The CD and name are identifier used to identify them but seq is used to keep track of the changes.
Mon Reln hist Give Heirarchy and FactSecSales(SF Detail SEQ related to SR )from Mon Reln hist ,gives sales for SR by which i can aggregate to higher levels.
All The master entries of All salesforce(SR,SS...) is in SF detail Dimension.somehow i need to relate both these 2 so that while reporting,
I can either report by there name or cd to maintain data abstraction .
As Salesforce not aware abt seq (only there cd or name).
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply