Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
need help with a report in excel connected to cube in SSAS

Answered need help with a report in excel connected to cube in SSAS

  • Wednesday, April 04, 2012 3:21 PM
     
     
    Sign In to Vote
    0

    Hi All

    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.

    Thanks

     

All Replies

  • Saturday, April 07, 2012 3:20 AM
     
     Answered
    Sign In to Vote
    0

    thinkingeye,

    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
     
     
    Sign In to Vote
    0

    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?

    Thanks

    bhargav kandala

     
  • Monday, April 09, 2012 2:09 PM
     
     
    Sign In to Vote
    0
    You just need to expand that "level", drag the required attribute into the filter section and set the filter.

    Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com