Share your thoughts on DirectQuery for Power BI datasets and Azure Analysis Services (preview)

[tessahurr]

Hit Reply and let us know what you think of the DirectQuery for Power BI datasets and Azure Analysis Services.  To learn more about this feature, please visit this blog post or our documentation.

Here are some areas that we'd like to hear about in particular:

• Performance
• Query editor experience--the remote model query doesn't show up in the query editor and only in the data source settings dialog. What are your thoughts?
• Navigator experience
• Thoughts around governance and permissions for models that leverage this feature
• Nesting models, i.e. building a composite model on top of a composite model
• Automatic page refresh for live connect in composite models

Thanks and we look forward to hearing your feedback!

- The Power BI Modeling Team

[samaguire]

So, this one is an oddball one.

When I query a particular DQ dataset form Excel I get the error "Data seen at different points in time during execution of this query are inconsistent with each other. This could be due to external transactions updating same data while this query was running. Please rerun this query." However, the exact same setup querying the underlying dataset (the source for the DQ dataset, which is a typical import model) doesn't produce this error. 🤔

Tracing using Dax Studio, I find the underlying MDX query (which works when directly queried against the source dataset) is:

MDX Query:

SELECT 
    NON EMPTY 
        Hierarchize
        (
            AddCalculatedMembers
            (
                {DrillDownLevel({[Value Selector].[Value Selector].[All]})}
            )
        )
    DIMENSION PROPERTIES 
        PARENT_UNIQUE_NAME,
        HIERARCHY_UNIQUE_NAME
     ON COLUMNS
FROM [Model]
WHERE 
    [Measures].[Total Actuals NZD]
CELL PROPERTIES 
    VALUE,
    FORMAT_STRING,
    LANGUAGE,
    BACK_COLOR,
    FORE_COLOR,
    FONT_FLAGS;

Resultant DAX generated to the source dataset is (and when run in DAX Studio is working):

DAX Query 1:

EVALUATE
GROUPCROSSAPPLY(
    "__Agg0", [Total Actuals NZD]
)

DAX Query 2:

DEFINE
VAR _Var0 = VALUES('Value Selector'[Value Selector])
EVALUATE
GROUPCROSSAPPLY(
    'Value Selector'[Value Selector],
    ALLSELECTEDREMOVE(_Var0),
    "__Agg0", [Total Actuals NZD]
)

DAX Query 3:

EVALUATE
SUMMARIZE(
    VALUES('Value Selector'),
    'Value Selector'[Value Selector],
    'Value Selector'[Order]
)

The model structure is simple enough - although not the best modelling setup. The measure used is a simple column sum...

Not sure where to go from here? 🤷‍

[jeroenterheerdt]

hmm, can you please open an issue on issues.powerbi.com or open a support ticket?

[samaguire]

In case you want to track your end: Support request ID 2205310030002149

[hemd1]

When you link with second dataset, the RLS would disappear. It would have data loss risk. Would there be any plan to fix that?

[jeroenterheerdt]

I am not sure I follow this - can you please provide more details?

[Ismaelhb1986]

I am referring to the fact that currently with PRO licensing it is necessary to release construction access to the sets that I use in my panels that use the resource

[gerardcanals]

Hi,

I have an error in all the reports with this functionality. All the visuals of the reports are broken:

The error is:

I have admin permissions to all datasets. The reports are connected to two datasets one in the same workspace of the report and another to other workspace. In both workspaces I am admin.

Do you now what is happening? Should I contact the support?

[soldous]

We have the same issue in our tenant. In PBI Desktop all works fine but in the Service there is the same issue. I'm the admin in both workspaces as well. So far haven't found any solution. 

[gerardcanals]

In my case in power PI desktop doesn't works too.

[jeroenterheerdt]

please open a support ticket so we can dive into this.

[AlejandroErazo]

Sorry, but where can I open this ticket?

[jeroenterheerdt]

either your support channels for the company you work for or issues.powerbi.com

[Anonymous]

I have had a rather critical OLE DB or ODBC error, which i have not been able to solve. When ever i try to open a KPI report i created i get the following error. 

This applies to all former version and backups and prevents me from opening any file and changing  the connection. It have tried to deleting the tables in datasets used, changing them to blanks, etc. but i all these cases i just receive the error below or the one above. 

Thus i have been unable to acces the reports and change the connection since the direct query loads before the reports is opened. Meaning all the work building the report has been lost. Would it be possible to open the reports in some kind of safe mode or prevent PBI from loading the dataset before opening the report, such that i could remove the faulty connection.

[mamsteroonie]

Did you turn off the auto date/time feature in options and settings?  In the source dataset?  That localDateTable is from the auto date/time feature in options.

Did you try bringing it in again as a new source to see if it works?

[Anonymous]

Yes - tried to disable it both globally and in the source dataset. Unfortunately that did not work either. 

Also tried to delete thet relevant table in the source database and create a simple one only with a blank column or a column with dates. 

Ufortunately the error occurs when i try to open the file in the desktop. and prevents it from opening. So i cannot change the source inside the report. 

