After some initial testing on small models which were quite impressive, I decided to put the new Genie skill to the full test with a more advanced model in Power BI.
Be aware that the /ImportBI feature is in Beta. This means that there are ongoing changes happening, and that the final product might be different to what you are experiencing today!
Below, you can catch the full 45 minute experience of me playing around with in on YouTube. You get all of my honest takes, my confusions when something does not work out the way I think, as well as a lot of me rambling about on some of the things I find along the way.
However, if you want the slightly shorter recap, this article might suit you better!
Setting up the model
First, I created a semantic model and report myself using the Tabular Editor SpaceParts dataset, that you can find on the Databricks Marketplace. Huge shoutout to TE team for putting a real-scale demo dataset out there, that has a great coverage of data, as well as some intentional design "mistakes" that you might find in the real world!
Then, after showing it to my colleague David Kofod Hanna, he was not too impressed with my out-of-data Power BI skills. Lucky for me (and you!) he was kind enough to help me develop a better model and report to run the tests on. Massive shoutout to David for that!
A quick overview of the report pages can be seen in the below animation. The model itself contains 4 facts and 11 dimensions, as well as some more advanced things that are highlighted below. For visualizations we use the following:
KPI Cards (with categories, and reference values/labels)
Slicers (Regular and Input)
Matrix Table (using Field Parameters - more on those below)
Bar Chart (Clustered)
Area Chart
... and then some buttons/text boxes if you count those as well.
Some advanced areas to test
So, in order to devise a proper test, the Power BI model contains a couple of elements that are essential to the way we develop and work with Power BI on a larger scale, I would consider them best practices at least in our ways of working at twoday. Some of these are more visual-based, while others are more focused on the model itself. The ones I want to highlight in this test case are:
Calculation Groups; In this case there are two. One for Time Intelligence, and one for Unit Conversion that are used across different visuals
Field Parameters; these are used for running the Pivot Table Analyze page, in which users can switch between measures and dimensions themselves in the Pivot Table
Model Documentation; the model has a built-in documentation page using the INFO.VIEW DAX functions. This tells the end-user how things are calculated as a sort of business glossary.
Information measures; here I am referring to the concept of adding measures to show e.g. Last Refresh, Filters Applied and other nice-to-know information
Understanding how /ImportBI works
The primary use case for ImportBI is teams already running Power BI or Tableau with Databricks as their primary data source. If your M queries simply point to Databricks catalog tables with no transformation logic, you're in the best position to benefit.
ImportBI is triggered via the Skill /importBI inside of Genie Code. It works mostly autonomously and assists in analyzing the model and planning what needs to be done. After approval it then translates measures, and builds dashboards in parallel before presenting you with the final output in both code and a small report.
If you have not seen the long-form video, and want a quicker rundown of how ImportBI works, this 5 Minute Features version might be more your style. In the video, I break down what ImportBI does and how, using a very simple Power BI model.
The workloads carried out by ImportBI can be split into 3 phases, as described below:
Setup is the first phase, in which we instruct Genie in what type of BI solution we have (PowerBI or Tableau) as well as direct it to a volume where it can find the file. In case of PowerBI it relies on the PBIT format. We also supply a catalog and schema for both the source (where the data lives) and the target (where it will create the Metric Views).
Analyzing and Planning is the phase where Genie unpacks and understands the existing model. It breaks down both the model itself, but also the visual side, and writes up a short report breaking down what it plans to do with each part - including anything that it will omit from the migration - either because it is unused, or because it is not directly migratable.
Metric Views and Dashboards is the actual migration phase. After approval of the plan in the previous step, Genie gets to work. It starts by developing the Metric Views, and then subsequently it does the Dashboard work.
As with any Genie Code session, you can intervene, redirect or change the plan and work carried out along the way. The above is just the general path that the skills takes with no redirection or additional instructions.
The results are in
So, as you know by now, I have run two different tests. One on a simple model, and one on a complex model. This will mainly discuss the results of the complex one, as that is by far the most interesting.
We will cover the Semantic Layer and the Visual Layer separately, and then sum up, evaluate, and then you can find my final verdict at the bottom.
Semantic Model to Metric Views
What works well, is understand the wiring between Databricks and Unity Catalog and does the basics:
The M expressions are parsed, and broken down to their correct counterpart catalog.schema.table setup, and added to the Metric Views sources and joins. I am unsure how much of this is derived from Unity Catalog directly, or if e.g. join columns are derived from the model design in PowerBI
Each Fact Table is created as its own metric view, all the fields are correctly identified
All generic/simple measures are created correctly
It references cross-measure references, and creates measures using other measures
Includes the original DAX expression as a comment to the new measures - ensuring traceability
The active filters footer and last refresh timestamp measures are not re-created. This might seem like a drawback, but actually both are built into the AIBI Dashboard UI natively which is why they are not there.
What does not work so well, it is honestly almost all of the more advanced stuff, as well as a few basics:
There is no Calculation Group equivalent concept in Databricks Metric Views. The workaround, that I myself prompted Genie to do, is creating static YTD, QTD, and MTD measures per metric. However, this contradicts the dynamic "inject any measure into any time window" flexibility of Calculation Groups.
Field Parameters are completely omitted - there is no style of dynamic parameter-based
Cross-fact measures e.g. comparing values across fact tables isn't currently possible. Dashboard relationships are in private preview, and might enable this soon though.
USERPRINCIPALNAME() as a measure is not handled. As far as I know, a workaround for a similar meaure exists in Databricks but the tool doesn't auto-detect it.
Only one of the four created Metric Views was added to the dashboard, the others had to be added manually. I believe this is because it created some of the measures on the wrong facts - therefore not needing the other ones directly.
Power BI Dashboard to AIBI Dashboard
What works well, is understand the visuals chosen and selecting the right fields:
The classic BI visuals are replaced correctly, choosing card, bar chart and area chart, as well as the slicers. This works for almost all of the visuals, only one is wrong.
The right measures/dimensions are added to the visuals
What does not work so well, is the actual attention to detail:
The visual placement is all over the place. The filters are below the visuals and the grid-style of AIBI Dashboards limits the flexibility a bit.
Genie added cards that did not exist in the original layout. Not entirely sure why, as there is not indication we would want invoice Cost and Quantity.
The filters are not all added on both pages. Despite Global filters being a thing, the Genie Code approach was to add them directly on page. Not a problem in itself, but it split them, adding two on each instead of all four filters on both
Power BI's ability to split a single card visual by a dimension into multiple cards doesn't exist in AI/BI dashboards. For some reason, it decided to do a Bar-chart instead.
The two later pages with Documentation and Pivot Table are not created at all. This is not a visual problem, as they are not created because there is not a direct replacement for them on the semantic model level. I did however manage to do the Documentation - catch that at the end!
The re-created AIBI Dashboard pages. As you can tell, they are not really up to scratch.
My personal verdict
For simple models with built directly on Databricks, with no advanced DAX patterns, ImportBI can meaningfully accelerate the transition. However, how many of those type of solutions do you really have in an enterprise context? For anything production-grade using features like time intelligence, field parameters, complex visual layouts, cross-fact calculations, you should expect a significant amount of manual work after the import.
For now, ImportBI is a useful starting point, not a full-scale migration tool
Remembering that this is still a Beta feature, I know my critique might seem a little harsh. I sincerely hope that some of these kinks get worked out, and honestly, some of them might even already be fixed when you read this.
To me, some of the most jarring things that need to get worked out is actually in the Dashboard part. Filters that are left out on one page but added to another. Visuals that are not placed correctly, or are not the right type is a real issue. The fact that it does not seems to understand when to add what to a page is a real issue when it claims to be able to do something.
On the Metric Views side, there are some obvious shortcomings related to cross-fact measures that needs to be solved elsewhere, but other than that, most of this part seems solid. I would however love to see some sort of a dynamic approach to time intelligence and/or parameter swapping as part of the intersection between the dashboard and metric views experience.
The important part, to me, is that tool is self-aware of its gaps and shortcomings. During the planning phase, it flags what it's skipped and why, and its migration report gives you a clear checklist of what needs your attention. That level of transparency is something I can appreciate - and it also serves as a good guide for what Databricks might choose to work on in the future.
My overall verdict of this as a migration tool for Power BI workloads is a score of: 4/10
... an unexpected bonus: Model Documentation
One useful discovery I made, was that we are actually able to do the documentation setup with only a tiny bit of additional work.
This was done in parallel, in another Genie session, but proved to be quite valuable nonetheless. The main point here being that ImportBI had no actual stake in this.
Because Databricks stores Metric View definitions in a queryable format, we are able to easily extract the YAML definition in code. This is done by querying the information_schema tables. From here we can extract each measure's expression, description, comment and window function configuration, directly from the YAML.
Then, by writing this to a Delta table, slapping a Metric View on top, and adding a simple Table Visual in a Dashboard, you can get a dynamic documentation layer, as shown below:
I believe the Documentation topic deserves a bit of additional attention. So expect a full write-up on it later on with all the code included for you to work with it yourself!
Announcements
