This is the first part in a blog series of two. Go straight to part 2 here.
In order to create your new tabular model project, ensure that you meet the following prerequisites:
- Have an instance of SQL Server 2012 running in Tabular mode.
- Have SQL Server Data Tools (SSDT) installed as part of SQL Server 2012.
Microsoft provides sample databases. The one referenced in this blog is AdventureWorksDW2012 and can be downloaded from http://msftdbprodsamples.codeplex.com/releases/view/55330.
Tabular models are created by selecting SQL Server Data Tools under Microsoft SQL Server 2012. The new tabular model opens a new project dialog in the tabular model authoring environment (Visual Studio 2010 or later) where you specify the Analysis Services Tabular Project template under Business Intelligence Analysis Services. Once your project is created, it opens in SQL Server Data Tools and an empty model, Model.bim, appears in the model designer, ready for use.
When importing data into your model, you can select from many data sources including Microsoft SQL Server, Microsoft SQL Server Azure, Oracle, Access, Teradata and Sybase, just to name a few.
When selecting from a list of tables, you can rename them with friendly names. For instance, a table called “DimCustomer” can be renamed “Customer” or a table named “FactInternetSales” can be renamed “Internet Sales”.
You can filter the columns brought over with each table and omit unnecessary data from the import by selecting the table and clicking on the Preview & Filter button. You can then uncheck any columns that are not needed.
After the import completes, the model displays the tables as tabs and, by clicking on the tabs, you will see the columns imported. You may change the column names by simply double-clicking on the column name and typing in the new name; “CustomerKey” may be changed to “Customer ID”, etc.
The beauty of all of this is that what you are changing is metadata, and this never touches the tables in your database!
You will need to deploy your model in order to make use of it. Right-click on the project name, select “Properties” and you are presented with a Properties dialog box. For the Server property, type the name of an Analysis Services instance running in Tabular mode. This will be the instance to which your model will be deployed.
Once you have verified your settings, click “OK”. In the SQL Server Data Tools, click on the Build menu and select Deploy AW Internet Sales Tabular Model (the name of your project). The Deploy dialog box appears and, if any credentials are needed to access the database, you will be given the opportunity to enter them. Once your credentials have been verified, the deployment begins and you can watch the progress. Congratulations, you have just finished authoring and deploying your first Tabular model!
Now, let’s see how you can use your model to create a report in Excel!
In Excel 2013, every workbook can contain an internal Data Model that you can modify in Excel, in PowerPivot, and even in a Power View sheet in Excel. A workbook can contain only one internal Data Model, in this case we’ll be using our Tabular model.
Insert a blank sheet into the Excel 2013 workbook and select the Data tab from the ribbon. Select “From Other Sources” in the “Get External Data” section and then select “From Analysis Services”. The Data Connection Wizard dialog box appears. Enter the name of the SQL Server that was used for the model deployment and your credentials, if required.
Select the database and table from the next dialog box and click Next. In the final dialog box, enter a friendly description and click Finish. The Import Data dialog box then appears with a number of options of how to view the data within your workbook. For this demonstration, we will select Power View Report.
Excel opens a new Power View Sheet with the external data model in the Field List.
We’re going to create a simple report, Order Quantities by Model Name. In the Field List, check the checkbox first for ModelName in the Product table and then OrderQuantity from the Internet Sales. These fields are added to your report. Click where it says, “Click here to add a title” and type in “Order Quantities by Model Name”.
As simple as that, you have just created your first Power View report using your Tabular model! It only took minutes and, once the model is created, users can create these reports without having to ask IT to create them!
Coming up in Part 2, we will see how to add measures and expressions to your model to add more functionality!
For more information on Power View and Tabular models, please contact me at (760) 930-6400 or firstname.lastname@example.org
BI Developer, FMT Consultants
Posted by: Jakob Bechgaard