DirectQuery for power bi datasets and AS error: Query can only go through 2 levels

[Anonymous]

Hi Everyone,

I have an urgent problem that caused my dashboard to stop working and possibly broke my dataset that had incremental refresh applied and was collectiing data for many months... I had 2 seperate reports connected to 2 seperate datasets that was published to the workspace. Datasets are connected with SQL db and runs scheduled refresh with incremental refresh overnight. Today I needed to take one of the data table from first dataest and link it to second dataset. I found that solution for that is to enable DirectQuery for power bi datasets and AS. Indeed that allowed me to add another datasource to the report and after that I was able to see in Model pane that datatable from another dataset has been added. Unfortunately now my report is not working anymore and Power BI (both desktop and web version) is not able to load metrics that are connected to the old dataset (error that PowerBI desktop is showing: Query can only go through 2 levels). Is there any way to repair that or at least restore previous data model for my dataset from Analysis Services back to SQL? I really need to keep this data alive...

Thank you in advance,

Mateusz Bartczak

[TiagoV46]

Hi,

Did you find any solutions for this?

I have the same problem now!
It never appear like that to me, it's my first time with this problem!

[Anonymous]

Hi @Anonymous ,

Thank you for response.

Unfortunately enabling option that you mentioned didn't help. By report not working I mean it cannot be loaded at all:

Card saying '98' is coming from dataset that I linked to original dataset of the dashboard. For some reason, it could be used, but old dataset cannot be loaded anymore.

If it comes to data gateway, it was always working fine since configuration long time ago, and I didn't change anything in regards to that since then.

After clicking at 'See details', following information appear:

Feedback Type:
Frown (Error)

Timestamp:
2022-11-10T17:44:41.9331024Z

Local Time:
2022-11-10T18:44:41.9331024+01:00

Session ID:
1cf61b8b-358b-4a8b-a862-e515a458fe57

Release:
September 2022

Product Version:
2.109.1021.0 (22.09) (x64)

Error Message:
OLE DB or ODBC error: OLE DB or ODBC error: OLE DB or ODBC error: Query can only go through 2 levels of Analysis Services datasources.

Technical Details:
RootActivityId: ff41a172-14a6-46a4-8bd0-f81e61448b5b
Date (UTC): 11/10/2022 5:41:43 PM.

Technical Details:
RootActivityId: 6284b5d7-ef27-474c-83b8-c9acb22b1553
Date (UTC): 11/10/2022 5:41:43 PM.

Szczegóły techniczne:
RootActivityId: 9b39e372-cef1-4c3c-a3d2-3f6812de81ae
Date (UTC): 11/10/2022 5:41:43 PM.

OS Version:
Microsoft Windows NT 10.0.19042.0 (x64 en-GB)

CLR Version:
4.8 or later [Release Number = 528372]

Peak Virtual Memory:
69.9 GB

Private Memory:
540 MB

Peak Working Set:
824 MB

IE Version:
11.789.19041.0

User ID:
6bb5a260-7882-402b-9ddf-c3679533a7e5

Workbook Package Info:
1* - pl-PL, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: False.

Telemetry Enabled:
False

Snapshot Trace Logs:
C:\Users\yegs\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShote49f7df1-d417-4d42-ba74-960af9f726bb.zip

Model Default Mode:
Composite

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\yegs\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Enabled Preview Features:
PBI_compositeModelsOverAS
PBI_enableWebView2
PBI_sparklines

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_b2bExternalDatasetSharing
PBI_enhancedTooltips
PQ_WebView2Connector
PBI_scorecardVisual
PBI_fieldParametersSuperSwitch
PBI_horizontalFusion

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
125%

Supported Services:
Power BI

Formulas:


section Section1;

shared #"DirectQuery to AS - Licence Utilization" = let
Source = AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/myorg/Robotics", "Licence Utilization"),
Cubes = Table.Combine(Source[Data]),
Cube = Cubes{[Id="Model", Kind="Cube"]}[Data]
in
Cube;

shared #"DirectQuery to AS - Robotics Health Check" = let
Source = AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/myorg/Robotics", "Robotics Health Check"),
Cubes = Table.Combine(Source[Data]),
Cube = Cubes{[Id="Model", Kind="Cube"]}[Data]
in
Cube;

Thank you once again. Any help is priceless.

Best regards,

Mateusz Bartczak

[Anonymous]

Hi, 

Did you figure out a solution to this? I had exactly the same problem where it decided to stop working, and thus crashed my web and local PBI files, since none of the visuals are displaying and it has the same error that you mentioned. Any ways on how you fixed it? 

I am thinking of bringing in the data source as a SQL query instead of the Direct Query approach I used! 

[Anonymous]

Hi @Anonymous ,

You cannot revert the data model from Analysis Services to SQL due to inconsistent data sources.
I have a few questions about "report not working", is the report not opening or refreshing, what are the details of the error, and is the gateway configured correctly for the data source.
You can try turning this setting on in Desktop.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.