Updating issue

[smpa01]

@TomMartens @Greg_Deckler @bcdobbs @AlexisOlson 

I have a datset existing on service with incremental refresh for nearly all the tables.

I often find myself needing to

1. Add new tables from new sources to that BI

2. Apply incremental refresh on the above sources

3. Edit/Delete Existing measure

4. Create new Measure

What is the most convenient way for me to deploy those changes to Prod without destroying existing partitons?

I currently do that with a DEV copy which does not have incremental configured initially. Once I bring on those changes to te DEV, I recreate incremental on all the tables and then upload, which is extremely difficult for a dataset containg many tables with incremental. It is extremely inconvenient.

Is there any tools exist out there/ some XMLA script on SSSMS that lets me do the above jobs on the PROD directly without going through the hassle everytime I have those reqs.

[bcdobbs · Accepted solution]

I tend to just connect direct to the dev model via xlma end point with tabular editor and add new tables there/configure new incremental when required. It's relatively straight forward in TE3 where you can right click to update table meta data once you've set it up. One done I use deployment pipeline to step through test and prod but you could connect straight to prod.

Would happily jump on a call and demo if you needed.

[bcdobbs · Accepted solution]

I tend to just connect direct to the dev model via xlma end point with tabular editor and add new tables there/configure new incremental when required. It's relatively straight forward in TE3 where you can right click to update table meta data once you've set it up. One done I use deployment pipeline to step through test and prod but you could connect straight to prod.

Would happily jump on a call and demo if you needed.

[smpa01]

@TomMartens  @AlexisOlson   I am on Premium capacity, so it should not be an issue. I will give ALM toolkit and XMLA a spin and report back my experience on each

[AlexisOlson]

I believe this is possible but I haven't attempted it myself yet. XMLA connection is the key.

Here are some links that may be useful:

https://docs.tabulareditor.com/te3/powerbi-xmla.html

https://www.sqlservercentral.com/blogs/power-bi-adding-columns-to-a-published-data-model-using-the-x...

https://www.elegantbi.com/post/xmlamisconceptions

https://data-goblins.com/power-bi/version-control-pt3

[smpa01]

Create or Replace with XMLA is resulting in this while trying to download from the server a workbook with no incremental but with "createOrReplace" exercised with XMLA through SSMS. I prefer doing it via SSMS but is this a definite side effect or it can be bypassed?

@d_gosbell @GilbertQ 

[AlexisOlson]

This is a documented limitation:
https://learn.microsoft.com/en-us/power-bi/create-reports/service-export-to-pbix

• Reports created in the Power BI service that are based on a single dataset that's configured for large models or incremental refresh, or that has been modified by using the XMLA endpoint can only be downloaded in live connected report mode.
• Datasets modified by using the XMLA endpoint can't be downloaded to a .pbix file.

There are ways to reconstruct a .pbix but they're hacky:
https://data-goblins.com/power-bi/download-pbix

[TomMartens]

Hey @smpa01 ,

if you are on Premium Per Capacity or Premium Per User you can use ALM Toolkit: Home Page - ALM Toolkit (alm-toolkit.com).

You can use ALM Toolkit to connect to the Power BI Desktop file and "synch" the changes to the dataset residing in Power BI Service without overwriting existing partitions 🙂

Regards,

Tom

[smpa01]

@TomMartens  one follow-up Q to this. Does ALM toolkit allow to delpoy selective changes to the server?

Meaning, I have delpyed a partitioned dataset (PROD) on the server and my dev dataset (local) is not partitioned. I work on my dev copy to update/create new meaures/tables. Is it possible to select a handful of changes (new tables/ new measures/ edited measures) through ALM toolkit and deploy them on the partioned dataset on the server. I have not used ALM toolkit before, but I think it can be extremely useful for my case. Please forgive, if it sounds like a stupid question but I want to be extremely cautious before jumping on to something without understanding the implication first that might cost me the PROD.

@bcdobbs I don't have TE3 yet

[TomMartens · Accepted solution]

Hey @smpa01 ,

you can do, this is exactly what ALM Toolkit was build for.

Of course you have to practice, a little.

Regards,

Tom