In this demonstration, you will see how to create a tabular Analysis Services database from an existing PowerPivot workbook.
Create a Tabular Data Model Project
1. If you did not complete the previous demonstration in this module, start the MIA-DC1 and MIA- SQLBI virtual machines, and log into the MIA-SQLBI virtual machine as
ADVENTUREWORKS\Student with the password Pa$$w0rd. Then in the D:\Demofiles folder, right- click Setup.cmd and click Run as administrator. When prompted, click Yes.
2. Click Start, click All Programs, click Microsoft SQL Server 2012, and then click SQL Server SQL Server Data Tools.
3. On the Start Page tab, click New Project.
4. In the New Project dialog box, in the Installed Templates pane, expand Business Intelligence, click Analysis Services, and then click Import from PowerPivot.
5. In the Name box, type Sales, and then click Browse
6. Click Browse, navigate to the D:\Demofiles\Mod04 folder, and then click Select Folder.
7. In the New Project dialog box, click OK. If the Workspace and Deployment Server Configuration dialog box is displayed, in the Default Server list, select localhost\SQL2, and then click OK (this dialog box is only displayed the first time you create a tabular model project).
8. In the Open dialog box, navigate to the D:\Demofiles\Mod04 folder, click Sales.xlsx, and then click Open. If a message notifies you that data cannot be imported, click Yes to import the metadata. 9. Wait until the creation of the Sales project completes. Note that the data model in the project
includes a tab for each table in the model.
10. On the Tools menu, click Options, and in the Options dialog box, expand Analysis Services and click Data Modeling. Note the name of the default workspace server used to host the data model
Configure a Connection
1. On the Model menu, click Existing Connections, and then in the Existing Connections dialog box, with the SqlServer MIA-SQLBI AdventureWorksDW connection selected, click Edit.
2. In the Edit Connection dialog box, click Impersonation. Then select Specific Windows user name and password, enter ADVENTUREWORKS\ServiceAcct and Pa$$w0rd, and click OK.
3. In the Edit Connection dialog box, click Save. Then in the Existing Connections dialog box, with the SqlServer MIA-SQLBI AdventureWorksDW connection selected, click Process to import the data.
4. When processing is complete, in the Data Processing dialog box click Close, and then in the Existing Connections dialog box, Close.
Edit a Tabular Model in SQL Server Data Tools
1. In the Model.bim designer window, click the Reseller Sales tab.
2. In the Measure Grid pane (the grid area under the data pane), click any empty cell, in the formula bar, type the following expression, and then press Enter.:
Profit:=SUM('Reseller Sales'[SalesAmount]) - SUM('Reseller Sales'[TotalProductCost]) 3. Note that the profit measure is calculated, and then on the File menu, click Save All.
Deploy a Tabular Database
1. On the Build menu, click Deploy Sales. If prompted, enter the user name ADVENTUREWORKS\ServiceAcct and the password Pa$$w0rd and click OK.
2. In the Deploy dialog box, when deployment is complete, click Close. Then close SQL Server Data Tools.
Create a BI Semantic Model Connection in SharePoint
1. Start Internet Explorer and browse to the SharePoint site at http://mia-sqlbi. Then on the Home page, under Library, click PowerPivot Gallery.
2. On the SharePoint ribbon, in the Library Tools section, click Library. 3. In the Settings area, click Library Settings.
4. Under the General Settings area, if a Content Types section exists, review the content types enabled for this document library. If no Content Types section exists or the BI Semantic Model Connection is not listed, perform the following steps to enable management of content types and add it.
1. In General Settings, click Advanced settings.
2. In Content Types, in the Allow management of content types? section, click Yes, and then click OK.
3. On the Document Library Settings page, in the Content Types section, click Add from existing site content types.
4. In the Select site content types from list, click Business Intelligence.
5. In the Available Site Content Types list, click BI Semantic Model Connection, click Add, and then click OK.
5. On the Document Library Settings page, under Libraries, click PowerPivot Gallery. 6. On the SharePoint ribbon, in the Library Tools section, click Documents. Then in the New
Document drop-down list, click BI Semantic Model Connection.
7. In the New BI Semantic Model Connection page, in the File Name box, type Sales; in the Description box, type Sales data model; in the Workbook URL or Server Name box, type localhost\SQL2; in the Database (if connecting to a server) box, type Sales; and click OK. After a few moments, the Sales connection is shown in the PowerPivot Gallery document library
8. On PowerPivot Gallery page, click the Open New Excel Workbook icon for the Sales BI semantic model connection.
9. When prompted, click Open. Then, when the download has completed, click Open again. 10. In Excel, in the Microsoft Excel Security Notice dialog box, click Enable.
11. In the PivotTable Field List pane, select Profit. Then in the Date table, select Calendar Year. Note that the PivotTable shows profit by year.