DirectQuery Dataset Not Showing Updated Records

[ahcspbi]

Reports on Dataset online is not showing updated records.

Steps taken:

1. I have a Dataset based on DirectQuery results published to a workspace online.

2. I have created a report on the PowerBI workspace online that lists all entries in a specific table in the dataset.

3. I have ordered the report by CreatedDate, descending.

4. I have created a new entry in the tables that the DirectQuery dataset references.

5. I've waited about 30 minutes after creation of the entry and navigated to the report.

Expected Result:

I should see the entry.

Actual Result:

I do not see the entry.

I've tried the following:

1. Clicking "Refresh Now" on the dataset in Power BI service.

2. Refreshing the report on the power BI service itself.

[v-lili6-msft]

hi, @ahcspbi 

Since you have tried the following:

1. Clicking "Refresh Now" on the dataset in Power BI service.

2. Refreshing the report on the power BI service itself.

If the refresh is successful? If there is some filter in this report, 

For example:

Slicer, visual/page/report level filter, or query Parameter in this report?

Try to use this dateset create a new report in power bi service to check if you could see the entry.

By the way, if "the entry" is a new column?

Best Regards,

Lin

[ahcspbi]

@v-lili6-msft 

The refresh reports as successful.

The report checking the dataset is structured as follows:

One Page.

Within this page, one table visual.

The visual reports Sale.Month, Sale.Day, Sale.Year, Sale.Total (none grouped).

The visual has a filter (SiteId = X)

Removing the filter and Adding it back yields the same result. Removing the filter & Refreshing the report yields the same result - a new entry (row within the table) is not reflected within the table on the dataset, regardless of how long I wait.

[garycarters]

I'm getting a similar problem to the OP - a report is not showing recent changes to my database.  I've tried refreshing dataset and report.

If I refresh the PBI on my desktop and republish, the numbers update to what I expect.

There are no filters/slicers on my data.

[ahcspbi]

@garycarters 

I'm experiencing the same thing - I do get updates to the dataset if I download PBIX, refresh the dataset and republish (as expected, I think?). However, refreshing the Dataset on the service does not show any new rows.

[v-lili6-msft]

hi, @ahcspbi 

It works well in my side.

for example:

before

Then I insert new data into sql db.

Now refresh the dataset and report.

Best Regards,

Lin

[ahcspbi]

@v-lili6-msft 

Still showing issues on our side.

Let me elaborate a bit on the dataset.

Dataset

• 3 Databases Involved
• 12 Tables pulled via DirectQuery (Customer-Facing Entities)
  • 10 Tables pulled from DB A, 1 from DB B, 1 from DB C.
• 2 Calculated Tables created via Power BI Queries (Date and Time Dimension Tables).
  
  • Date dimension table has 2 int parameters (Start Year & End Year) to calculate the time frame.

As stated in above reply, I am able to get new data to appear properly if I update the data in desktop and republish the dataset, but a refresh of the dataset and the report online continues to yield the problem.

[ahcspbi]

++ Further Illustration

The view the dataset is pulling from:

Latest Entry:

Id = 'B69F2CE6-1777-4AA9-D33A-08D6D87E4FA6', Date = '2019-05-14 15:10:33.923'

The dataset in power bi desktop:

BEFORE REFRESH Latest Entry:

Missing

AFTER REFRESH Latest Entry:

Id = 'B69F2CE6-1777-4AA9-D33A-08D6D87E4FA6', Date = '2019-05-14 15:10:33.923'

Newly Created report in power bi service:

BEFORE & AFTER REFRESH Latest Entry:

Missing

Refreshed Dataset and created report after the refresh was complete.

Screenshot shows the current output from cache refresh history below:

[v-lili6-msft]

hi, @ahcspbi 

I still couldn't reproduce the issue, so we couldn't submit this case to power bi product team,

For the strange issue, I suggest you create a support ticket in Power BI Support to get further help and share the reply with us. 

Best Regards,

Lin

[ahcspbi · Accepted solution]

@v-lili6-msft @garycarters 

No need - I figured out what the problem was.

When I was first creating these entities, I had forgotten to set to directquery (they were initially import).

Afterwards, I had attempted to convert them to DirectQuery by modifying the query in the advanced editor for each of the entities (instead of readding them from scratch). Did this primarily to preserve the relationships I had set up.

Turns out, I wound up with Import Entities that were set up identical to DirectQuery. The service even seems to have thought that my dataset was DirectQuery.

The tip off was looking at the model diagram with relationships. I selected a model, expanded the properties pane (to the left of the field pane), expanded the Advanced section and looked at the storage mode.

The storage modes were set to import. However, looking at the queries for the models in edit queries, I wound up seeing DirectQuery queries that were bound to those import models.

I'm not exactly sure why the powerbi service thought that these were directquery, but I'm assuming the service is looking at the queries for the models, but the runtime that actually updates looks at the models' storage modes and determines whether to execute a query maybe? to explain why it didn't refresh in the service but did on the desktop.

The Fix

For me was to recreate all of my models and relationships, this time as DirectQuery from the start. Regardless of what the query is set as, if you select import (or forget to select DirectQuery the first time you bring your models in), the models will stay as import. If you change the queries to look like DirectQuery models but they were brought in initially as import, the desktop application will work fine, but the service will view your dataset as a DirectQuery dataset, giving you the same options as a DirectQuery dataset, even though your models are actually import.