Post reply

how to create hierarchy in star or snowflake based schema in SSAS 2008

  • December 11, 2011 at 11:22 pm

    #150747

    Hi all,

    currently i am working on ssas 2008 and trying to create cubes, dimention etc. i am new in this area but try to learn and create it, i had one fact table and three dimention tables & using that i had created star schema based and snowflake based cube in ssas 2008, now my issue is in star schema there is three table in i want to create a hierarchy which help me for drill down in pps charting, but my hierarchy belong to three different tables like,

    1) accountname(from Projectowner)->groupname(from Projectowner)->COE(from Resource),

    2) accountname->(from Projectowner)groupname(from Projectowner)->Projectname(from projectdetail)-> COE(from Resource)

    here, i attach both schema's images so please check it and help me.

    Any help would be greatly appreciated.

    Thanks & Regards,

    Ankit

  • December 12, 2011 at 8:56 pm

    #1420335

    For a Star Schema, you will need to adjust you dimension tables to minor the attributes you would like included in the hierarchy. You can either create named queries or reference views you have created in the database.

  • December 12, 2011 at 10:35 pm

    #1420354

    Hi Scott,

    Thanks for your reply,

    i tried with creating a named query, but result what i expected its not coming. here i attach named query relations and query that i had written,

    Query used for creating a named query:

    select pw.OwnerID,pw.AccountName,pw.GroupName,pd.PDID,pd.ProjectName,pd.Department,rs.RID,rs.COE from OPM_ProjectOwner pw

    INNER JOIN SnowlFacttab sf on pw.OwnerID=sf.OwnerID

    INNER JOIN OPM_ProjectDetails pd on pd.PDID = sf.PDID

    INNER JOIN OPM_Resource rs on rs.RID = sf.RID

    and i had created one hierarchy like :

    AccountName->Groupname->COE

    based on that i had count of resources, but when its drill down the count must be counted as per that but its not happen it will display all count, like:

    Banking (has: 27 Resource) -> Ankit(17) -> UI (3)

    Vishal(10) Testing (1)

    Mobile (1)

    i expected above kind of result but it displays 114.

    so please tell me where am i wrong or show me the correct track.

    Thanks & Regards,

    Ankit

  • December 13, 2011 at 4:02 am

    #1420489

    Have you checked your cube dimension usage?

  • December 13, 2011 at 4:11 am

    #1420494

    Hi Scott,

    i have no idea regarding cube usage, can you describe me how i can show and what its use ?

    Thanks & Regards,

    Ankit

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply