Power Pivot Principles: The A to Z of DAX Functions – INFO.DATASOURCES

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at INFO.DATASOURCES.

The INFO.DATASOURCES function

Dynamic Management Views (DMVs) are specialised queries provided by SQL Server Analysis Services (SSAS), Azure Analysis Services (AAS), and Power BI that offer an administrative view into the internal state of these systems.  DMVs are used to retrieve metadata, monitor health and performance, and diagnose problems within the database or data model.  They serve as a powerful tool for administrators and developers to gain insights into the workings of the database engine and the tabular data model, covering aspects like performance metrics, configuration settings and the structure of database objects.

The $System schema DMVs in SQL Server Analysis Services (SSAS), Azure Analysis Services (AAS), and Power BI are categorised into four [4] types, each serving specific purposes:

• DISCOVER: requires admin privileges and provides information about the model, including details on connected sessions and environment configuration
• DMSCHEMA: focused on data mining, offering insights for predictive analytics and pattern recognition, mainly used in SSAS/AAS
• MDSCHEMA: targets multidimensional models, delivering metadata and structure from an MDX perspective, relevant for OLAP cubes and dimensions
• TMSCHEMA: designed for tabular models, it provides detailed metadata about tables, columns, measures, etc., using Tabular Model Scripting Language (TMSL) information, crucial for Power BI and tabular SSAS/AAS models.

In the past, if we wanted to query those $System schema DMVs we used external tools like Tabular Editors or DAX Studio to query them:

The INFO.DATASOURCES function is one of the system functions.  It employs the following syntax:

This function has no parameters.

Based upon the ‘[MS-SSAS-T]: SQL Server Analysis Services Tabular Protocol’ from Microsoft (which you may access here), the DataSource object represents an external source of data.  It is a child of a Model object.

We can write this INFO.DATASOURCES function in DAX query view to get the same information on the TMSCHEMA_DATA_SOURCES:

It will query the $SYSTEM.TMSCHEMA_DATA_SOURCES and return an entire table with 18 columns:

• ID: this represents a reference to the object
• ModelID: this represents an ID-based reference to a Model object
• Name: this represents name of the object
• Description: this is an ID-based reference to a LinguisticMetadata object
• Type: this is the type of the DataSource.  The only possible values are as follows:
• Provider (1) – a data source that has a data provider and connection stringo   
• Structured (2) – a data source that uses a JSON-based extensible protocol to define the location and mechanism by which the data is retrieved.  The compatibility level 1400 or higher is required
• ConnectionString: this is a string that is used to open the connection to a provider data source
• ImpersonationMode: this is a numeric value that specifies the credentials to use for impersonation when connection to a provider data source.  The enumeration values are as follows:
• Default (1) – the server uses the inherited value of ImpersonationMode from the database object’s DataSourceImpersonationInfo
• ImpersonateAccount (2) - the server uses the specified user account
• ImpersonateAnonymous (3) - the server uses the anonymous user account
• ImpersonateCurrentUser (4) - the server uses the user account that the client is connecting as
• ImpersonateServiceAccount (5) - the server uses the user account that the server is running as
• ImpersonateUnattendedAccount (6) – the server uses an unattended user account
• Account: this represents the user account that is used for impersonation when connecting to provider data source
• Password: this is the password that is used to impersonate the specified user account when connecting to a provider data source
• MaxConnections: the maximum number of connections to be opened concurrently to the data source
• Isolation: the kind of isolation that is used when executing commands against the provider data source.  The possible values are as follows:
• ReadCommitted (1) – this value specifies that statements cannot read data that has been modified, but not committed, by other transactions
• Snapshot (2) – this value ensures that the data read by any statement in a transaction is transactionally consistent, as if the statements in a transaction receive a snapshot of the committed data as it existed at the start of the transaction
• Timeout: this represents the timeout in seconds for commands executed against a provider data source
• Provider: this is an optional string that identifies the name of the managed data provider for the provider data source
• ModifiedTime: this represents the time that the object was last modified
• ConnectionDetails: this represents the information that identifies the location of the structured data source.  This is a property bag formatted as a JSON string that allows details about the connection to the data source to be passed.  The compatibility level 1400 or higher is required
• Options: this is the information that defines possible additional settings for the structured data source.  This is a property bag formatted as a JSON string.  The compatibility level 1400 or higher is required
• Credential: this represents the credential information that authenticates against the structured data source.  This is a property bag formatted as a JSON string.  Compatibility level 1400 or higher is required
• ContextExpression: this is a string that can contain additional information, such as content type, content shape, and format, about the structure and/or metadata of the structured data source.  The data source is then represented by the ConnectionDetails property.  Compatibility level 1400 or higher is required.

It should be noted that:

• it is used for querying the DMV (Dynamic Management Views) from the $System schema called TMSCHEMA where TM stands for ‘Tabular model’ and TMSCHEMA provides information from the tabular model
• sometimes querying DMVs may fail if we do not have the appropriate permission.

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.