Wednesday, April 04, 2012 3:21 PM
i'm new to SSAS and to this forum. I have a report in excel which was created by a user,
how can i identify which cube he used to create this report?
can i create a report using two cubes in excel?
once i create the tables/ views in SSIS should i create the dimensions and cubes in SSAS or can i create the dimensions in SSIS itself?
Please nayhelp or advice is greatly appreciated.
Saturday, April 07, 2012 3:20 AM
Welcome to SSAS (and this forum)!
1. You can identify which cube is used by looking at the data connection properties within Excel (or by examining the .odc file the user will have automatically created in his/her "My Documents\My Data Sources" folder). In the dialog below, the server is "labsql1", the SSAS database is "AviationStatistics" and the cube is "FlightData"
2. In Excel, a pivot table is based on one and only one cube. So, to create a single worksheet that reports data from two cubes, you have a couple choices:
a) place multiple pivot tables into the sheet. You can still connect slicers to multiple pivot tables, which makes a nice user experience.
b) pull the data into excel using Excel formulas rather than (or in addition to) using pivot tables. A lesson on how to do this is here: http://msbiacademy.com/Lesson.aspx?id=15
3. You create dimensions & cubes in SSAS. These objects can be based on SQL tables you create with SSIS, but the dimensions & cubes themselves are implemented in SSIS (via a data source view (DSV)).
Rob Kerr SQL Server MVP CTO, BlueGranite
Monday, April 09, 2012 1:22 PM
Thanks Rob it was of great help, i have one more questio. In the excel report that i have on the right hand side of the sheet "pivot table field list" under the list i have a DIM "CAP Types", under this DIMENSION i have multiple leveles and each level has some attributes, how can i set a filter on just one attribute?
Monday, April 09, 2012 2:09 PMYou just need to expand that "level", drag the required attribute into the filter section and set the filter.
Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com