visual-studiopowerbisql-server-2019ssas-tabularpower-bi-report-server

Why can't I save a new measurement created in my tabular model in Visual Studio 2019?


I am currently working with a BI environment that was developed by a service provider for my company. The infrastructure uses SQL Server 2019 (developer edition) and employs a BI architecture that involves creating a DWH database using the Extract Load Transform (ELT) process from SSIS packages. This database feeds into my tabular model on Visual Studio 2019. The Workspace Server (named localhost) is a workspace instance that contains the datamart and was also created on the same computer. Reports are built using Power BI connected to the server localhot, which the company accesses via Power BI services. The reports are automatically updated every day.

AchitectureBI

Recently, I needed to make some changes to the report, including creating a new Flow W% measure that divides Flow W and Flow W-1, both of which were already created on the cube. I created the measure in the tabular model project and successfully deployed it. However, when I tried to save my changes in the project directory, I received an error message, even though I had selected the same project and asked to replace it.Moreover i was not able to retrieve my measure on PowerBI services.

the measurecreated measuredisappear Access Denied for saving

I need some help regarding my tabular model. I made some modifications to it but none of them are getting saved. As a workaround, I created a new template to save the changes. However, when I open the template, it appears to be empty.

I tried creating a measurement on PowerBI Desktop but it seems to be outdated compared to the version available on PowerBI services. There are too many changes to make and i was not able to know all of them.

Can you please guide me on how to modify the cube so that I can retrieve the measure directly from PowerBI services?


Solution

  • The service provider to your company has done a lot of questionable things that need to be corrected. I'm assuming you are new to these tools, so I apologize if I'm telling you anything you already know.

    First off, you mention Developer Edition. "It is a free edition of SQL Server for developers and/or testers. This SQL Server edition is used for non-production environments".1 If you are publishing reports built off this data, you need a paid licensed version. IT was OK to do a proof of concept using Developer Edition, but it needs to be moved to production.

    Second, when you use this for a production environment, SQL Sevrer should be installed on a server, not on your computer. Visual Studio, SSMS, and Power BI should be accessing the server and not localhost. To do your development, you should be working from your computer and connect to the server. There is no need to remotely connect to a server to do your development.

    When you set up the project in Visual Studio, you have the option of setting up the Development Workspace on a server (typically the same server where you will deploy your model) or on your workstation (the "Integrated workspace" option). I prefer to always use the server. More on that shortly.

    enter image description here

    You can check which you are using by clicking on Model.bim in the Solution Explorer pane, then scroll down and check the settings shown below. Workspace Server is the server name for temporary model created while you are making changes. Workspace Database is the database name for this temporary model, and is in the format of "ProdDBname_userid_GUID". The default sets the Workspace Retention to "Unload from memory". This is helpful because 1) it doesn't take memory away from your production models, and 2) you can view the server from SSMS and see who is working on a model.

    enter image description here

    So in your screenshot, I see a production model, DM_NAPS, and 2 development instances, one for user "pbi" and one for "pbiv2". Normally I would expect this to be your username and not an application account like these, which leads me to believe this is the cause of your error message.

    enter image description here

    In English, I read the error as "Access to the access path 'D:\Data…' is denied." (Please always paste in error messages as text and not as images, especially in another language! This is probably why no one has responded to your post yet.) This is just a permissions error to the D drive for some account. My guess is you are somehow using your user account and an application account, and one of the 2 accounts does not have access to this drive. I don't develop this way, so I don't know how you have this set up, but that's what you should look into.

    In my experience, only one person should be editing a model at a time, or user pbi will overwrite user pbiv2's changes when they deploy. The fact that there are 2 dev versions out there at the same time is something that normally shouldn't happen. I am the only developer of my models, so I'm not the best person to give advice here, but you need to be aware of who all is working on this model at the same. Maybe the access error is because someone else has the project open.

    The Deployment looks like it was successful (again, please paste messages as text, especially if not in English). You should check your settings to confirm where it was deployed, and make sure Power BI is pointing at that same server. Go to Project > Properties and check the setting for Server. Then check that Power BI is using this same server and your measures should be there, though depending on how your connected from Power BI, you may need to do a refresh.

    enter image description here

    You can also check your measures from SSMS by right clicking the database and selecting New Query > MDX and then using the command select * from $SYSTEM.TMSCHEMA_MEASURES WHERE [Name] = 'your_measure_name'; (Note that LIKE is not supported here.)

    enter image description here

    I hope something in there helped and sorry if I'm over-explaining anything!