English
AnalyticsCreator Congress 2021 Ennoble automated Power BI models
Questions
- Why is a semantic layer important in Power BI?
- How can AnalyticsCreator support Power BI model generation?
- What should be removed from a Power BI model to improve performance?
- How do date tables support time intelligence in Power BI?
- How does incremental refresh improve large Power BI models?
- How can row-level security restrict Power BI data access?
Key Takeaways
- A semantic layer helps business users work with understandable fields, KPIs, hierarchies, and relationships.
- Power BI models should remove unused tables and columns to reduce memory usage and improve performance.
- Technical prefixes such as dimension or fact naming should be cleaned up for business-facing models.
- KPI tables can make measures easier to find and use.
- Date tables must be marked properly to enable Power BI time intelligence.
- DAX functions can calculate values such as same period last year.
- Descriptions and synonyms improve model usability and Power BI Q&A.
- Display folders help organise KPIs and fields.
- Hierarchies support drill-down reporting, for example year, month, and day.
- Incremental refresh is important for large fact tables.
- Row-level security can restrict visible data based on the logged-in user.
- Model design, naming, documentation, and performance tuning are essential after automatic generation.
Transcript
Hello everyone, my name is Robert Buch. I will present how we use AnalyticsCreator to support the creation of Power BI models and semantic layers. A semantic layer is important because it creates a bridge between technical data structures and business users.
Our demo project shows how we build a modern data warehouse without using real customer data. The architecture starts with source data, continues through ingestion, data lake storage, modelling, and semantic presentation, and can also include streaming data, data science, and machine learning components.
Most of our work is in Azure. For data ingestion, we mainly use Azure Data Factory pipelines. For semi-structured data, we also use Databricks to prepare data for the data lake.
We usually divide the data lake into raw, staged, and curated areas. Raw data is stored as received. Staged data adds technical structure. Curated data is prepared for business use and can be compared to a data mart.
The semantic layer sits between the technical database model and the user. It exposes understandable fields that users can use in Power BI, Excel, or other reporting tools. This is essential for user acceptance.
For the demo, we used a Microsoft sample sales database and adapted it into a beer brewing and sales scenario.
In AnalyticsCreator, we denormalised geographical information and added it to the customer dimension. We also connected the order line fact table to the calendar dimension so we could analyse data over time.
After deployment, the Power BI model still needs refinement. The first step is to remove tables and columns that are not required for the report. Power BI models are stored in memory, so reducing model size usually improves performance.
Naming is also important. Technical prefixes such as dim or technical terms such as “denormalised” should be removed. Business users need clear names, not implementation details.
Date handling is central in Power BI models. A fact table may contain several dates, such as order date and delivery date. You can either create multiple calendar tables or use inactive relationships with DAX functions such as USERELATIONSHIP.
To use time intelligence properly, the date table must be marked as a date table and must contain a continuous date range. Once configured, measures such as same period last year can be created and reused in reports.
KPIs should include clear descriptions so users understand what they mean and how they are calculated. Display folders can also help organise measures into groups such as current values, previous year values, or delta values.
Hierarchies support drill-down analysis, for example from year to month to day. Different departments may need different hierarchies, such as year-quarter-month or year-week-day.
Sorting also matters. Month names should not be sorted alphabetically, so we create a sort key, such as year plus month number, and use it as the sort column.
For large models, incremental refresh is essential. Instead of reloading all historical data every day, Power BI can store older data and refresh only recent periods. Row-level security is also important when users should only see data for specific customers, sales groups, or buying groups.
When creating Power BI models, pay attention to filter directions, performance tuning, documentation, DAX complexity, and aggregation tables. These topics become more important as data volumes and model complexity increase.
One practical report design tip is to create report backgrounds in PowerPoint, export them as images, and use them in Power BI. This makes it easier to structure reports with clear sections and navigation elements.
This was a short overview, but I hope it gave you a clear impression of why semantic modelling matters and how AnalyticsCreator can support Power BI model generation.
