Adding Metadata to an Azure Analysis Services Database

Question

How can metadata be added to a database or cube in Azure Analysis Services? Specifics include adding information such as descriptions, data officer contacts, and data stewards. What steps or methods are available to achieve this?

Answer 1

AAS supports adding extended properties to models, tables, columns, measures, and other objects. Extended properties are custom metadata that can hold information such as descriptions, contacts, and more.

PowerShell provides a way to automate the addition of extended properties.

# Connect to Azure Analysis Services

$server = "your-azure-analysis-service-server-name"

$database = "your-database-name"

$connectionString = "Data Source=$server"

# Import the necessary module

Import-Module SqlServer

# Set metadata for a model object

$model = Get-AnalysisServicesObject -Server $server -Database $database

Set-AnalysisServicesObjectExtendedProperty -Object $model -Name "Description" -Value "This is a description of the cube."

You can use Tabular Model Scripting Language (TMSL) or XMLA scripts to add annotations to tables, columns, and measures.This is useful for larger deployments or for repeated tasks.

After you prepate your script, you can deploy it using SSMS or directly through PowerShell/SQL scripts.

{
    "type": "Table",
    "name": "Sales",
    "extendedProperties": [
        {
            "name": "Description",
            "value": "This table contains sales data."
        }
    ]
}

If your end goal is to visualize the metadata or document it, you can also create a Power BI report that connects to the Azure Analysis Services model and pulls metadata through DAX or data models.

You can use DAX measures or Power Query to create a table displaying metadata descriptions, contact details, etc., which can be consumed by business users.

Links to help you :

https://docs.microsoft.com/en-us/sql/analysis-services/extended-properties-tabular-model?view=asallproducts-allversions

https://docs.microsoft.com/en-us/powershell/azure/analysis-services/

https://docs.microsoft.com/en-us/sql/analysis-services/xmla-commands?view=asallproducts-allversions

https://docs.microsoft.com/en-us/sql/analysis-services/tmsl-tabular-model-script?view=asallproducts-allversions