More Dashboard Magic
iTwins and PowerBI
Back in November 2021, I wrote a blog post how to keep your PowerBI reports (extracted from iTwins) always up to date.
Well, a lot of water has since flown down the river Thames and technology progressed a lot!
“Behold!” cried the wizard. “The power of automation! With this PowerBI connector, you shall never have to suffer through manual updates again!”
To set the stage…let’s say you have a bunch of BIM models — often in their hundreds — and you need to report quantities for scheduling purposes. And let’s say, you don’t have access to the original authoring applications. But you are required to keep the PowerBI dashboards up to date! Because the wizard (your boss) demands so.
Bring it on — no code this time, please!
In the previous post, the steps may have been easy for an advanced user, but a casual user might find them confusing. Rest assured, things are much easier now, and our development team did an amazing job. You’ll no longer have to tear your hair out battling with ECSQL. Now you can simply use TypeScript! And if you don’t know TypeScript, it’s time to learn something new ;)
Getting started — trust me, it’s fun
Did I say no code? Yeah well, there is some code involved but there is no need to be a ‘coding wizard’ at all — it’s easy.
Just head on over to https://www.itwinjs.org/getting-started/ where you will learn how to build a basic digital twin application. Simply follow the steps (and in no time) you have a fully functional web-based 3D viewer.
You can pick and choose from any of the above engineering formats. One, a few…or all of them! Just like a pick’n mix.
Once your viewer is up and running, it’s time to add the required npm packages.
and
Install commands:
npm i @itwin/grouping-mapping-widgetnpm i @itwin/reports-config-widget-reactOnce you install the two packages in Visual Studio Code, just add a few lines in the App.tsx file as described in the sample usage (which you can find in the doc links above). These packages will add widgets to prepare the PowerBI report data.
Enter — the Reporting Widget
The fundamentals of this widget are very well explained in this excellent article:
by Arnob Mallick.
He describes in-depth how to structure and organize your data using this widget. Arnob’s writing is inspired by Marie Kondo — the world’s most organized person. Last I heard; Marie gave up on being tidy — sorry Arnob 😊
One of my favorite features of the reporting widget is the calculated property functionality. This allows us to automatically calculate properties, such as volumes or lengths of elements which otherwise have no such information. Think of a basic 3D CAD file you received the other day — sold to you as a “BIM model”.
In this example, you can calculate the volume of the above concrete piles automatically. You don’t even need the CAD or BIM application to do that. And the best part! Whenever the iTwin receives updated model files, this calculated data will automatically be refreshed.
Reporting
Once the exercise above (grouping and mapping) is complete…it’s time to create a PowerBi report. You can pack as many mappings as you want into a single report. These will then show up in PowerBI as individual queries. Any modifications (or new additions) to the mappings will be processed as these changes are made.
Now to generate the report, you will need an APIKey to link the report into PowerBI. In PowerBI, just add a new OData source and paste the report URL into the entry box.
Select basic authentication, insert OData as the username and the generated APIKey as the password.
And that’s it! We have made it to the land of PowerBI visualizations. 🥳
“And so, they lived happily ever after, basking in the glow of their new found automation. They never forgot the wizard who had saved them from the clutches of manual updates, and they always made sure to leave offerings of keyboard cleaner and RAM sticks at his virtual shrine.”
Or just ping me and we’ll get you started in no time.
The end.
