About me

I work as SQL Server DBA / Developer and BI Consultant in Toronto, Canada.Canada Flag More...
Vidas Matelis picture

MVP Logo
Pyramid Analytics

Search

Pyramid Analytics
blank
Report Portal
blank

MS SQL Server Analysis Services 2008 – November CTP quick review

November 19th, 2007 by Vidas Matelis

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:

  • MOLAP Performance (FITS): New MOLAP-enabled write-back capabilities in SQL Server 2008 Analysis Services remove the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance.
  • 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.

Posted in SSAS, SSAS 2008 - Katmai | 3 Comments »

3 Responses

  1. The Riyaz Blog : Jason Morales' Microsoft BI Update v.15 - December Says:

    […] SQL 2008 CTP, November 2007 […]

  2. Raj Says:

    Hi there,

    Could you please tell me how did you connect to Analysis services (I mean using which editor and what user were you running the queries) to execute

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

    Kind Regards
    Raj

  3. Vidas Matelis Says:

    Raj,

    Start SQL Server Management Studio, connect to your Analysis Services. Select Database and on the top toolbar choose “New Query”. There you can enter all DMV query statements.

XL Cubed