Watch the video demonstration of importing power pivot model to tabular model.
In the last post, we have discussed the difference between Power Pivot and Tabular Model.
Why do we need to Import Power Pivot in Tabular Model?
In a scenario where Business users create a prototype power pivot model for quick analysis but later on it need to be enhanced with more complex calculations or there is need of added features that power pivot does not support like scalability, manageability and security; we can import existing power pivot mode in tabular model .
We can use Visual Studio 2012 BIDS that gets installed with SQL Server 2012 installation. I have recently un-installed my VS 2012 and installed latest VS 2013. But it does not have Business Intelligence Development Studio (BIDS). Download BIDS for VS 2013 from Microsoft Site here or using SQL 2014 setup files and install it.
Key Points to remember before Import
- There is a limit of 10,000 rows that can be added to the data embedded (data in excel sheet and added to power pivot model using linked tables) in the tabular model. If you import a model from PowerPivot and see the error, “Data was truncated. Pasted tables cannot contain more than 10000 rows” you should revise the Power Pivot model by moving the embedded data into another data source, such as a table in SQL Server, and then re-import.
- If the tabular model instance is on local Analysis Service server, you can import both the metadata and data from Power Pivot workbook.
- If the tabular model instance is on remote Analysis Service server, you cannot import data from Power Pivot workbook.
Workspace in Tabular Model
A workspace database which reside in-memory is created on the Analysis Services instance, specified in the Workspace Server property, when you create a new Business Intelligence project by using one of the tabular model project templates in SQL Server Data Tools (BIDS) edlekarna.cz. Each tabular model project will have its own workspace database. You can use SQL Server Management Studio to view the workspace database on the Analysis Services server. The workspace database name includes the project name, followed by an underscore, followed by the username, followed by an underscore, followed by a GUID.
When project is closed:-
- Workspace is Kept in-memory, stored to disk and removed from memory (default)
- OR removed from memory and not stored on disk, as determined by the Workspace Retention property.
When you deploy a tabular model project, the deployed model database, which is essentially a copy of the workspace database, is created on the Analysis Services server instance specified in the Deployment Server property.
For more details on workspace and property, visit here.
Tip: While creating tabular model, import only subset of data to reduce processing time and consumption of workspace database resource.