Table Restructuring Options in PowerBI

Question

I was just given a PowerBI file that gets its data from some really messed up views in SQL Server. Basically, the database they're getting their data from is an OLTP database, and they don't seem to understand dimensional modeling at all. So using this "design" is a pretty challenging. Maybe I have to go back to whoever has access to the database and have them create views for me that are sane.

Is that my only option, or can I export this junk from say a tablix on a page and then export that to CSV and fix from there?

Answer 1

It's not unusual for PowerBI to get data from an OLTP database. The whole point of Power BI is that you can create a semantic data model. Use Power Query to create the appropriate denormalised fact and dimension tables and then create the relationships in the model. Introducing a CSV is unnecessary if you have access to the database as you can take advantage of query folding.

Answer 2

The scenario you're describing is a very common scenario in datamodelling - I'm saying that as a data scientist myself at the moment. With various clients I'm doing this: creating your own layer in the middle. Basically you import the data from the OLTP db into your own db and write your own SQL views. After that, import that into PowerBI therefore saving you tons of work, as SQL structures data way better and more efficiently than in PowerQuery - hence the name, Structured Query Language.

Will your source be outdated then? Not if you schedule your refresh using SQL Agent or use something like Azure Data Factory.

Otherwise, you can also ofcourse give your own created views on your intermediate to the one managing the OLTP db, asking them to implement them (or maybe even giving you DDL rights for example on your own schema, so you can implement your views yourself)

Hoping to have helped and love to hear!