Training Module:

Semantic Models

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

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.

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

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
   

Add customer table to Sales Model

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

Add Product table to Sales Model

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

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)
   

Execute CSV Endpoint

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

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?

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