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 imple
View Complete Post