English
How to automate processes in a BI competence center using MS Analytics, October 2022
AnalyticsCreator automates BI competence centre processes by generating data warehouse code, documentation, historization, deployment artefacts, Azure Data Factory pipelines, and Power BI models from a graphical metadata model. The webinar shows how teams can standardize development, reduce manual work, support agile delivery, and deploy the same model on-premise or to Microsoft Azure.
Short answer
- How can AnalyticsCreator automate BI competence centre processes?
- How does AnalyticsCreator support agile data warehouse development?
- Can AnalyticsCreator generate documentation automatically?
- How does AnalyticsCreator automate historization?
- Can the same AnalyticsCreator model be deployed on-premise and to Azure?
- How does AnalyticsCreator generate Azure Data Factory and Power BI artefacts?
Key Takeaways
- AnalyticsCreator automates many manual BI competence centre processes.
- The application generates source code from a graphical metadata model.
- Teams can standardize naming conventions, architecture, field names, and generated code.
- Historization can be generated automatically instead of being hand-coded.
- Agile development is improved because teams can produce usable results in hours or days.
- Source metadata can be extracted and reused automatically.
- Graphical lineage helps business users understand data flows and KPI logic.
- Change management is simplified because changes can be made once and propagated across layers.
- Documentation can be generated automatically as Word or Visio documents.
- AnalyticsCreator is a design-time application with no runtime dependency.
- Generated code remains usable without AnalyticsCreator.
- The same model can be deployed on-premise, to Azure SQL Database, Azure Data Factory, and Power BI.
Transcript
Hello everyone, and thank you for joining the presentation “How to Automate Processes in a BI Competence Centre Using Microsoft Analytics and AnalyticsCreator.”
AnalyticsCreator is a data warehouse automation application. It automates not only the data warehouse, but also the analytical applications built on top of it.
AnalyticsCreator works as a source code generator. You work in a graphical user interface, and the result is generated code instead of manually programmed components.
One customer example is Robert Bosch. Together with Bosch, we decided to rebuild an existing data warehouse instead of modernising the old version, because the automated approach was faster and more efficient.
The result was three months of development instead of the originally estimated twelve months.
Another example is MyMuesli. They started from scratch, and AnalyticsCreator helped them begin their data warehouse journey and reach their goals much faster.
A further customer had many SAP instances and reported that they were 20 times faster with AnalyticsCreator.
In a BI competence centre, the first step is usually a requirement document.
With AnalyticsCreator, you can model a top-down view of the business. You can add dimensions, measures, and other structures without needing a live connection to source data or real data.
This creates a sandbox environment where you can explore the business model. The result can serve as a practical requirement document.
With AnalyticsCreator, you can standardise the naming of fields and tables. The generated code follows the same naming conventions consistently.
The architecture is also standardised, which helps teams work in a more structured and maintainable way.
AnalyticsCreator also handles master data historization automatically. You can choose between several historization concepts, and the required code is generated automatically.
AnalyticsCreator supports agile development because results can be created in hours or days instead of weeks.
When you connect to a source system, AnalyticsCreator can extract metadata such as field names, keys, foreign keys, and other structural information. This metadata is then integrated into the new database model automatically.
With AnalyticsCreator, development starts from a top-level view. Data flows are shown graphically, so you can always see where you are in the development process.
Change management is also simplified. You can change a field in one place, and the transformations in the following layers are updated automatically.
For modernisation, AnalyticsCreator can help generate source code for a new architecture or modelling approach. This makes it easier to evolve the data warehouse without rebuilding everything manually.
AnalyticsCreator provides an automated deployment process.
The generated source code is tested automatically, which helps identify issues earlier in the process.
AnalyticsCreator can also create Word and Visio documentation automatically. This makes handover easier because the holistic data model, source connections, fields, and structures are documented clearly.
With the holistic data model, handover is much faster than explaining the solution only at source-code level.
The AnalyticsCreator approach follows five main steps.
First, you connect to the database. Second, AnalyticsCreator extracts metadata from the source and creates a metadata layer.
Third, the intelligent wizard uses this metadata to propose a data model. Fourth, you optimise the model by adding business logic, dimensions, measures, joins, and transformations.
Fifth, you deploy the generated code and structures.
AnalyticsCreator is a pure design-time application. It is not required in the operational runtime of the data warehouse.
There is no runtime dependency and no vendor lock-in. AnalyticsCreator generates the code, and the customer can continue using that code independently.
Everything shown in the holistic model is stored in a Microsoft SQL database. This open repository can be inspected and extended when needed.
Dimitri starts AnalyticsCreator and creates a new repository called Cloud DWH.
This creates a repository database on the local SQL Server, which stores the information about the data warehouse.
For the demo, AdventureWorks 2019 is used as the data source. A new connector is added, and the AdventureWorks database is connected.
The Data Warehouse Wizard is used to create a draft data warehouse.
Tables from the Human Resources schema are selected, and a typical Kimball-style data warehouse is created.
The generated diagram shows the source layer, staging layer, persisted staging layer, core layer, and data mart layer.
In the staging layer, imported data is stored. Import packages define the mappings between source columns and target columns.
The persisted staging layer contains historized data.
The core layer transforms relational data into facts and dimensions. Regular transformations are generated as SQL views.
A fact transformation is created for Employee Department History.
Because the tables are historized, the transformation uses surrogate keys.
A calendar dimension is added to the fact transformation using macros. The fact transformations are then extended with calendar keys and persisted for performance.
The data mart layer defines how core layer objects are exposed to Power BI and OLAP models.
Calendar dimensions can be exposed multiple times, for example as Start Date, End Date, and Rate Change Date.
Measures are created in the data mart layer, and measure names can be generated using templates to keep them unique.
A deployment package is created.
It generates and deploys a DACPAC file to Azure SQL Database, generates Azure Data Factory pipelines, and deploys a Power BI tabular model.
The workflow pipeline is executed, and the data is loaded from AdventureWorks into Azure SQL Database. Power BI Quick Insights are then generated from the data.
