Advice needed: Automated refresh, modelling and archiving system with PowerBI

[Anonymous]

Hi all,

Rather new to PowerBI but I roughly know my way around. I don't think we're using PBI for what it might typically be used for, which makes learning harder.

My team is trying to identify the best way to automate some data refresh, modelling and archiving tasks. Unfortunately we also have to dodge access issues left right and centre.

Here is the plan as it stands. There are probably some dumb steps in here, but this is what I've drafted up so far:

1. Extract

We have a legacy OBIEE database which we can't directly query via PBI. Until this database is superseded, our interim solution is to schedule an Agent in the database to send an email to my email inbox with the result (.csv) of a prewritten query as an attachment. PowerAutomate grabs that attachment and dumps it into a Sharepoint location.

2. Transform/load

PowerAutomate then triggers a refresh of the PBI Report, which has been pointed to the aforementioned Sharepoint location for it's input data. Within PBI, the query cuts the input data up and creates new columns as required for the next step. Historically, we did this via manual copy/paste/edit into a sheet in excel. Yuck!

For auditability, we'd like a copy of the input data exported to .csv at this point.

Question 1: Is it possible to automatically export a data table from PBI to a sharepoint location as a .csv for archiving via PowerAutomate or otherwise? (there appear to be some options in Automate for exporting something from a report, but I can't make sense of how to set that up at the moment)

3. Modelling

We plan to have a DAX formula (half of which has been developed) run a handful of operations on this prepped data and produce results. This modelling consists some bespoke logic (unique to the type of asset being modelled) to assign ratings. For example, we might assign an expected life value based on various combinations of values of 5 text values. We then combine a bunch of other variables with calibrated algorithms to produce an aggregated health figure (as well as a few other output columns).

Historically, this stage has been achieved with some VBA Functions within the aforementioned excel workbook. Most of these functions are copied from a template we maintain and update. These functions are tweaked and upgraded as required when a new model is created. I've yet to recreate these functions in DAX, but I get the feeling it's a little trickier to make modular, fairly involved modelling functions the same way we did in VBA. I'll probably create a separate forum post about this concern when I get to it.

Hopefully this DAX formula produces a tidy table with relevant data inputs as well as these model results.

At this point we'd like to export a .csv to Sharepoint again. So we already have a backed up copy of the input data, now we want an archive of the output. That's two. Question 1 applies here too.

4. Visualisations and Analysis

Here we're doing bread and butter PBI visualisations on the output from Step 3 - nothing unusual here.

However, we would like to do the same step as above - a backup of the outputs here. Probably just a .pdf of all the visualisations we've prepared.

5. Logic Archiving

Up to this point, we have archived:

• A raw .csv of the OBIEE Query
• A .csv of the transformed data from PowerBI
• A .csv of the modelling (DAX) stage results
• A .pdf of the visualisations

The only thing missing for auditability of the entire process is the logic we've got throughout our .pbix file. Say we get a new condition attribute measured on all assets of a certain type. We'd edit the OBIEE Query to add a column (can't archive the query itself unfortunately), edit the .pbix to ETL this new column, edit the modelling stage to handle the new condition input - probably leave visualisations the same. End result is our model results will probably have a step change based on this new input.

If we make decisions based on the PowerBI model, we need to defend them to our auditors if they investigate the decision for whatever reason. Hence these updates to the PowerBI model need to be tracked.

Question 2: is it possible to archive a .pbix to a Sharepoint location via PowerAutomate or otherwise?

Question 2A: Is it possible to have "track changes" on this PBIX, in such a way that it would be easy to tell where changes have occured when referencing an archived report? I think I might be able to achieve this with a "Changelog" sharepoint list that's updated manually and synced when the flow runs, but have yet to investigate how this might work.

Question 3: If we were to use Document Sets in Sharepoint, would it be possible to have an archive of all the above files with version history that could be rolled back as a set?

Below is a diagram of the proposed architecture:


Draft architecture

There is a lot more to investigate, but I figure I should start with the big questions.

Any advice or commentary is appreciated.

Cheers,

Daniel

[Syndicate_Admin]

Hi Daniel,

If your goal is to connect Power BI to OBIEE (or OAC/OAS), you may consider trying the Power BI certified OBIEE connector.

The connector:

1. Connects to both OBIEE Subject Areas and Reports
2. Retains your Subject Area joins for reuse in Power BI
3. Works in both Import and Direct Query modes
4. Compatible with both Power BI Desktop and Server clients
5. Eliminates OBIEE/Power BI version compatibility issues

You may also want to check out the 9 best practices for the Power BI OBIEE integration, the connector's reference architecture, and how it addresses the security considerations.

[parry2k]

@Anonymous too much to answer, if you want we can set up a Teams/Zoom call and go over it. You can reach directly via email (it is in my signature)

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