Create a Dimension with Drilldown But The Fields Are Unrelated

Posted on 2007-10-11
Last Modified: 2016-02-13
I want to create a dimension that has drilldown between 3 fields in my fact table.   The fields are not a parent - child relationship, they are just 3 independent fields.   To make it work at this point I have setup a dimension with each field in the key and then each field is also an attribute.  I am then able to setup the drilldown hierachies that I need.  Is this the proper way to have done this or is there a better way?
Question by:curtis591
    LVL 18

    Expert Comment

    The problem you will have is that if the fields do not actually have any integral hierarchical relationship then trying to enforcing this within the cube may well lead to data inconsistencies. Also from a 'correctness' perspective, a dimension should not really contain unrelated attributes.

    I would let SSAS create three seperate dimensions directly from your fact table, then view the required hierarchies within the client application. i.e. rely on the client application to use filtering to simulate a hierarchical drilldown rather than using an enforced hierarchy. i.e. In an Excel pivot you can combine any number of dimensions into a real time hierarchy by dragging extra atributes into the row/column.

    However, if you can get your current method to provide reliable data at all levels of drilldown then by all means stick with it, just test the results very carefully... And re-run thorough tests if you alter the attribute relationships.
    LVL 6

    Author Comment

    The problem that we had, at least in 2000, was that you hit a wall when you get around 8 dimensions in your pivot table.   We have a group of fields that if you look at one you probably are going to want all 3 to go with it.  When the user was looking at some detailed information and they had the 3 individual fields it would take 30 minutes to calculate.  If you had the two dimensions as drilldowns of 1 of them the same pivot table would return in a few seconds.  

    I suppose I should actually check to see if this is even a problem anymore.   I could be working around a problem I don't have any longer.
    LVL 18

    Accepted Solution

    Have you tried increasing the number of aggregations you are storing? It is worth redesigning you aggregations to make sure they are appropriate.

    Also, you should definately find that 2005 has some performance enhancements over 2000.

    Mind you as I mentioned earlier, as the old adage goes: if it aint broke, don't fix it!
    If it works properly, and you get the results you want, then I'd be reluctant to change it, even if it isn't perfect...

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Creating and Managing Databases with phpMyAdmin in cPanel.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now