COURSE OVERVIEW

Hexagons Background

Training Module:

Semantic Models

Learn about creating curated data marts and adding different ​endpoints to your Semantic Models

Geometrical Illustration

Table of ContentS

  • Key Concepts: SSL
    • What are Semantic Models?
    • What are Endpoints?
  • Build a Semantic Model
    • Add Order Date table to Sales model
    • Add Ship Date table to Sales model
    • Add Customer table to Sales model
    • Add Product table to Sales model
    • Auto-Suggested Relations
    • Add Sales Transactions to Sales model
  • Measures & Date Hierarchy
    • Add Measures to Semantic Model
    • Add Date Hierarchy
  • Add Semantic Endpoints
    • Use Tabular (CSV) Endpoint
    • (optional) Use Analysis Services (Power BI) Endpoint
    • (optional) Use Tableau Endpoint
    • (optional) Use Power BI Premium Endpoint
    • (optional) Use Qlik Endpoint


Hexagons Background

Key Concepts: SSL

what are semantic models?

The Semantic Model allows you to create department or purpose-specific models of your data, often referred to as "data marts". These models are intended to translate more technical field or table names into more familiar business terms like “product” or “revenue” to create more human-readable data for reporting. By providing only the most relevant data to each department or business unit, data consumers don't have to waste time sorting through all the data in the warehouse to find what they need. The models consist of multiple related tables similar to a star schema, and often include, calculated measures for commonly used KPIs.

what are endpoints ?

TimeXtender allows users to build a Semantic Model once and deploy it across multiple endpoints, so they will always provide consistent figures regardless of which visualization tool you use. This approach drastically improves data governance, quality, and consistency, ensuring all users are consuming a single version of truth, regardless if they are using Power BI, Analysis Services, Qlik, Tableau, or CSV.

Hexagons Background

Build a Semantic Model

Add Order Date table to Sales Model

  1. From MDW drag and drop the Date table to the ​Sales Semantic Model
    1. Name: Order Date
    2. Select Date Value and rename Semantic Field ​Name to Date
    3. Select YearMonthName and rename Semantic ​Field Name to Month
    4. Select YearQuarterName and rename Semantic ​Field Name to Quarter


Add Ship Date table to Sales Model

In the following steps you will be creating a Role-Playing Dimension. This is when a dimension table in the data warehouse can play a different "role" by relating to a different foreign key in the fact table. In this example, even though the date dimensions have all the same data, when relating to ShipDate, instead of OrderDate, it can filter and show different results.

  1. From MDW drag and drop the Date table to the ​Sales Semantic Model
    1. Name: Ship Date
    2. Select Date Value and rename Semantic Field ​Name to Date
    3. Select YearMonthName and rename Semantic ​Field Name to Month
    4. Select YearQuarterName and rename Semantic ​Field Name to Quarter
  1. From MDW drag and drop the Customer table ​to the Sales Semantic Model
    1. Name: Customer
    2. Select:
      1. AccountNumber
      2. CustomerName
      3. CustomerType
      4. PhoneNumber
      5. EmailAddress


  1. From MDW drag and drop the Product table ​to the Sales Semantic Model
    1. Name: Product
    2. Select:
      1. ProductModelName
      2. Color
      3. ProductName
      4. ProductCategoryName
      5. ProductSubcategoryName

Auto-suggested Relations

Circle Pin Icon
White Circle Vector
Cute Organic Light Bulb

Primary Key

After adding a table to a Semantic Model, TimeXtender will auto-suggest table relationship in the following scenarios.

Relations in the MDW

A relationship exists in the data warehouse with any other table in the model.

Identical Primary Key Field Name

The primary key field on one table matches the name of a field on any other table in the model.

Identical Field name

An Identically named field exists in any other table in the model.

Add Sales Transactions to Sales Model

  1. From MDW drag and drop the SalesTransactions table to the Sales Semantic Model
    1. Name: Sales Transactions
    2. Select:
      1. SalesOrderID
      2. OrderQty
      3. UnitPrice
      4. Order Date
      5. ShipDate
      6. GrossLineTotal
    3. Setup Relations
      1. Order Date: Existing Relation 'Date_OrderDate'
      2. Ship Date: Existing Relation 'Date_ShipDate'
      3. Customer: Primary Key on 'Customer' (One to Many)
      4. Product: Primary Key on 'Product' (One to Many)
Hexagons Background

Measures & Date Hierarchy

Add Measures to Semantic Model

Add a table Measure

  1. Right click a table in semantic model and click Add Measure.
  2. In the Name and Description box, type a name and description for the measure.
  3. In the Type list, select Row Count (The value will be the number of rows in the table).
  4. In the Data format list, click on the data format you want the field to have and click Ok

Pro Tip - SSAS Tabular & Power BI Premium only:


The fields for Description, Data type, Data format setting for adding a measure applies to SQL Server Analysis Services (SSAS) Tabular and Power BI Premium endpoints only.

Add a field Measure

  1. Right click the desired field under the table in the semantic model and click Add Measure.
  2. In the Name and Description box, type a name and description for the measure.
  3. In the Type list, select Sum or any other desired type from the list.
  4. In the Data type list, select Decimal or the desired data type.
  5. In the Data format list, click on the data format you want the field to have and click OK.

Add a custom Measure

  1. Right click a table in semantic model and click Add Measure.
  2. In the Name and Description box, type a name and description for the measure.
  3. In the Type list, select Row Count. (The value will be the number of rows in the table).
  4. In the Data format list, click on the data format you want the field to have and click OK

