Forum Discussion

mestee's avatar
mestee
Frequent Visitor
8 months ago
Solved

Creating a non-human connection in Power BI Service to query DMVs via XMLA endpoint (Import Mode)

Goal: 
Create a non-human connection in the Power BI Service (PBIS) that connects to the semantic model DMV views to run queries using Import mode.
Why: 
Until we have a more robust platform in place, I have been documenting our semantic models in Excel. Among other information, this includes measures, tables/columns, and the SQL statements in each applicable dim and fact table.
How:
In DAX Studio, I run the applicable queries and output the results to the Excel document. This document is available to a select group of individuals who perform validations on reports that are built off of the semantic model. 

As a next step in this process, we would like to eliminate the Excel document and instead add the output of those queries, as tables, in the model itself, and then create a report off of the model that would be published to PBIS for these individuals to view. These reports output exactly what was in the Excel document: a clear list of measures, tables/columns, and the SQL statements for the facts and dims. Moving towards a more self-documenting method.

What I've done:
In Power BI desktop, I create the tables for the measures, tables and table columns from the respective view (example: INFO.VIEW.MEASURES).
However, for the SQL statements, I get this information using the 
SQL Server Analysis Services connection, where the server is the XMLA endpoint obtained from PBIS, and authentication is my Microsoft account for credentials. I created a table called model_SQL that has the following query:
SELECT
[TableID],
[QueryDefinition]
FROM $SYSTEM.TMSCHEMA_PARTITIONS

Next, I deploy the model to PBIS. 
In PBIS, in the model settings, I see 2 options to create a connection.

1. Gateway connections (we have an on-premises data gateway):
Under this section, the AnalysisServices connection shows as 'Optional - Add to gateway'.
If I select this option, the only authentication method is Windows.

2. Cloud connections:
Under this section, the AnalysisServices connection shows Maps to Personal Cloud Connect or clicking on the drop-down the options are: Basic, OAuth 2.0, Workspace identity. 

I would like to use Service Principal but would entertain another non-human account that works in the same way, provided our setup allows it.

Looking for guidance on options to create this non-human account for the AnalysisServices authentication (or if there's another way I should connect) on the model.

If I'm missing any information that may be helpful in your response, please let me know.

Many Thanks!
Maria



  • Hi mestee 

     

    Could I recommend using the Power BI, Scanner, API information. This contains all the information that you were talking about and more and this process can be fully automated, ideally using something like a notebook to download the scanner API and expand the information into a laycast table which you could then modify and get it into the format you need using Power Query. Will then be refreshable in a Power BI report which at the end of the day you could then use a pivot table in Excel if needed.

3 Replies

  • Hi mestee 

     

    Could I recommend using the Power BI, Scanner, API information. This contains all the information that you were talking about and more and this process can be fully automated, ideally using something like a notebook to download the scanner API and expand the information into a laycast table which you could then modify and get it into the format you need using Power Query. Will then be refreshable in a Power BI report which at the end of the day you could then use a pivot table in Excel if needed.

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi mestee,

     

    Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to GilbertQ  for prompt and helpful responses.

    Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

     

    Best regards,
    Prasanna Kumar

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi mestee,

     

    Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

     

    Best regards,
    Prasanna Kumar