none
Relationship between cube and pivot table RRS feed

  • Question

  • Hi team,

    Just wondering the relationship between cube and pivot table.

    if possible, I also need some example how to use vba control cube.

    Cheers,

    Tom Sun

    Monday, January 9, 2017 10:06 AM

Answers

All replies

  • Hi Tom,

    A cube is typically the data source of an Excel Pivot Table ...
    Please find the following links ...

    Create a PivotTable with an external data source

    Get data from Analysis Services

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    Monday, January 9, 2017 11:47 AM
  • Hi Kieran, Thanks for your reply. The reason I asked for this is that I am having a project which needs to setup a few pivottables via excel data model I used to have the experience using vba to automatic generate pivot tables via normal data source rather than cubes The first step of this is to use power query to grab data from different source and build a data model. The second step is to use this data model to build a few pivot tables and slicers to get a dashboard report When I finish the first step and goring to the second, I suddenly find that all the codes which worked for pivot tables not created via the data model does not work for the one which generated by the data model. That is why I asking the question. is there any way I can better understand the cube and handle the way to use vba code to control the cube? Cheers, Tom
    Monday, January 9, 2017 12:02 PM
  • Hi Tom,

    Sorry I don't have a lot of experience with generating PivotTables from vba. Can any one else chip in?

    So I guess you will have used something like ...

    http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=147:create-an-excel-pivot-table-report-using-vba&catid=79&Itemid=475

    I do recommend that you use the links provided to connect to your cube. This connection process creates a Pivot Table as a default. Since the first step has to be for you to familarise yourself with the data module of your cube first.

    The native query language of SSAS multi dimensional cubes is MDX.

    So for using MDX within Pivot Tables the following is a good starting point ... PivotTable.MDX Property (Excel)

    Kind Regards,

    Kieran.


    Kieran Patrick Wood http://www.innovativebusinessintelligence.com http://uk.linkedin.com/in/kieranpatrickwood http://kieranwood.wordpress.com/

    • Marked as answer by Tom J Sun Thursday, January 12, 2017 10:02 PM
    Tuesday, January 10, 2017 10:35 AM