locked
Getting list of attribute values used by fact table? RRS feed

  • Question

  • I am trying to build a custom BI report with filter parameters.  One thing I need to do is get the list of valid parameters for each dimension attribute.  For example, the report includes the County attribute as one of the grouping attributes and users need to be able to filter by county.  When I generate the list of county checkbox/dropdown options in my ASP.NET page, I need to only list those counties which are referenced in the fact table.  There is no reason to list a county as a filter option if there are no associated facts. Essentially I am doing a inner join between a single dimension and the fact table, and then grouping by the attribute that I want to present as parameter choices.  The below options are what I've tried. 

    I am hoping for suggestions on a better way to do this or maybe improve the performance of the option using an MDX statement.  I feel like I should be querying SSAS for the attribute information instead of querying the underlying tables directly.  If anything in my SSAS cube changes that would affect the attributes used in the report, then I will have to be careful that I update views as necessary to make sure the right columns/keys are queried.

    1) I tried using a group by LINQ against Entity Framework model, but that is very slow because of the way Linq uses subqueries and distinct to implement a grouping. It took 8 seconds. I know it is the SQL that is generated becaus I used the profiler to capture the SQL and run it in SSMS.

    2) Using a SQL statement, stored procedure, or view through ADO.NET using inner join with a group by all take about 2 seconds.

    3) Indexed view takes .04 seconds to run, but requires that I create two views for each attribute/fact table combination.  The first view is the indexed view, and then the second view calls the first view using the noexpand option.  I then add the second view to my entity data model.  The second view with NOEXPAND is needed because SQL Server Standard edition would otherwise expand the view which gives me the same 2 second performance as a regular view.

    4) Using an MDX query like the below takes around a second and a half to execute through SSMS.  This seems kind of slow considering what I am ultimately after is the list of 40 or so counties which are references from the fact table.  I don't really need the aggregated values that come along with it.

     SELECT

              NON EMPTY
              {
              [Property Dimension].[County].[County]
              }
              ON COLUMNS        
               FROM [Data View] 
              Where (Measures.Amount)

    5) My other options would be to create utility tables for each attribute and populate this in advance.  This could result in a proliferation of utility tables, or I could have a single table with catergories/attribute names/values or something of the such, but feels very hackish.

    #3 and #5 are much faster than the other options by many times, and seems like #3 is less maintenance than #5.  For now #3 is what I'm going to do, but I'm going to have a proliferation of views like this, because I will need 2 views for each attribute/fact table combination where I want to allow MDX queries to be parametrized by the attribute.

    If you're curious as to what my parametrized query looks like (the one that generates the actual report, whereas the above MDX was just to get the list of attributes in use) see:

    http://social.msdn.microsoft.com/Forums/en-SG/sqlanalysisservices/thread/c0d368d2-7448-4731-8071-d60d5f87c4b3

     


    Friday, May 20, 2011 4:01 AM

All replies

  • how about elilminating dimensione-members that do not have any associated fact-rows at processing time?

    would solve all your problems, would perform pretty good and is also easy to maintain

    greets,
    gerhard


    - www.pmOne.com -
    Friday, May 20, 2011 6:31 AM
    Answerer
  • Can you give a little more detail on how I would do this? I'm not sure how to filter dimension rows in the SSAS cube.  Many of the dimension tables are shared by multiple fact tables, so I would need to filter the dimension rows depending on what fact table the report is being run against.  Hence why I'm creating a view for every fact table/dimension combination.

    Friday, May 20, 2011 8:39 PM