March 4, 2012 at 8:02 am
Hello,
I am new to Analysis services and I built my first cube. The project managers are able to access the cube using power pivot, and the load time is very fast. The only problem I have now is that they are asking me for drill down functionality within the cube. I used Visual Studio 2008 to build this cube. Does anyone know how I can do this?
Any help would be greatly appreciated,
Dave
March 5, 2012 at 5:12 am
Drill down functionality is setup on the Action Tab of the Cube. In BIDS/ Visual Studio, double click on the cube, then click on the Actions tab. Then click on the "New Drill Through Action" button. From there you can specific the drill through details you would like to display from the list of dimensions.
Also remember you have to enable drill down for the security roles that you setup.
March 5, 2012 at 5:15 am
Drill down is the ability to expand the categories in a dimension by clicking on them or a symbol by them to expand a more detailed category. For example, clicking on a state might open that value to show the counties, the counties, cities, and the cities zip codes.
Do do this, you simply drag the items out in BIDS from the Attributes section to the Hierarchies and Levels section. It's easy. If that's not enough, then google Creating Heirachies in SSAS or something similar.
March 5, 2012 at 1:17 pm
Thank you all very much for your response. Let me try to explain exactly what I need:
I have the following dimension tables:
Agency
AdSales
MOwner
Client
MVendor
MParent
MType
Country
And only 1 fact table
Fact_Finance
The cube works fine and I am able to view it in power pivot and the SSAS browser but, I need to be able to define a hierarchy/drilldown within the analysis services cube that drills-down to the following diagram below. I am not even sure is this is possible within the cube:
Region -->
Country -->
Client -->
MType -->
MParent -->
MOwner -->
MVendor --X
March 5, 2012 at 2:27 pm
No, that is not possible directly. However, it's just a matter of adding attributes from the different dimensions one after the other. This is also a common way to use the cube to analyze different patterns.
March 6, 2012 at 8:45 am
Thank you again for your response.
How would I add attributes to the dimensions? I have not been able to find a way to do this. Also, would an MDX query be able to accomplish this? I would prefer doing this in BIDS/VS.
Thanks again
March 6, 2012 at 8:48 am
The attributes are create automatically. Those are the characteristics you listed.
March 6, 2012 at 8:48 am
You will need to have the attributes in the Data Source view. They you can just navigate to the individual dimension and drag and drop the attributes from the right side (DSV table/view) to the left side where your attributes are listed.
March 8, 2012 at 9:25 am
I replaced one of the dimension tables with a named query, added a new dimension table, and included the new dimension. So essentially I added the tables in a cross join and then linked the keys in the data source view to the fact table. The problem I am having now, is that Visual Studio is now crashing my development environment, as it is not able to process all the cube.
Does anyone know how to create a drop down in visual studio that spans across 6 dimension tables?
Please let me know if I have been clear enough in this post. Sometimes it's difficult explaining what is needed in a project over emails/IM/Blogs, etc.
Thanks again
March 8, 2012 at 9:34 am
What error are you getting by means of "crashing".. running out of memory.
I guess I am a bit confused by what you mean by drill down. I assume you are using Excel, so you can drop all your dimension into a row or column areas of the filters.
Or are you trying to flatten the data?
March 8, 2012 at 11:28 am
Hello,
I figured out why it was crashing. That was my fault
We are using Performance point to view the data, and my boss is asking me to create a drill-down that span across 6 dimension tables. I am not sure how I can accomplish this. I am using Visual Studio 2005.
March 19, 2012 at 7:36 am
Thank you all for your responses.
Basically what I need to do is create a hierarchy based on multiple dimension tables. I can easily create a hierarchy per dimension table, however the levels are spanned across multiple dimension tables.
My apologies if I am not being clear enough. I am new to Analysis services, so I am still trying to get the lingo down 🙂
Dave
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply