Hi community members!
I need some high level advice/guidance for a project I am working in with PowerBI. Let me describre the situation.
I am using PowerBI to transform some output logs of my company systems to create some valuable insights. For this, I have a report that uses this files as source and build many tables in my model and then use them for many visuals in different pages of the report. This part is basically solved and have no issues here ( i can identify what info I need from the source files and transform them accordingly with powerquery to get what I want).
Now the next step was to make this tool more scalable and accesible to all the colleagues that want to use it instead of just me. In order to do this I went from a folder data source to a dedicated sharepoint where I created the folder structure and adapated the powerbi model to it. With this in mind the way the tool works is:
- You load the necessary files into the sharepoint folders.
- Each person has their own "version" of the tool with a PowerAutomate button that triggers a PowerAutomate flow to update the associated dataset.
This state works but you can probably see why it is not really functional. Since it is using import mode its forcing me to use this structure of "1 tool per person" and it involves a lot of app/sw interactions (smartsheet,powerautomate,powerbi). All this means this is definitely not as scalable and usable as I want it to be ( any new additions to the tool require to modifiy every single instance and when dealing with many source files, which can happen for big systems that generate hundreds of files, the load/delete operations in smartsheet are extremley clumsy).
The next step/solution I want to implement ( and this is where I need some help) is to somehow store the source information in SQL and use directquery. For this I have managed to create a way to inject the information in the original files into an SQL server. The problem is that this files are very unstructured data sources and require some heavy data transformation proceses (The queries for some of the tables of the model are more than 40+ steps). Another detail that needs to be taken into consideration is that all the systems are identical in terms of HW and SW and hence the log information they output is indistinguishable between customers. To tackle this my inital idea was to have each customer be a separate database in the SQL server ( for instance using the serial number as the name for that database) and inside this separate databases insert all the apropriate data. In this stage it is reasonably easy to make everything work with the help of M-Query parameters to change the name of the database. The problem is that, as I mentioned, the data needs lots of transformations and quite many of them cant be done on the SQL and powerBI forces me to switch to import mode.
I have studied a bit the new features Dataflows and Datamarts but I am very new to them and not sure if i can leverage them to solve my issue. The only options I can think of right now are:
- Doing all the ETL outside of PowerBI (probably with python) and load the prepared data into the SQL and leave PowerBI just the visualization part.
- Somehow append to eeeevery single line I inject in the SQL a field which would be the serial number and then use Dataflows incremental refresh, do the transformations there with Powerquery and then use the Dataflow as datasource for the report.
I am not very convinced of either solution because the first one forces me to drop powerquery as the transformation tool ( and I feel very comfortable with it now) and the second one forces to word with aaall the data at the same time and relying on one column with the serial numbers to do the customer filtering (instead of only getting the data and transform it for the customer that you want to gather your insights from).
I am not sure if I did a good job of explaining everything correctly and sorry that it is just a “weird” help request. Also if the post belongs in other section of the forums let me know so I can move it accordingly.
Any help or tips are more than welcome and appreciated.
Many thanks, happy holidays!