[MoeData]

The end users that dont have Pro license cannot view the dashboards that uses this feature, right?

[jeroenterheerdt]

I assume with "don't have Pro license" you mean Free users - they will be able to see the reports created using this feature if: a) they have at minimum Read permission on all datasets involved and b) the datasets involved are published on a Premium capacity.

[samaguire]

Looks like DQ is importing calculated tables (not normal tables) flagged as private. This is irrespective of the "Include tables added later" checkbox. I noticed this after adding some clustering via the standard Scatter Chart visual to one of the datasets.

I imagine this would be an issue for those who use private tables to track things - like the dataset template version that I've just started tracking using a private table 😞

The below images show the tables selected for import (not the private tables are not listed), and the ClusterMappingTable tables that ended up in the model.

[jeroenterheerdt]

thanks for reporting this - I will look into this!

[jeroenterheerdt]

@samaguire it took me a while to get back to you as we are unable to reproduce this problem. Are you still seeing this issue?

[samaguire]

@jeroenterheerdt 

Sorry for the delay in getting back to you. Yes, this is still a problem.

I created an example for you. SourceModel.bim which is used as the source model for the Desktop DQ for AS connection as shown in the below snips.

I also now note that it looks like it only happens when a table is both flagged as private and hidden.

SourceModel.bim as Json:

Spoiler

