Copying calculated columns and measures to other models

[sbroaden]

I am looking for a good way to keep multiple models in sync with each other.  With SSAS tabular we were able to use BISM normalizer which allowed us to view 2 models side by side and copy columns, measures, relationships, etc from one model to another with a click of a button.  It seems that the best way now, is to just open the 2 models and physically copy and paste from one to another.  We have several large models (over 2GB) that need to be kept in sync a little better and was hoping there was a better option than copying and pasting.  Does anyone know if something is in the works or are there best practices out there?  Has anyone heard if they might create a BISM normalizer option for Power BI models?

[MattAllington]

If they need to be in sync, do you really need 2?  Are they the same data?  Could you use a Golden dataset?  https://exceleratorbi.com.au/new-power-bi-reports-golden-dataset/

[sbroaden]

They are different cuts of the same data.  I would build one large 'golden model' but unfortunately it does not perform well as it would be about 5-7GB.  Also each of the models has specific custom metrics that only make sense in that model.  But about 75% of the calcs are similar between them and about 25% are custom.  We need to be able to sync up those 75%.

[Anonymous]

Interesting conversation. Reading through your comment that your data is "different cut of the same data" made me think about a YouTube video from the MS Data Insights seminar in June. It´s by Chris Webb and has the rather unexciting sounding name of "Working With Parameters And Functions". What Chris is demonstrating there might be what you are looking for, i.e. having one PBI model, but a parameter at load time determines what data is read into the model.

However he does get a question at the end of the video, if the user can change the parameter from the web UI, and I believe he answered no to that. This may, or may not be a deal breaker for you, but even if a BI developer had to set the parameters for each instance of your model I´m sure there would be a lot of time saved, and you would get to have a master copy with the 75% of measures all synced up.

What caused me to find your thread was the mention of BISM normalizer, which sounds very interesing. In another YouTube video (I believe from he same seminar) when talking about Azure Analysis Services there was a demo of how a Power BI model was merged into a corporate BI platform (and a 10 billion row taxi model) using BISM Normalizer allowing you to skip or include stuff. Not sure if that could also work with a Power BI model being both the source and target but maybe worth looking up. Unfortunately I don´t have the URL for that to include.

Hope to hear from you again about how you get on with this.

[sbroaden]

@Anonymous thanks for the info.  I will definitely watch those videos and determine if any of those would be an option.  I agree that if a user cannot change parameters I am not sure if that will work.  However, if it would involve the person running the model to just re-select parameters and save 5 different times instead of having to go in and copy and paste measures/columns different places, I still think that would be an advantage.  Our SSAS is on 2014 and doesn't enable us to have the cross filtering functionality.  So I cannot revert back to SSAS at this time until we are upgraded to 2016 at which point we may have to consider doing so in order to use the BISM.  We did use the BISM with 2014 and it was amazing.  Saved tons of time.  The other nice thing about using power bi for modeling instead of a more formal SSAS environment is that as business users, we can control when things are published and released instead of using the corporate change control policies which don't always apply to BI.  I will keep everyone posted.