Data from SQL->Warehouse>Star Schema>Online PBI Service to PBI Desktop / Report

[allasion]

Hi, all. I am looking for options to edit data in PBI for a report, am not 100% knowledgeable and not a developer and would like to ask for your advice. 

This is the situation: our developer set up a data extraction path from SQL - Data Warehouse though Star Schema to PowerBI Online Services. The reason this has been done in this way is that the amount of data is huge so we need to be able to only extract data for a specific client / time period. 

In PBI online services the data shows as a report rather than dataset whych can be seen as tables... I am used to working with excel and access as data sources, can see the data, edit queries, add calculations and measures etc. But in this instance I cannot edit data or use it to create new tables/measures.... I can just summarise it and the capabilities are limited. 

So my questions: 

Is there a way around it for me to enable me to edit data, create tables, etc....? 

Should the data be prepared for PBI using a different method?

I can try and answer any questions you may have regarding the above if need be. Please let me know, 

Thank you

Alla

[v-yuezhe-msft · Accepted solution]

@allasion,

In Power BI Service, it is not possible to edit queries or create new tables/measures. You can use Power BI Desktop to connect to your data source(SQL data warehouse), then edit data, add calculations and measures there. 

Moreover, after you create report in Power BI Desktop, you can publish report to Power BI Service.

Regards,
Lydia

[allasion · Accepted solution]

Hi,

The data from SQL/DW was uploaded to PBI online services. I did the following:

1. I tried to upload this into the desktop services where I normally work on reports but, as mentioned, could not see or edit the data.

2. Downloaded from PBI online services the dataset as .pbix - the result was different - it opened as a PBI desktop and I could see tables and relationships.

3. Following step 2, I tried to edit the queries and it did not let me open it without SQL credentials. So I am still stuck.

As I am not a developer as such and do not know SQL I probably do not need to have SQL credentials and access to ALL the data.

All I need is to be able to work with the data already downloaded.

Are there any other options for me?

Thank you

Alla

[v-yuezhe-msft · Accepted solution]

@allasion,

In Power BI Service, it is not possible to edit queries or create new tables/measures. You can use Power BI Desktop to connect to your data source(SQL data warehouse), then edit data, add calculations and measures there. 

Moreover, after you create report in Power BI Desktop, you can publish report to Power BI Service.

Regards,
Lydia

[allasion]

Hi Lydia,

Thank you for your reply. This is what I was trying to find out as I was given access to the extracted data via PBI online services.

[v-yuezhe-msft]

@allasion,

Do you get the shared report in Power BI Service? And do you have access to the data source?

Regards,
Lydia

[allasion · Accepted solution]

Hi,

The data from SQL/DW was uploaded to PBI online services. I did the following:

1. I tried to upload this into the desktop services where I normally work on reports but, as mentioned, could not see or edit the data.

2. Downloaded from PBI online services the dataset as .pbix - the result was different - it opened as a PBI desktop and I could see tables and relationships.

3. Following step 2, I tried to edit the queries and it did not let me open it without SQL credentials. So I am still stuck.

As I am not a developer as such and do not know SQL I probably do not need to have SQL credentials and access to ALL the data.

All I need is to be able to work with the data already downloaded.

Are there any other options for me?

Thank you

Alla

[v-yuezhe-msft · Accepted solution]

@allasion,

Firstly, after using Power BI Service connector in Power BI Desktop to connect your dataset, you are only able to add new measures, create visuals. It is not able to edit queries in this case.

Secondly, when you download PBIX file from Service and open the file in Power BI Desktop to edit queries, you need to enter credential. Please ask for the owner of PBIX file to get vaild credential.

Regards,
Lydia

[allasion]

@v-yuezhe-msft

Hi Lydia, 

Just going back to this, so the option I had was to download the the PBIX file from Service which I did and am workng on the report just by adding columns, measures and vistual which is ok. However, while the raw data is set to refresh daily on the Service, it does not refresh on the Desktop- is this corect or is there an option to refresh the data from Services to my Desktop report on server? 

Seems like I am going round and round in circles as I do not have credentials to connec to SQL servier for security reasons. 

Is there a better way to download data from SQL to server rather than to Services that you can suggest? 

Thank you

Alla