Microsoft Learn

Databases in Analysis Services Multidimensional Modeling

In SQL Server Analysis Services (SSAS) Multidimensional Model, a database is the fundamental container for your business intelligence solutions. It holds all the objects that define your analytical models, including cubes, dimensions, measures, and related metadata. Understanding how to create, manage, and interact with Analysis Services databases is crucial for building effective analytical solutions.

Creating an Analysis Services Database

You can create an Analysis Services database using SQL Server Data Tools (SSDT) or by scripting using AMO (Analysis Management Objects) or XMLA (XML for Analysis).

Using SQL Server Data Tools (SSDT)

1. Open Visual Studio with SQL Server Data Tools installed.
2. Go to File > New > Project.
3. Under Business Intelligence, select Analysis Services.
4. Choose the Analysis Services Multidimensional Project template.
5. Provide a name for your project and click OK.
6. Once the project is created, you can start adding data sources, multidimensional models (cubes, dimensions), and deploy it to an Analysis Services instance. Deployment typically creates the database on the server if it doesn't exist.

Using XMLA

You can execute an XMLA command to create a database. Here's a basic example:

<CreateDatabase xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Name>MyNewAnalysisDatabase</Name>
  <DataSource>
    <DataSourceID>MyDataSourceID</DataSourceID>
    <ConnectionString>Provider=MSOLAP.8;Data Source=YourServerName;Initial Catalog=YourDWDatabase;Integrated Security=SSPI;</ConnectionString>
  </DataSource>
</CreateDatabase>
            

Managing Analysis Services Databases

Managing Analysis Services databases involves several key operations:

• Connecting to the Server: Use SQL Server Management Studio (SSMS) or SQL Server Data Tools (SSDT) to connect to your Analysis Services instance.
• Viewing Databases: In SSMS, expand the Analysis Services server node to see a list of all existing databases.
• Creating New Databases: As described above, via SSDT or XMLA/AMO.
• Deleting Databases: Right-click on a database in SSMS and select 'Delete'. Be cautious as this is irreversible and removes all associated objects.
• Backup and Restore: Regularly back up your Analysis Services databases to prevent data loss. SSMS provides tools for this.
• Processing: After making changes to the underlying data sources or model structure, you need to process the database and its objects (cubes, dimensions) to update the data.
• Security: Configure database roles and permissions to control access to the data and model objects.

Database Properties

Each Analysis Services database has various properties that define its behavior and configuration. These can be viewed and modified in SSMS under the database's Properties menu. Key properties include:

• ID: A unique identifier for the database.
• Name: The display name of the database.
• Compatibility Level: Indicates the version of Analysis Services the database is designed for.
• Language: The default language for the database.
• Collation: Defines the character set and sorting rules.

Best Practices

• Use descriptive names for your databases and all their contained objects.
• Implement a robust backup and disaster recovery strategy.
• Secure your databases by applying appropriate roles and permissions.
• Regularly process your databases to ensure data is up-to-date.
• Keep your Analysis Services instance and development tools updated.

By mastering the management and structure of Analysis Services databases, you lay the groundwork for powerful and insightful business intelligence solutions.