SQL Server Analysis Services To Power BI

SQL Server Analysis Services to Power BI.png

1) Why is the BI Semantic Data Model Important ?

They're important because they model the real-world. Successful organizations unify the minds and hearts of the team and simplify the mantra they live by and this is exactly what the Semantic Model does for your Analytics - it unifies your organization-wide and cross platform data and simplifies the data-driven mantra that your team lives by.

He will win whose army is animated by the same spirit throughout all its ranks
— Sun Tzu

What tools do I need to build a SSAS based Semantic Data Model with Power BI ?

  • Microsoft Visual Studio
  • Power BI Desktop
  • A Power BI Pro license (for sharing the Dashboard with your organization)(optional)
  • Microsoft SQL Server with SQL Server Analysis Services

Below are a few key considerations that I believe place importance on a SSAS based BI Semantic Data Model.

Blog SSAS w Artboards-10.png

Unified Data Location

Geo-Location and the disparate nature of cross-platform data is still one of the biggest Analytics challenges faced by organizations wanting to build a data-driven culture. An ETL process precedes the BI Semantic Data Model and these then culminate in centralized data containing the business logic that matches your organizational culture.

Blog SSAS w Artboards-02.png

1 Version of the Truth

It's easier than you think, to measure something as simple as productivity, incorrectly - some revenue types may need to be excluded for example. In such cases, without one version of the truth, each person on the team could be measuring and acting on a different version of productivity measurement.

Blog SSAS w Artboards-07.png

Federated User Authentication

SQL Server Analysis Services respects your Active Directory (which synchronizes with Azure Active Directory if required) and AD in turn can drive user's access to datasets and to data as granular as individual records, using SSAS Row Level Security.

Blog SSAS w Artboards-05.png

Distributed Processing

Whether it is Power BI or any other data visualization service that you use atop of SSAS, the BI Semantic Data Model removes the data processing burden by shifting the processing to SSAS.

Blog SSAS w Artboards-04.png

Self-Governing Data Model

Governance is the comprehensive and understandable description of your Analytics. Disparate data needs separate documentation to bring it all together. Having your data and business logic in one place allows you to describe your data and the business logic inside your BI Semantic Model.

Blog SSAS w Artboards-06.png

Visualization Agnosis

Centralizing your organizational data allows your choice of Visualization Service (like power BI) to be almost 100% agnostic and to a lesser extent this agnostic benefit applies to your BI Semantic Data Model too.

Blog SSAS w Artboards-08.png

Consolidated Solution Maintenance

One BI Semantic Model means less moving parts, which means less maintenance is required and when troubleshooting is required, it is easier to troubleshoot in one environment.

Blog SSAS w Artboards-09.png

Data Model Reduction

SQL Server Analysis Services in 2016 has a very cool feature that allows a SSAS Model to consume another SSAS model. So.....hierarchies of data models are now possible. This reduction of data models opens up a list of benefits not the least of which is simplified data models.


2) What Steps are Required to deploy SSAS with Power BI?

It's real easy to build a basic SSAS to Power BI Solution. In the video and the steps below we've sequenced the the approach you could take to build your own BI Semantic Data Model using SSAS and Power BI.

These are the salient steps in your quest:

  1. Ensure you have the required software listed above
  2. Choose your data model type and connect to your database
  3. Model your data to match the business logic that your organization is accustomed to
  4. Point Power BI to your data model in SSAS and design your dashboard

 


Step-by-Step Guide

 1) Get and fire up Microsoft Visual Studio  On your local machine, you need to open Visual Studio SSDT and create a new 'Analysis Services Tabular Project'. This local template is where we'll design how the SSAS model works.   Note: The SSDT version of Visual Studio is different from the standard version of Visual Studio.  

1) Get and fire up Microsoft Visual Studio

On your local machine, you need to open Visual Studio SSDT and create a new 'Analysis Services Tabular Project'. This local template is where we'll design how the SSAS model works.

Note: The SSDT version of Visual Studio is different from the standard version of Visual Studio. 

 2) Select the type of SSAS Model  Select the type of SSAS data model. I'm partial to Tabular Data Models because I love using DAX over MDX for data modelling. As you see above, you can import from an existing Power Pivot Model, so no need to recreate those models you've sweated over.

2) Select the type of SSAS Model

Select the type of SSAS data model. I'm partial to Tabular Data Models because I love using DAX over MDX for data modelling. As you see above, you can import from an existing Power Pivot Model, so no need to recreate those models you've sweated over.

 3) Create the Analysis Services Instance  Choose 'Workspace Server' and set the Compatibility level to at least 1200. This ensures the model will be compatible with SSAS.

3) Create the Analysis Services Instance

Choose 'Workspace Server' and set the Compatibility level to at least 1200. This ensures the model will be compatible with SSAS.

 4) Connect to your data sources  When the blank model opens, right click on the 'Data Sources' folder and add a Data Source.  Once you've added a data source, right click on  that  resources and choose import data. From here follow the prompts to get data flowing into your model.

4) Connect to your data sources

When the blank model opens, right click on the 'Data Sources' folder and add a Data Source.  Once you've added a data source, right click on that resources and choose import data. From here follow the prompts to get data flowing into your model.

 5) Model your data  This is where the fun starts. Create logical joins between datasets and create some meaningful DAX (in the case of tabular SSAS models) or MDX (in the case of multi-dimensional SSAS models)

5) Model your data

This is where the fun starts. Create logical joins between datasets and create some meaningful DAX (in the case of tabular SSAS models) or MDX (in the case of multi-dimensional SSAS models)

 6) Build your dashboard in power BI  Use power BI to be as creative as you please. Your SSAS model can be consumed by any modern BI solution.

6) Build your dashboard in power BI

Use power BI to be as creative as you please. Your SSAS model can be consumed by any modern BI solution.


3) What is the Value Proposition of implementing a SSAS based BI Semantic Model for my Business?

In addition to the business value derived from the features of using SSAS as the platform for your BI Semantic Model listed at the start of this post, there are 3 value propositions that I think are important to consider.....

  1. TIME: When comparing the time to deploy and manage multiple data sources and multiple repositories of your business logic, the time saving of centralizing these activities is significant. Hardware and consequently time saved through improved performance can save significant time in getting processed data delivered to your team.
  2. COST: In addition to the costs associated with the time savings above, significant costs are saved through timely data delivery and the ability to focus platform investments, data model enhancements and maintenance in one centralized location.
  3. ANALYTICS AVAILABLE: A SSAS based BI Semantic Model is capable of delivering the best analytics available to organizations through almost unlimited data connectivity, in-memory processing and advanced data modelling.

 
FreshBI - Craig Juta.png

Author, Craig Juta

OCCUPATION:
Speaker, Solution Architect - Business Analytics

EDUCATION:
Degree in Accounting and Information Technology.

EXPERIENCE:
Extensive horizontal experience in crafting business analytics solutions

COMPANY:
FreshBI

Team FreshBI is an innovation machine and the blogs that Craig writes mostly emanate from the innovations that FreshBI does for its customers.

 

Our Latest Blogs

About FreshBI

Based in Canada, South Africa and in the United Kingdom, we have helped hundreds of businesses achieve excellence & success through business intelligence.

Power BI runs in our blood, and we are ready to take your business to next level.

craig jutaPower BI