SQL Server Analysis Services To Power BI
Why is the BI Semantic Data Model Important ?
Having a Bi Semantic layer sets the stage for the collaborative composition that is modern BI. Successful organizations unify the hearts and minds of the team, they 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 culture that your team lives by.
What tools do I need to build a SSAS based Semantic Data Model with Power BI?
Microsoft Visual Studio
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.
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.
One 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.
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.
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.
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.
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.
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.
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.
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:
Ensure you have the required software listed above
Choose your data model type and connect to your database
Model your data to match the business logic that your organization is accustomed to
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.
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.
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)
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.
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.....
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.
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.
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.
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.