Create Tabular Model Sample from SQL Server Database – Part 1



By:   |   Read Comments   |   Related Tips: > Analysis Services Development







Problem

As a SQL Server business intelligence developer, I would like to work directly on a tabular
analysis services project using a SQL database sample which must comply with data
warehouse sample requirements.  How can I prepare a database to be used for
tabular analysis?

Solution

The solution is to convert a SQL database sample into a tabular model sample
by making changes in such a way that it looks as if the data model has gone through
all the stages of a data warehouse and is ready to be used with a tabular analysis
services project.

SQL Database Sample Overview

Let’s have a look at the potential SQL database sample to be converted into tabular
data model sample.

Sample Database Structure (OfficeSuppliesSampleV2)

The sample database covers a common business scenario where customers can place
orders using multiple options such as email, phone or in-store to buy products.

The database consists of the following database objects (tables):

  1. Orders Table
  2. Product Table
  3. Customer Table
  4. OrderType Table

database diagram

The sample database also contains OrdersView and some other database objects
which are not important in the context of current article.

Setup Sample Database (OfficeSuppliesSampleV2)

Create and populate sample database (OfficeSuppliesSampleV2) using the
following scripts.

Test Run Sample Database (OfficeSuppliesSampleV2)

Once you have created the SQL database sample, it is worth doing a test run by
running the following script:

-- View orders 
SELECT
  ov.OrderID
 ,ov.OrderType
 ,ov.OrderDate
 ,ov.Customer
 ,ov.Product
 ,ov.Quantity
 ,ov.TotalPrice
FROM OrdersView ov

order view content


Once we have successfully created, populated and quick tested our sample database
next thing is to add the sample database to a tabular model project using SQL Server
Data Tools (SSDT).

Adding sample database to tabular model project consists of the following steps:











Steps Prerequisites/Supporting Information

  1. Creating Analysis Services Tabular Model Project in Visual Studio



SQL Server Data Tools (SSDT) for Visual Studio
is installed (which must
match your Visual Studio version)

  1. Choosing Integrated Workspace

Integrated Workspace server is available with the latest versions of
Visual Studio (For example, Visual Studio 2015 and Visual Studio 2017 offer
integrated workspace server) otherwise you have to install Tabular Analysis
Server on your dev machine.

  1. Compatibility Level

If you are using integrated workspace server then setting the compatibility
level to SQL Server 2017 (provided your Visual Studio version offers that
level of compatibility) makes your project compatible with Azure Analysis
Services.

  1. Setting up Data Source

Specify data source which is SQL Server Database in our case.

  1. Providing Credentials

Server name followed by your windows account user name and password
is provided which impersonates your account.

If you are pointing to the (installed) default instance of SQL Server then
your PC name is your SQL Server name For example if your pc name is “My-PC”
then your server name is “My-PC”. Default instance can also
be accessed by simply typing a dot “.”.

The desired database is connected after server and user credentials are
validated.

  1. Loading Data into Date Model

The data is loaded into the Data Model which can be one or more than
one database tables (including views).

  1. Switching to Diagram View

Diagram view shows the tables and the relationships between tables.

  1. Converting primary key columns (IDs) into alternate key(s)

The primary key columns of SQL database sample is not important (if
we are considering a data warehouse compliant sample) inside the data model
so we change them into alternate keys so that we can refer to them if required
later on.

Create Tabular Model Project

This tip assumes you have already installed SQL Server Data Tools (SSDT) and
have a basic understanding of creating and managing Analysis Services Tabular Projects.

Open Visual Studio and create a new Analysis Services Tabular Project and name
it “OfficeSuppliesSampleV2Tabular” as follows:

new project steps

Next choose “Integrated workspace” option and latest compatibility
level (if possible) and click OK:

tabular model designer

Tabular Project is ready as shown in Tabular Model Explorer:

tabular model explorer

Import SQL Server Sample Database

Click the Database icon on the toolbar and select SQL Server database from
the Get Data
window:

import sql server database

Next enter the server and database name:

database connection info

Enter credentials accordingly and carry on without encryption when asked since
this is a sample database:

database connection info

Next select the desired database (OfficeSuppliesSampleV2) and click “OK”:

database connection info

Loading Data into the Data Model

Select Orders, Customers and Product tables only and click Load button:

load data into model

Clicking the Load button will load data into tabular data model:

data processing

If we look at the model, we can now see the tables loaded into it:

loaded data

Switching to the Diagram View

From the Model menu Click Model View > Diagram View:

switching diagram view

The Diagram View shows tables and their relationships:

database diagram

Converting IDs into Alternate Keys

The database tables have been successfully loaded into the data model, but there
are some key things missing from the model.

If we assume that we are creating a data (warehouse) model which has gone through
all the basic stages of data warehouse creation, then we cannot ignore the importance
of alternate keys.

Yes, the primary key columns represented by IDs in the database world become
alternate key(s) in BI (Business Intelligence) world.

The relationships in a data warehouse are created using special types of keys
called surrogate keys rather than using primary keys.

Switch to Diagram View and right click on Customer and click “Rename”:

renaming keys

Rename CustomerID as CustomerAlternateKey:

add new key

Similarly Rename ProductID as ProductAlternateKey and OrderID as OrderAlternateKey:

database diagram updated

We have dropped the alternate key relationship(s) in our data model as per data
warehouse standard practices.

You must be wondering why we cannot use alternate key(s) to create relationships
(in the context data warehouse) while they are still unique, but think of a scenario
where data is extracted from multiple data sources (multiple databases, flat
files, Excel, etc.), before loading into the model, as a single table having same ids with different values (which
is quite possible) then they are no more unique so we cannot depend on these keys
anymore.

Please stay in tuned as we are going to transform the database sample into a
full fledge data warehouse compatible model sample in the next part of this tip.

Next Steps

  • Please try creating a new tabular project by adding any other SQL database
    sample of your choice and follow the steps mentioned in the tip.
  • Please try removing CustomerID primary key from the SQL database sample
    and add duplicate CustomerID values to the table to understand why data warehouse
    does not depend on data source ids (primary keys).
  • Please add Supplier table to the SQL database sample and populate it manually
    and follow the steps mentioned in this tip to make it a tabular model sample.


Last Update:


next webcast button

next tip button

About the author

MSSQLTips author Haroon Ashraf

Haroon Ashraf’s interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).

View all my tips


Leave a Reply

Your email address will not be published. Required fields are marked *

HashFlare