Import from PowerPivot (SSAS Tabular)
This topic describes how to create a new tabular model project by importing the metadata and data from a PowerPivot workbook by using the Import from PowerPivot project template in SQL Server Data Tools (SSDT).
Create a new Tabular Model from a PowerPivot for Excel file
When creating a new tabular model project by importing from a PowerPivot workbook, the metadata that defines the structure of the workbook is used to create and define the structure of the tabular model project in SQL Server Data Tools. Objects such as tables, columns, measures, and relationships are retained and will appear in the tabular model project as they are in the PowerPivot workbook. No changes are made to the .xlsx workbook file.
Note
Tabular models do not support linked tables. When importing from a PowerPivot workbook that contains a linked table, linked table data is treated as copy\pasted data and stored in the Model.bim file. When viewing properties for a copy\pasted table, the Source Data property is disabled and the Table Properties dialog on the Table menu is disabled.
There are special considerations depending on whether or not the workspace database is on an Analysis Services instance on the same computer (local) as SQL Server Data Tools (SSDT) or is on a remote Analysis Services instance..
If the workspace database is on a local instance of Analysis Services, you can import both the metadata and data from the PowerPivot workbook. The metadata is copied from the workbook and used to create the tabular model project. The data is then copied from the workbook and stored in the project’s workspace database (except for copy/pasted data, which is stored in the Model.bim file).
If the workspace database is on a remote Analysis Services instance, you cannot import data from a PowerPivot for Excel workbook. You can still import the workbook metadata; however, this will cause a script to be run on the remote Analysis Services instance. You should only import metadata from a trusted PowerPivot workbook. Data must be imported from sources defined in the data source connections. Copy/pasted and linked table data in the PowerPivot workbook must be copied and pasted into the tabular model project.
To create a new tabular model project from a PowerPivot for Excel file
In SQL Server Data Tools, on the File menu, click New, and then click Project.
In the New Project dialog box, under Installed Templates, click Business Intelligence, and then click Import from PowerPivot.
In Name, type a name for the project, then specify a location and solution name, and then click OK.
In the Open dialog box, select the PowerPivot for Excel file that contains the model metadata and data you want to import, and then click Open.