Setup Tabular Model from SQL Server Database or Power Pivot on Analysis Server


By:
Overview

The best way to run DAX queries against a Data Model is in the form of Tabular Analysis Services Database. The Data Model when deployed to Tabular Analysis Server becomes Analysis Services Database.

This section deals with the available options to set up Tabular Analysis Services Database so that it can be queried using DAX.

Tabular Analysis Database Setup Options

There are two ways to setup Tabular Analysis Database:

  1. Deploying Data Model to Tabular Analysis Server through SQL Server Data Tools (SSDT)
  2. Restoring Tabular Analysis Database from Power Pivot (created in Excel)

Method 1: Deploying Data Model through SQL Server Data Tools (SSDT)

Steps Summary

This method requires the following steps:

  1. Creating Analysis Services Tabular Model Project in Visual Studio
  2. Choosing Workspace Server option
  3. Creating and Populating a Sample SQL Database (OfficeSuppliesSampleV3)
  4. Importing SQL Database (OfficeSuppliesSampleV3) to Load Data Model
  5. Rename Date table as DimDate
  6. Creating relationship by Joining DimDate table with Orders table
  7. Adding a role (by adding your username) to solve access problems
  8. Deploy the Data Model to Analysis Server as a Tabular Analysis Services Database

Step 1: Creating Analysis Services Tabular Model Project

Create a new Analysis Services Tabular Project in Visual Studio SQL Server Data Tools (SSDT) and name it OfficeSuppliesSampleV3_Tabular:

analysis service tabular project

Step 2: Choosing Workspace Server Option

Choose Workspace Server option and point it to the installed Tabular Analysis Server:

choose workspace server

Step 3: Creating and Populating Sample SQL Database

Please run the following scripts to setup and populate SQL Database OfficeSuppliesSampleV3:

 
sample database

Step 4: Import SQL Database (just created) to Load Data Model

Import the database into the Tabular Model Project:

import sql database

Select desired database objects (tables) to be imported ignoring the OrdersView:

import objects

Step 5: Rename Date table as DimDate

Rename Date table as DimDate to avoid getting confused with Date reserved work.

Step 6: Creating relationship by Joining DimDate table with Orders table

Switch to Diagram View of Data Model and join Datetable Date_Key with Order_Date column in Orders table, once Date table is renamed as DimDate:

create relationships

Step 7: Adding a role (by adding your username) to solve access problems

Add a new role by clicking MODEL > Roles… form the main menu in Visual Studio:

add a new role

Next add your user name as a new Role by first clicking New button and then Add button is used to assign your user name to the freshly created Role as shown below:

role manager

Step 8: Deploy Data Model to the Analysis Server:

Finally deploy the model to the Analysis Server:

deploy model

For further information please go through the following previous tips:

Method 2: Deploying Data Model through Power Pivot

There is a more direct approach to deploy tabular model to Analysis Server from Power Pivot created in Excel.

Since the detailed steps of creating Power Pivot in Excel from a sample database is beyond the scope of this tutorial, we assume an Excel Power Pivot file has been crated from sample database OfficeSuppliesSampleV3.

Steps Summary

This method requires the following steps:

  1. Creating Excel Power Pivot based on OfficeSuppliesSampleV3
  2. Providing Analysis Server access to the Excel File
  3. Deploying OfficeSuppliesSampleV3 on Analysis Server by choosing Restore from Power Pivot option
  4. Adding a role (by adding your username) to solve access problems

Step 1: Creating Excel Power Pivot based on OfficeSuppliesSampleV3

Please download OfficeSuppliesSampleV3 Power Pivot Excel file:

Step 2: Providing Analysis Server access to the Excel File

Once we have created Power Pivot from Excel file it should be accessible by Analysis Server which is done by providing access rights to the file.

Right-click the Excel Power Pivot sheet and click Properties.

Under the Security Tab click Edit and then click Add:

provide analysis server access to power pivot

Click Advanced and then click Find Now and then locate Tabular Analysis Server in the list and click OK:

add analysis server access for power pivot

Step 3: Deploying OfficeSuppliesSampleV3 on Analysis Server

Connect to Tabular Analysis Server in SSMS (SQL Server Management Studio) and right-click Databases and then click Import from PowerPivot…

create analysis services database

Locate the path and file name of the Power Pivot file and click OK:

select path and file name

Restore database as OfficeSuppliesSampleV3-PowerPivot-Tabular:

select path of power pivot file

Step 4: Add New Role

Add new Role by providing yourself your username) access to this tabular database:

add new role





Comments For This Article

















get free sql tips
agree to terms