{
  "name": "SourceModel",
  "compatibilityLevel": 1550,
  "model": {
    "culture": "en-NZ",
    "dataAccessOptions": {
      "legacyRedirects": true,
      "returnErrorValuesAsNull": true
    },
    "defaultPowerBIDataSourceVersion": "powerBI_V3",
    "sourceQueryCulture": "en-NZ",
    "tables": [
      {
        "name": "PqTable",
        "lineageTag": "7001ad32-dbd3-4728-a2da-095a886fb709",
        "columns": [
          {
            "name": "Column1",
            "dataType": "string",
            "sourceColumn": "Column1",
            "lineageTag": "aab2170a-ae38-4eec-8765-32a2cdab3d7c",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "PqTable-2446d46a-8837-4b4f-b324-f8cb57d49656",
            "mode": "import",
            "source": {
              "type": "m",
              "expression": [
                "let",
                "    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(\"i45W8ssvycjMSw/JD05N9UgtSlWKjQUA\", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),",
                "    #\"Changed Type\" = Table.TransformColumnTypes(Source,{{\"Column1\", type text}})",
                "in",
                "    #\"Changed Type\""
              ]
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_ResultType",
            "value": "Table"
          }
        ]
      },
      {
        "name": "PqTablePrivate",
        "isPrivate": true,
        "lineageTag": "a3264cb3-17b2-45e0-9628-8f916380f694",
        "columns": [
          {
            "name": "Column1",
            "dataType": "string",
            "sourceColumn": "Column1",
            "lineageTag": "3ff1f0ad-f03b-4861-8fd5-674c27991c66",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "PqTablePrivate-d62a028b-42cd-4a3a-b97d-ce883e99a833",
            "mode": "import",
            "source": {
              "type": "m",
              "expression": [
                "let",
                "    Source = PqTable",
                "in",
                "    Source"
              ]
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_NavigationStepName",
            "value": "Navigation"
          },
          {
            "name": "PBI_ResultType",
            "value": "Table"
          }
        ]
      },
      {
        "name": "PqTablePrivateHidden",
        "isHidden": true,
        "isPrivate": true,
        "lineageTag": "83b80701-e6f4-4c64-8ca1-0fb0c5399c83",
        "columns": [
          {
            "name": "Column1",
            "dataType": "string",
            "sourceColumn": "Column1",
            "lineageTag": "1f665f61-f972-4ac0-ba1c-492b6c98f170",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "PqTablePrivateHidden-64c17b17-de9b-402d-8d25-dc37c9285f48",
            "mode": "import",
            "source": {
              "type": "m",
              "expression": [
                "let",
                "    Source = PqTable",
                "in",
                "    Source"
              ]
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_NavigationStepName",
            "value": "Navigation"
          },
          {
            "name": "PBI_ResultType",
            "value": "Table"
          }
        ]
      },
      {
        "name": "DaxTable",
        "lineageTag": "3e2f348a-a902-4608-b1c7-aa71d407dea0",
        "columns": [
          {
            "type": "calculatedTableColumn",
            "name": "Column1",
            "dataType": "string",
            "isNameInferred": true,
            "isDataTypeInferred": true,
            "sourceColumn": "PqTable[Column1]",
            "lineageTag": "5d04fbcc-0316-42bd-968b-3ba3ef20230e",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "DaxTable-e9cdb89e-ccdf-4558-8b65-0d9b4fa93ee9",
            "mode": "import",
            "source": {
              "type": "calculated",
              "expression": "PqTable"
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_Id",
            "value": "4c2f8b71a4a54f56a62b1456a4812712"
          }
        ]
      },
      {
        "name": "DaxTablePrivate",
        "isPrivate": true,
        "lineageTag": "f3f4992d-0ea8-4f7c-a167-c70760c2f2a5",
        "columns": [
          {
            "type": "calculatedTableColumn",
            "name": "Column1",
            "dataType": "string",
            "isNameInferred": true,
            "isDataTypeInferred": true,
            "sourceColumn": "PqTable[Column1]",
            "lineageTag": "c9b74999-e76b-4621-866d-1a89a62e9c89",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "DaxTablePrivate-173e0da7-40bd-4f77-89bd-9a9430bc3733",
            "mode": "import",
            "source": {
              "type": "calculated",
              "expression": "PqTable"
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_Id",
            "value": "ec0e344532bd46439b6b3cc56d73dd44"
          }
        ]
      },
      {
        "name": "DaxTablePrivateHidden",
        "isHidden": true,
        "isPrivate": true,
        "lineageTag": "9c4a8457-dd9e-4d1e-a89b-2d8d79abf52c",
        "columns": [
          {
            "type": "calculatedTableColumn",
            "name": "Column1",
            "dataType": "string",
            "isNameInferred": true,
            "isDataTypeInferred": true,
            "sourceColumn": "PqTable[Column1]",
            "lineageTag": "b857de53-def6-4096-b0ca-9a44eddfd1fa",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "DaxTablePrivateHidden-21fee87e-f8cd-474c-a789-ff9fc8b40e24",
            "mode": "import",
            "source": {
              "type": "calculated",
              "expression": "PqTable"
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_Id",
            "value": "9ee117f94d894652abee8a5df6f00d77"
          }
        ]
      },
      {
        "name": "PqTableHidden",
        "isHidden": true,
        "lineageTag": "79520b06-87e3-4980-9164-47f2ba6229a8",
        "columns": [
          {
            "name": "Column1",
            "dataType": "string",
            "isHidden": true,
            "sourceColumn": "Column1",
            "lineageTag": "ae0dcab9-8d91-4fdd-98d5-25ee3189b1e5",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              },
              {
                "name": "PBI_ChangedProperties",
                "value": "[\"IsHidden\"]"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "PqTableHidden-e11b05cc-a916-4282-99dc-18cfd20b5c0f",
            "mode": "import",
            "source": {
              "type": "m",
              "expression": [
                "let",
                "    Source = PqTable",
                "in",
                "    Source"
              ]
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_NavigationStepName",
            "value": "Navigation"
          },
          {
            "name": "PBI_ResultType",
            "value": "Table"
          },
          {
            "name": "PBI_ChangedProperties",
            "value": "[\"IsHidden\"]"
          }
        ]
      },
      {
        "name": "DaxTableHidden",
        "isHidden": true,
        "lineageTag": "835545c4-882b-4d4b-b421-95093a38c93e",
        "columns": [
          {
            "type": "calculatedTableColumn",
            "name": "Column1",
            "dataType": "string",
            "isNameInferred": true,
            "isDataTypeInferred": true,
            "isHidden": true,
            "sourceColumn": "PqTable[Column1]",
            "lineageTag": "aa414bfb-83d6-4750-9969-c4754407bd44",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              },
              {
                "name": "PBI_ChangedProperties",
                "value": "[\"IsHidden\"]"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "DaxTableHidden-b1232328-42b2-40e6-8c2b-5012d927c408",
            "mode": "import",
            "source": {
              "type": "calculated",
              "expression": "PqTable"
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_Id",
            "value": "351ba69ae8f6473da85ce525ba0f002c"
          },
          {
            "name": "PBI_ChangedProperties",
            "value": "[\"IsHidden\"]"
          }
        ]
      }
    ],
    "cultures": [
      {
        "name": "en-NZ",
        "linguisticMetadata": {
          "content": {
            "Version": "1.0.0",
            "Language": "en-US",
            "DynamicImprovement": "HighConfidence"
          },
          "contentType": "json"
        }
      }
    ],
    "annotations": [
      {
        "name": "__PBI_TimeIntelligenceEnabled",
        "value": "0"
      },
      {
        "name": "PBIDesktopVersion",
        "value": "2.107.841.0 (22.07)"
      },
      {
        "name": "PBI_QueryOrder",
        "value": "[\"PqTable\",\"PqTablePrivate\",\"PqTablePrivateHidden\",\"PqTableHidden\"]"
      },
      {
        "name": "TabularEditor_SerializeOptions",
        "value": "{\"IgnoreInferredObjects\":true,\"IgnoreInferredProperties\":true,\"IgnoreTimestamps\":true,\"SplitMultilineStrings\":true,\"LocalRelationships\":false,\"LocalPerspectives\":false,\"LocalTranslations\":false,\"PrefixFilenames\":false,\"Levels\":[\"Data Sources\",\"Shared Expressions\",\"Perspectives\",\"Relationships\",\"Roles\",\"Tables\",\"Tables/Columns\",\"Tables/Hierarchies\",\"Tables/Measures\",\"Tables/Partitions\",\"Tables/Calculation Items\",\"Translations\"]}"
      }
    ]
  }
}

Tables selected:

Tables loaded: