How to edit Workspace Dataset created in Visual Studio?

[gmelhaff]

How can you change a dataset once it's published?  Especially if it originated from Visual Studio SSAS model?

If I use the dataset as a source inside of PBIX then I get greyed out Transform data option and that's because its opened in direct query mode...so the question is, how to open so you can actually make changes to the dataset model?

This seems SO fundamental to converting SSAS models to datasets and yet I've found NO answer to this question.  And of course dataset schema management is a fundamental requirement otherwise publishing datasets is a waste of time is it not?

But unless you create the dataset using pbi AND never lose that file (or stand on your head while reciting Vogon poetry) it can be difficult if not impossible to ever edit it again even IF you originally created it using powerbi desktop.  Why is that?

Because you are expected to never lose the original pbix file which is the only one ring that rules them all.  The other option is to "download to pbix" from the dataset but that's not as easy as it sounds.

So I digress...again what am I trying to do?

I need to change a dataset that was the result of an SSAS model.  That's it.  I'm trying to migrate all our SSAS models to powerbi service workspace datasets.  Microsoft themselves actually recommended we do this.  Great, but how?   They didn't provide that bit.

According to one blogger this actually isn't possible.  In fact he said if you ever open the dataset in Visual Studio and re-deploy it you can never edit it again in PBIX - its a one way street he said. That just doesn't make sense to me as this is all just SSAS under the covers. So why not stick with Visual Studio?  Because it doesn't support pretty much everything that PBIX does like incremental refresh and a multitude of sources and functionality.

One thing to note is you need a premium subscription and a premium workspace.  But when I attempt to use the dataset as a source from PowerBI desktop this brings up the fundamental problem - it opens it in direct query mode.  You can't edit a source that's opened in direct query mode I get that.  So then how DO you open a dataset without being in direct query mode?   Like I said you can open it with the original pbix file or download it from the dataset.  But downloading from the dataset doesn't work for datsets deployed from Visual Studio!

I can generate the TMSL to create the model so....why can't I just create a pbix model using the TMSL?  If you know how to do that please oh please tell me how!

FYI.....how do you edit the dataset in a workspace if you lose the original PBIX file?

1) Open it with the original PBIX file. What if you lose that? 

or

2) "Download the pbix file" option from the workspace dataset

Problem with #2 is that doesn't work unless you remove incremental refresh AND turn off large file size for the dataset.  Why large file size?  What does that have to do with any of this?  If the download fails for ANY reason, you get a super awesome message below...

Yes you can also open it from tabular editor but lets ignore that for now as that can have its own issues (for me that just resulted in an error message when I tried the "edit schema" in tabular editor....looks like maybe it doesn't support gateway connections?  Just a guess.)

So how do you convert SSAS models to datasets and then edit those datasets?  A virtual hug for anyone who can solve this for me 😉

[GilbertQ · Accepted solution]

Hi @gmelhaff 

What you can do is a combination of the following:

• Open Visual Studio and go into each table, then right click and go into table properties.
• At the same time have your PBIX open and go into Power Query Editor
• Now right click on the table in Visual Studio and select Copy
  • Go into PQ in your PBIX and right click and select Paste.
  • This will paste the table there.
  • Do this for all your tables.
• Now to copy the measures open Tabular Editor connected to your Visual Studio Project.
• Open Tabular editor connected to your PBIX
  • Then paste all the measures into the same table.
• All that is now left to do is to create the relationships.
• I typically keep my PBIX file seperate from my PBIX with the visuals. So that changes in the reporting layer area quick and easy to deploy.

I hope that helps.

[gmelhaff]

So its available here in case anyone is looking for solution....What was proposed above in the end did not work.  I have resorted to manually re-creating table imports in PBIX that were used in SSAS and copy/paste DAX code from Visual Studio into PBIX.  That's really not that difficult as I had assumed.  Just tedious is all for large model.  I would not advise attempting to do some kind of conversion - its a time suck and you'll likely even if somehow succesful end up with model that doesn't work for power bi service incremental refresh based on timestamps which is one of the advantages of using datasets on PowerBI service over SSAS server.  

Learnings when attempting above...

PQ doesn't appear in VS 2019 so that's not a thing at least in the latest version.

Copying tables with tabular editor from SSAS to PBIX corrupts the pbix model. 

Attempting some kind of tool-based conversion from SSAS to PBIX is a waste of time. 

And even IF you were succesful in somehow copying a table that results from a SQL query it would seem to be an invalid method for incremental refresh purposes since parameterized incremental refreshes in Power BI service is very picky and requires full table import so it can add it's DAX parameters on the end of the table query.  Read below article that talks about this limitation...

Chris Webb's BI Blog: Query Folding And Writing Your Own SQL Queries In Power Query/Power BI/Excel G...

[gmelhaff]

HI  @GilbertQ   Sent private message....not following originally but finally realize you were talking about opening 2 instances of tabular editor - one from within external tools in PBIX and the other for connecting to the SSAS model. Then copy paste within tabular editor works.  If you try to startup tabular editor when connected to SSAS or PBI dataset it just gives an error message but when you do it from brand new pbix its happy.  There was no copy from visual studio possible which threw me.   Would have appreciated more clarity what you were talking about would have saved me couple hours work trying to figure this out but in the end its working well.  Thank you very much. 

[gmelhaff]

Roadblock is this...I'm having same issue reported here...Unable to save files as Enhanced Dataset Metadata ... - Microsoft Power BI Community. 

I have latest version of Power BI desktop and yet it isn't saving as V3 metadata and tabular editor is complaining and behaving badly possibly as a result of this.  The bugs with powerbi seem endless 😞

Attempting to either refresh or upgrade simply results in discarding any changes made in tabular editor.