An Example in SSAS and PowerBI
In this post, I introduced application in SSAS, PowerBI with live connection. This study contains two side. First side is SSAS (Analysis Services) Tabular. First side content is definition of data warehouse in SSMS (SQL Server Management Studio), addition database in SSAS in visual studio, connection relationship between tables are acquired and create sematic layer. Second side is live connection to Power BI from SSAS tabular semantic layers and presentation with exist data.
- SSAS Tabular Model
This stage contains created data model and relationship between table. As reference to below schema figure, SSAS tabular model is three stage in Big data architecture as Semantic Layer. It feeds from data warehouse. Data warehouse feeds from raw data or data source. Also, you can visit previous my post. (https://medium.com/@MustafaKaynak-/semantic-and-presentation-layer-of-big-data-56304b5a73ba)
https://venturebeat.com/2022/02/15/how-a-semantic-layer-bridges-bi-and-ai/
In this figure, Microsoft Analysis Server chapter marked. SSAS tabular model saved this chapter as tables. It feeds from SQL Server Database chapter as Databases. That database used an example acquire from Microsoft official web site as adventurework2019 database example. You can also download this Microsoft website [1].
First step in SSAS tabular model is definition a new data sources. This model is going to feed from this source. When your model finish, this model’s going to save analysis services step in SSMS. In this below figure, clicked data source on right page, then, table import wizard page require server name and database name. Other steps have called tables which are processed. Also, you can schedule of your model refresh with job schedule selection under the Microsoft SQL chapter. If you want to see detail of job schedule, you can visit this website. [2].
Second step is relationship between calling tables from sources. In this tabular model, product table is main or fact table, other tables are filter table or dimension table. This model is star schema. [3]. All dimension tables connect to fact(product) table as many to one connection type. You can see below figure.
Third step is finished all connection and deploy operation. You can click the start button and wait for finish deploy operation. When deployment finish, this model save in analysis services in SSMS.
- Microsoft Power BI presentation Layer
This stage has including live connection and presentation with some statistical visualization. Shown as below figure, first step is acquired data and analysis services click.
Then, opened navigator page, model in tabularProject10 that was saved in tabular model selected for visualization.
Last step is using card, bar chart, donut chart and table tools in power BI. Generally, design of dashboard as shown dashboard. Table of product detail in bottom page. Line chart or bar chart visual in top right. Donut chart on side with bar chart. On the left top side has commonly card and KPI shown as total income and total product quantity. This will change depend on time filter. You can see also by weekly or daily these numeric values and other graphics display. Also, you can see based on product transaction that are name or product id, product categories. These changing depends on filters.
Conclusion
In this study, product and detail table from adventurework2019 database shown in semantic layer and presentation layers. Table relationships and all table displayed in SSAS tabular model. Than, this tabular model connected on Power BI with live connection. As a result, this called data tables shown in dashboard example with some statistics visuals.
References
[2] https://help.kepion.com/hc/en-us/articles/360027751112-Process-OLAP-Database-with-SQL-Agent-Job
[3] https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
Author:
Mustafa Kaynak