Combine ASA realtime data with historic data that are incrementally refreshed

[Micado]

Hi,

I have two things I'd like to achieve:

- get the most recent data from Stream Analytics to be as up-to-date as possible

- provide the historical data (we store them in a Cosmos DB).

The connection to the Stream Analytics in realtime carries the disadvantage that it is limited to 200.000 rows, data will be dropped FIFO.

To overcome this I decided to load older data from the Cosmos DB into the model. Because these are continously increasing measurement data without changes in older data, I assumed incremental refresh would be the most efficient solution.

Therefore I created a dashboard in Power BI Desktop and implemented the incremental refresh.

Additionally I configured a PowerBI Output in my stream analytics jobs, so the data are pushed into a dataset in the Power BI service. 

I can add this dataset via the Power BI Datasets Button and use those data as an additional data source. This works fine (iirc I had to activate an option in the previews setting in the settings menu).

Looks fine so far, pretty much what I wanted, the live data refresh continously. But if i publish this into the Power BI service all visualizations using these live data are labeled as unavailable. What am I missing? Did I miss to set some access rights?

kind regards!

[v-kelly-msft]

Hi @Micado ,

Check whether there are some limitations in your visuals:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about

Besides have you configured the data source in Service?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Micado]

Hi, 

thx for your response. As stated, it is a dataset, configured in Azure Stream Analytics and directly pushed into a workspace into PBI service.

Opening it in Power BI Desktop (from the option "Power BI-Datasets") works, I can create visualizations and they show the data in Power BI Desktop.

But publishing it (into the same workspace the datasets is located) breaks the visualisation with the note "Cannot load data".

That's unexpected, to say the least 😄 

[v-kelly-msft]

Hi  @Micado ,

Have you checked the limitations and considerations below?

Limitations and considerations

When using the Power BI service live connection, there are a few limitations and considerations to keep in mind.

• Only users with Build permission for a dataset can connect to a published dataset using the Power BI service live connection.
• Free users only see datasets in their My Workspace and in Premium-based workspaces.
• Since this connection is a live connection, left-navigation and modeling are disabled. You can only connect to one dataset in each report. This behavior is similar to the behavior when connected to SQL Server Analysis Services. However, the following article describes how a new feature in Power BI now makes this possible: see using DirectQuery for Power BI datasets and Azure Analysis Services (preview)
• Since this connection is a live connection, row-level security (RLS) and other such connection behaviors are enforced. This is the same as when connected to SQL Server Analysis Services.
• If the owner modifies the original shared .pbix file, the dataset and report that is shared in the Power BI service is overwritten. Reports based on that dataset aren't overwritten, but any changes to the dataset are reflected in the report.
• Members of a workspace can't replace the originally shared report. Attempts to do so result in a warning that prompts you to rename the file, and publish.
• If you delete the shared dataset in the Power BI service, then other reports based on that dataset will no longer work properly or display their visuals.
• For Content Packs, you must first create a copy of a content pack before using it as a basis for sharing a .pbix report and dataset to the Power BI service.
• For Content Packs from My Organization, once copied, you can't replace the report created on the service or a report created as part of copying a Content Pack with a live connection. Attempts to do so result in a warning that prompts you to rename the file, and publish. In this situation, you can only replace published live connected reports.
• Deleting a shared dataset in the Power BI service means no one can access that dataset any longer from Power BI Desktop.
• Reports that share a dataset on the Power BI service don't support automated deployments using the Power BI REST API.

Check the reference below:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-report-lifecycle-datasets

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Micado]

Thx, let's get through the limitations, but should all be fine.

• Only users with Build permission for a dataset can connect to a published dataset using the Power BI service live connection.
  • As I've created the dataset, I'm the admin, should be okay.
• Free users only see datasets in their My Workspace and in Premium-based workspaces.
  • I have a pro license, should be fine.
• Since this connection is a live connection, left-navigation and modeling are disabled. You can only connect to one dataset in each report. This behavior is similar to the behavior when connected to SQL Server Analysis Services. However, the following article describes how a new feature in Power BI now makes this possible: see using DirectQuery for Power BI datasets and Azure Analysis Services (preview)
  • I've used exactly this feature to include two dataset in the report,  should be fine
• Since this connection is a live connection, row-level security (RLS) and other such connection behaviors are enforced. This is the same as when connected to SQL Server Analysis Services.
  • We haven't employed any RLS rules yet, so this should be fine.
• If the owner modifies the original shared .pbix file, the dataset and report that is shared in the Power BI service is overwritten. Reports based on that dataset aren't overwritten, but any changes to the dataset are reflected in the report.
  • I do work on the dashboard in Power BI, but this should not harm the visualisation.
• Members of a workspace can't replace the originally shared report. Attempts to do so result in a warning that prompts you to rename the file, and publish.
  • not applicable, I own the workspace
• If you delete the shared dataset in the Power BI service, then other reports based on that dataset will no longer work properly or display their visuals.
  • Obviously 😄 But the dataset is still there
• For Content Packs, you must first create a copy of a content pack before using it as a basis for sharing a .pbix report and dataset to the Power BI service.
  • What is a Content Pack? But I guess, this topic is not applicable
• For Content Packs from My Organization, once copied, you can't replace the report created on the service or a report created as part of copying a Content Pack with a live connection. Attempts to do so result in a warning that prompts you to rename the file, and publish. In this situation, you can only replace published live connected reports.
  • Probably not applicable, see above
• Deleting a shared dataset in the Power BI service means no one can access that dataset any longer from Power BI Desktop.
  • Caption Obvious 😄
• Reports that share a dataset on the Power BI service don't support automated deployments using the Power BI REST API.
  • Didn't use PBI REST API

[v-kelly-msft]

Hi @Micado ,

Then let me ask a question may be a little foolish,have you checked the account in Service and desktop?Whether they are the same?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Micado]

Hi, that ain't a foolish question, it's just ruling out avoidable problems - and I didn't check it because a relied on the fact, that my company is really ruling the SSO game - and indeed they are: Both accounts I'm signed in with are the same.

[v-kelly-msft]

Hi  @Micado ,

If so,I would suggest you create a support ticket via below link and our relevant personnel will help to handle it.
https://powerbi.microsoft.com/en-us/support/

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Micado · Accepted solution]

Well, to finalize the topic here:

I contacted MS Support and we met via video call. When I wanted to show the problem, it was magically solved. I do have NO idea why it suddenly worked. Only thing that changed is Google Chrome, which updated itself after my last check-in that it didn't work and the video call with the support team.