Add Date Hierarchy

To create a date hierarchy, you must first create a date table then deploy and execute, you can complete the following to create a data hierarchy.


Please see Date Table tutorial for more information.

  1. Edit the MonthName field and set the “Sort by” setting to use the MonthKey, which is the integer number of the month.
  2. Right-click the data table and select “Add Hierarchy” from the context menu.
  3. In the Add Hierarchy window, select your date fields from largest to smallest.
  4. Once your date fields are added, click OK to save the new hierarchy.
Hexagons Background

Add Semantic ENdpoints

Use Tabular (CSV) Endpoint

Create Endpoint in Portal

  1. Click Data Estate > Instances
  2. Open the Sales instance and click Edit
  3. Add an Endpoint:
    1. Select Tabular Endpoint and click Add
      1. Name: SalesTabular
      2. Server: Localhost
      3. Database: Sales
      4. Click Save

Deploy & Execute in TIMEXTENDER

  1. Right-click the Semantic model > Deploy and Execute
  2. Right-click CSV Endpoint > Open Directory
  3. Explore the Files

Use Analysis Services (power BI) Endpoint

Create Endpoint in Portal

  1. Click Data Estate > Instances
  2. Open the Sales instance and click Edit
  3. Add an Endpoint:
    1. Select Tabular Endpoint and click Add
      1. Name: SalesTabular
      2. Server: Localhost
      3. Database: Sales
      4. Click Save

Deploy & Execute in TIMEXTENDER

  1. Refresh and Deploy and Execute Semantic Model

Connect Power BI

  1. Click Get Data > Analysis Services
    1. Server: localhost
    2. Click OK
      1. Select Sales > SalesDev
    3. Click Models to see complete Relationship
    4. Build a dashboard and have fun!

Use Tableau Endpoint

Create TaBleau Endpoint in Portal

  1. Click Data Estate > Instances
  2. Open the Sales Dev instance and click Edit
  3. Add an Endpoint:
    1. Select Tabular Endpoint and click Add
      1. Name: Tableau
      2. File: Sales.tds
      3. View Schema: Sales
      4. Click Save

Attention Snowflake Users: If the Tableau endpoint uses data from a MDW instance with Snowflake storage follow these additional steps (not shown here).

Deploy & Execute in TIMEXTENDER

  1. Refresh and right-click the new endpoint, Tableau, and select Deploy

Open Tableau TDS from TimeXtender Desktop

  1. Double click the generated .tds file in the folder mentioned in the portal.
  2. Enter your credentials and create your worksheets.

Another way to open the .tds file is through the desktop application. Right click the Tableau endpoint and click on the name of the generated .tds file

Use Power BI Premium Endpoint

Create Endpoint in Portal

  1. Click Data Estate > Instances
  2. Open the Sales Dev instance and click Edit
  3. Add an Endpoint:
    1. Select PowerBI Premium Endpoint and click Add
      1. Name: PowerBiPremiumtraining
      2. Server: Enter server details
      3. Dataset: SalesModel
      4. username - Enter your username
      5. password- Password
      6. Click Save

Deploy & Execute in TIMEXTENDER

  1. Refresh and Deploy and Execute Semantic Model

Use Qlik Endpoint

Create Endpoint in Portal

  1. Click Data Estate > Instances
  2. Open the SSL instance and click Edit
  3. Add an Endpoint:
    1. Select Qlik Endpoint and click Add
      1. Name: Sense Sales
      2. Qlik application: Qlik Sense Desktop
      3. Enter other relevant information


Deploy & Execute in TIMEXTENDER

  1. Refresh and Deploy and Execute Semantic Model

Connect Qlik

  1. Open and launch Qlik Desktop Application.
  2. Open TimeXtender-generated app displayed in Qlik Sense Desktop hub.
  3. Select Data model viewer from the Prepare drop down to view your model.

exercise Connect Power BI to ​analysis services model

Use what you've learned so far to complete the following exercise.

  1. Deploy and Execute your tabular endpoint for the Sales semantic model.
  2. Open Power BI to connect to the analysis service using Get Data option
  3. Select the machine name or localhost in the server option and Database can ​be left blank.
  4. Add the measure field and dimensions to create a chart in Power Bi

Section Quiz...

Planning to take the Solution Architect exam? Then you want to be sure these questions are easy to answer.

TimeXtender Semantic Models support which Endpoints?

How do you create a Role-Playing Dimension in TimeXtender?

Can you create ​custom measures in ​SSL?

Brushstroke Arrow Smooth Curve Down Small

When you're ready, see Answers Bellow

Section Quiz Answers

Planning to take the Solution Architect exam? Then you want to be sure these questions are easy to answer.

TimeXtender Semantic Models support which Endpoints?


  1. Tabular
  2. Qlik
  3. Power BI Premium
  4. CSV
  5. Tableau

How do you create a Role-Playing Dimension in TimeXtender?


Add the same DWH table to the Semantic Model twice, then select different relationships to the Fact Table.

Can you create custom measures in SSL?



Yes! you can create custom measures using DAX scripts. Right-click the table select Add custom measure and enter your script.

want to Learn even more?

Learn even more about custom measures and time intelligence from TimeXtender Tuesdays

Congratulations! You've completed the training module

Semantic Models

give FEEDBACK

Thumbs Up Illustration
Thumbs Up Illustration