cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tessahurr
Microsoft
Microsoft

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

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

476 REPLIES 476

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

MatDon
Frequent Visitor

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. 

 

MatDon_0-1651824164653.png

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. 

MatDon_1-1651824388319.png

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.

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?

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
Helper I
Helper I

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

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
Helper II
Helper II

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.

 

samaguire_1-1651552636635.png

 

samaguire_0-1651552553638.png

 

thanks for reporting this - I will look into this!

@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?

@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:

samaguire_0-1658920523554.png

 

Tables loaded:

samaguire_1-1658920534140.png

 

thank you, I will pass this along and get back to you!

Been a while since I checked. I'll confirm when I'm back in the office on Monday. I ended up manually modifying the json annotation that controls the schema import using TE3; to remove the tables I didn't want included. 😊 (And haven't made any subsequent changes since...)

philqc77
Frequent Visitor

Hi,

Since the latest PowerBi Desktop update (April 2022), I'm unable to connect to more than one live dataset.

I'm getting this error after selecting the database/tables (for the second dataset connection) :

philqc77_0-1650483484807.png

It's obviously not a timeout issue since I'm able to connect to the dataset if it's the first one that I'm connecting to.

Note that I can still open and refresh existing datasets containing multiple live dataset connections. Those were created in a previous version of Power BI Desktop.

 

Am I the only one getting this error?

Thanks

@philqc77 when you say "live connection" do you mean, an actual LIVE connection or a DQ connection? Because an actual LIVE connection is always just to one source. If you're talking about DQ connections, please give some more details on what source you're using.

Sorry, maybe I'm not using the right terminology. Here's what I did:

 

1. I created a live connection to a Power BI Dataset (let's call it ds1). No problem there.philqc77_7-1650546698114.png
2. Then I wanted to add another connection to another Power BI Dataset (ds2). I got this prompt which I was expecting and I clicked on "Add a local model".philqc77_0-1650545825057.png
3. Clicked "Submit" on the following. This is related to ds1.philqc77_2-1650546119514.png
4. Selected ds2 and clicked on "Create"philqc77_3-1650546265341.png
5. Clicked on "Submit". This is for ds2philqc77_4-1650546322846.png
6. Then I got the login prompt which is unusual at this point.  philqc77_5-1650546463990.png
7. Then I got the error.philqc77_6-1650546524352.png

 

I did the exact same steps (using the same connection) on Power BI Desktop updated to dec 2021 and it worked flawlessly.

Hi thanks for sharing these details with me. I can reproduce a similar issue on my end. Looking at it now.

This is a known issue at this point and has been fixed - we will release an update soon. It only affects datasets in Pro workspaces.

Do you know when this will be fixed for the Pro workspaces?  Is there a work around?

I cannot give you a timeline yet, but rest assured this will be updated for Pro workspaces as well. There is no workaround.

Is there an 'issue tracker' we can check to know this has been fixed? 

this is not an issue. It's a design that we're changing. But to answer your question, no there is no such thing. Just keep an eye on the blog and our documentation. Rest assured, this will be aligned before we reach general availability.

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors