How To Import Tabular Model/Data into Excel PowerPivot?

Question

Hello,

Is this possible?  If so, what's best practice for importing both the model and the data?

Also, is it possible to import only "partial" fact data, so it does not blow up Excel?  Any limitation to Excel data size?

Your help is much appreciated!

Answer 1

Hi,

Migrating the model from SSAS Tabular to PowerPivot is not feasible at the moment. But, why you need to move it if you can report directly on SSAS tabular ? You can migrate to SSAS Tabular from Powerpivot easily using Visual Studio or SSMS.

Also, you can migrate the subset of data/table from SSAS to Powerpivot model using DAX/MDX. Easiest way is to connect via SQL Server Analysis Service in Powerpivoty to Tabular instance and user Wizard in Design mode to select subset of data you want to move. It will create a new Query table which can be used.

Cheers,

Mudit

Answer 2

The requirement is to bring "partial" tabular data into excel so we can showcase all the fabulous features of powerpivot and powerview offline (not relying on SSAS, SharePoint connections). 

Is there whitepaper on how best to bring partial Tabular data into PowerPivot?

You mentioned Wizard, and can you elaborate?  What are the steps?

Thanks for your help!

Answer 3

For calculated DAX metrics, is it possible to copy all the CODE from one model to another model in a separate Excel PowerPivot?

I know in Multi-dimensional, in Visual Studio, you can copy code for calculated metrics into another solution.  Is this possible for PowerPivot?

If so, how can this be done?

Thanks!

Answer 4

Ok, I have not seen any best practice document on the required implementation. If you are trying to achieve managed service BI the entire stack is recommended by Microsoft i.e. Tabular model in SSAS /PowerView /SharePoint.

In realistic scenario, I have implemented SSAS Tabular Model + PowerView showing off cool dashboards on PowerView using power of Tabular model SSAS at backend but perspectives are not supported in this case. Just open excel, From Other Sources-->From Analysis Services--> Choose Tabular SSAS instance --> Cube(not perspectives)--> At the end of new connection choose PowerView report and you are ready to build cool dashboard from SSAS Tabular Model.

In case you want to use Powerpivot/Powerview combination for self service BI, be aware that it can be used only for demo purposes or for individual analysis but not a good enterprise wide solution as its not scalable, secured, etc etc.

The good approach is to replicate a tabular model in Powerpivot from SQL server database if Datawarehouse relationships are optimal. It takes no time. Also as a part of moving some data/calculations from Tabular model to existing powerpivot model follow the steps -

1) Go to Powerpivot and click Manage

2) From other sources choose Microsoft Analysis Services

3) Wizard will open. Choose relevant SSAS Tabular instance and Cube and click Next

4) MDX wizard will open. Rather than typing MDX if you are not comfortable, you can use design mode and browse the data set you need for Powerpivot model. You can use Calculated measures defined in SSAS Tabular to move into Powerpivot and then rename a table and define relationships with existing Powerpivot model.

Hope this helps !!

Cheers,

Mudit

 

Answer 5

Take it into SSAS Tabular and then pull it into new Powerpivot model as defined in previous answer.

Why you want to create multiple PowerPivot models with same data model? Migrate to SSAS Tabular and share the model with different reports - the best practice..

Answer 6

The requirement is to have powerpivot "offline" mode so not relying on SSAS Tabular model.

Also, the goal is to use the same model as Tabular but with "Partial" data only so it does not blow up Excel.

I tried the MDX Import Wizard like you mentioned, but there are like 20 dimensions and so many custom calculated metrics that "errors out" when you simply try to write MDX or drag and drop in the wizard.  MDX script is not optimal solution here. 

Still trying to figure out a good way to replicate models.

We want to showcase all the fabulous features of powerpivot and powerview offline (not relying on SSAS, SharePoint connections). 

Thanks!

Answer 7

MDX script is a solution when you need a partial or a subset of data in existing Powerpivot model and not the efficient one if it is entire model. Moreover, there is no other solution provided by Microsoft as of now :(. 

Just for your information, in the MDX query editor you can also write DAX(if you are comfortable) which can migrate the entire dimension and fact tables comfortably with some painful limitations with calculated measures..

In case you have Geography table in your Tabular model type EVALUATE 'Geography' in MDX editor window and it will move the entire content to Powerpivot table. You can define relationships afterwards.

Answer 8

Will the MDX query be able to separate out all the dimensions (like 20 of them) so I can join them accordingly like my original Tabular model?

Weird that Microsoft does not allow model import.

Answer 9

Will the MDX query be able to separate out all the dimensions (like 20 of them) so I can join them accordingly like my original Tabular model?

Weird that Microsoft does not allow model import.

Hi LAWSQL,

I don't think we can use MDX query to perform the action. MDX query focus on query multidimensional objects, such as cubes, and return multidimensional cellsets that contain the cube's data.

Best Regards,

---

Elvis Long
TechNet Community Support