none
transform power pivot model to query

    Question

  • hi eb

    after building a power pivot model which includes a number of related tables

    i realized i want to analyze results in a simple table not in pivot tables

    there for my model should result to one table of records.

    So i guess i need to start with a single query which will return a single results table.

    Can i transform my original model to free sql to be used as my data source?

    TIA!

    Rea

    Thursday, January 23, 2014 8:06 AM

Answers

  • Power Pivot sends to SQL one query per table - join between tables are made within the internal engine.

    If the schema allows you to do (e.g. yuo have a star schema) then your DAX query might involve all the tables and get a single flat table with columns of all the tables in JOIN - but in any case there is no SQL code generated for such a query.

    Marco

    • Marked as answer by reapeleg Sunday, January 26, 2014 8:11 AM
    Sunday, January 26, 2014 6:50 AM

All replies

  • In Excel 2013 you can do this:

    • click ribbon Data / Existing Connections
    • click tab Tables
    • select one of the tables of your PowerPivot model (not the Workbook Data Model at the beginning of the list)
    • click Open
    • In the Import Data dialog box, confirm you want to import a Table and click OK
    • Right-click any of the cells of the table and select Table / Edit DAX menu
    • In the Edit DAX dialog box change Command Type to DAX, the write the DAX query you want in the Expression text box, then click OK

    Of course, in order to write the DAX query I suggest you using a better editor such as DAX Studio (more info here: http://www.sqlbi.com/tools/dax-studio).

    Marco

    Thursday, January 23, 2014 4:13 PM
  • thanks but what i need is to get the full sql behind the complete

    schema of related tables i connected earlier in the power pivot model...

    what u suggested will refer to only one table from that model...

    am i right?

    Sunday, January 26, 2014 6:30 AM
  • Power Pivot sends to SQL one query per table - join between tables are made within the internal engine.

    If the schema allows you to do (e.g. yuo have a star schema) then your DAX query might involve all the tables and get a single flat table with columns of all the tables in JOIN - but in any case there is no SQL code generated for such a query.

    Marco

    • Marked as answer by reapeleg Sunday, January 26, 2014 8:11 AM
    Sunday, January 26, 2014 6:50 AM