SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

As Microsoft released today November CTP 5 for SQL Server 2008 and this time there are quite a few changes in Analysis Services area. Documentation lists these SSAS changes in November CTP:

• Resource Monitoring: This concept is similar to DMV found in the relational engine. Analysis Services DMV addresses immediate needs of DBAs to get answers to the questions like: Who is connected to my server? What are the active sessions and what commands/queries they are running? What CPU consumed by particular query/command? What are the objects being referenced by particular command?
• MDX Query Optimizer - Block Computation: Block computations provide a significant improvement in processing performance, enabling users to increase the depth of their hierarchies and complexity of the computations.
• Aggregation Design:  SQL Server 2008 drives broader analysis with enhanced analytical capabilities and with more complex computations and aggregations. The AS Aggregation Design improvement exposes Aggregation Design objects in SQL Server BI Dev Studio and SQL Server Management Studio and provides tools for users to better work with these aggregation designs. In addition, an advanced view in the new Aggregation Design tab of the cube editor provides the ability for an advanced user to view and manually edit individual aggregations within an aggregation design.
• Analysis Services Cube Design:  New cube design tools help users streamline the development of the analysis infrastructure, enabling them to build solutions for optimized performance. The AS Cube Design improvement introduces a new Cube Wizard which helps users create better cubes in fewer steps.  The new wizard focuses on having the user answer a few questions to create leaner cubes that better targets their needs.  It also unblocks the previously difficult scenarios of creating a cube a cube based on a single, de-normalized table and creating a cube containing only linked dimensions.

Darren Gosbell already wrote about Aggregation Design improvements in his blog “SSAS 2008: New cube aggregations tab”.

Block computation is welcome MDX query performance improvement. I will test actual numbers for my database a bit latter when I’ll setup proper test environment. I am actually very optimistic about this change as I heard that almost everybody saw big improvement in MDX query performance.

New Analysis Services resource monitoring approach was already presented in the past webcasts but this time I run some tests to see how it works. As Microsoft described, concept is very similar to Dynamic Management Views (DMV) in SQL Server database. You simply write queries against tables, that returns result as rowset. Here are few queries that helps you monitor resources:

SELECT * FROM $system.DISCOVER_CONNECTIONS
SELECT * FROM $system.DISCOVER_MEMORYUSAGE
SELECT * FROM $system.DISCOVER_LOCKS
SELECT * FROM $system.DISCOVER_SESSIONS

I am not going to go here into the details on what these queries return, but you can guess a lot just from the table names. From the previous webcasts I got a feeling that Microsoft will have 4-5 of these special “DMV” tables. But while playing with analysis services instance that did not have any databases yet installed I found that there are 54 rowsets available for querying. You can get a list of available rowsets by executing following query in Analysis Services 2008:

SELECT table_schema, table_name
   FROM $system.dbschema_tables
 ORDER BY table_name

Here is result of this query from Analysis Services machine with no databases:

table_schema	table_name
$SYSTEM	DBSCHEMA_CATALOGS
$SYSTEM	DBSCHEMA_COLUMNS
$SYSTEM	DBSCHEMA_PROVIDER_TYPES
$SYSTEM	DBSCHEMA_TABLES
$SYSTEM	DISCOVER_COMMAND_OBJECTS
$SYSTEM	DISCOVER_COMMANDS
$SYSTEM	DISCOVER_CONNECTIONS
$SYSTEM	DISCOVER_DB_CONNECTIONS
$SYSTEM	DISCOVER_DIMENSION_STAT
$SYSTEM	DISCOVER_ENUMERATORS
$SYSTEM	DISCOVER_INSTANCES
$SYSTEM	DISCOVER_JOBS
$SYSTEM	DISCOVER_KEYWORDS
$SYSTEM	DISCOVER_LITERALS
$SYSTEM	DISCOVER_LOCKS
$SYSTEM	DISCOVER_MASTER_KEY
$SYSTEM	DISCOVER_MEMORYGRANT
$SYSTEM	DISCOVER_MEMORYUSAGE
$SYSTEM	DISCOVER_OBJECT_ACTIVITY
$SYSTEM	DISCOVER_OBJECT_MEMORY_USAGE
$SYSTEM	DISCOVER_PARTITION_DIMENSION_STAT
$SYSTEM	DISCOVER_PARTITION_STAT
$SYSTEM	DISCOVER_PERFORMANCE_COUNTERS
$SYSTEM	DISCOVER_PROPERTIES
$SYSTEM	DISCOVER_SCHEMA_ROWSETS
$SYSTEM	DISCOVER_SESSIONS
$SYSTEM	DISCOVER_TRACE_COLUMNS
$SYSTEM	DISCOVER_TRACE_DEFINITION_PROVIDERINFO
$SYSTEM	DISCOVER_TRACE_EVENT_CATEGORIES
$SYSTEM	DISCOVER_TRACES
$SYSTEM	DISCOVER_TRANSACTIONS
$SYSTEM	DMSCHEMA_MINING_COLUMNS
$SYSTEM	DMSCHEMA_MINING_FUNCTIONS
$SYSTEM	DMSCHEMA_MINING_MODEL_CONTENT
$SYSTEM	DMSCHEMA_MINING_MODEL_CONTENT_PMML
$SYSTEM	DMSCHEMA_MINING_MODEL_XML
$SYSTEM	DMSCHEMA_MINING_MODELS
$SYSTEM	DMSCHEMA_MINING_SERVICE_PARAMETERS
$SYSTEM	DMSCHEMA_MINING_SERVICES
$SYSTEM	DMSCHEMA_MINING_STRUCTURE_COLUMNS
$SYSTEM	DMSCHEMA_MINING_STRUCTURES
$SYSTEM	MDSCHEMA_CUBES
$SYSTEM	MDSCHEMA_DIMENSIONS
$SYSTEM	MDSCHEMA_FUNCTIONS
$SYSTEM	MDSCHEMA_HIERARCHIES
$SYSTEM	MDSCHEMA_INPUT_DATASOURCES
$SYSTEM	MDSCHEMA_KPIS
$SYSTEM	MDSCHEMA_LEVELS
$SYSTEM	MDSCHEMA_MEASUREGROUP_DIMENSIONS
$SYSTEM	MDSCHEMA_MEASUREGROUPS
$SYSTEM	MDSCHEMA_MEASURES
$SYSTEM	MDSCHEMA_MEMBERS
$SYSTEM	MDSCHEMA_PROPERTIES
$SYSTEM	MDSCHEMA_SETS

Some of these rowsets gives you information about resources, others describes analysis services objects (measures, dimensions, cubes, etc.).  

Things got even more interesting after I deployed a few Analysis Services databases. Now the same query from $system.dbschema_tables returned all analysis services objects as rowsets available for querying. That is for example, you can now write a query, that will return a rowset with a list of Account dimension members:

SELECT * FROM [Adventure Works].[$Account]

There is a lot of information available through this  new rowsets, I just have to figure out know how to make it useful.