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
ESS

Report Portal
blank

SSAS 2008 Katmai – info from August 9th webcast

August 9th, 2007 by Vidas Matelis

In my last post I listed changes that are expected in SSAS 2008 next CTP 4. But it looks like none of the changes made into July CTP. They probably were postponed last minute. Even new Microsoft documentation (read Chris Web  blog) lists some of the features as present. For example block computation is listed in documentation, but just today in webcast I saw block computation example where in CTP4 it took query to execute 1min, and in new SSAS build the same query come back in a few seconds. So I hope that all changes listed in my previous post will be in the next CTP5, that is targeted for September 18th release (during PASS conference).

Today there was another good webcast presentation “The value of Business Intelligence with SQL Server 2008” by Julie Strauss (Product Manager) & Donald Farmer (Principal Program Manager). From this presentation I got more info on 2 new enhancements (not listed in my previous posts) that we should expect in the next CTP.

First of all, we should expect enhancements to aggregation designer. Algorithm that builds aggregations will be improved, there will be support for manual edit/create/delete of aggregations and we should be able to see what aggregates were designed. Also aggregation designer will have built-in validations for optimal design assistance. In my opinion this is very welcome change as I absolutely would love to have much better control on what aggregations are build.

And the another new enhancement will be DMV (Data Dynamic Management Views) that we will be able to use to access information about Analysis Services. As I understood, there will be something like “Default Resource cube”, that DMV will query and come back with useful information. During webcast presenter demonstrated 2 views:

  • $system.discover_connections
  • $system.discover_sessions

To access DMV, you simply write SELECT statement:

SELECT * FROM $system.discover_connections

Below are example of queries that list just some (!) of the columns available in these DMVs:

SELECT
connection_id
, connection_user_name
, connection_host_application
, connection_start_time
FROM $system.discover_connections

SELECT
session_id
, session_spid
, session_user_name
, session_last_command
, session_start_time
, session_cpu_time_ms
, session_reads
, session_writes
, session_status
, session_current_database
, session_used_memory
, session_properties
, session_start_time
, session_elapsed_time_ms
, session_last_command_start_time
, session_last_command_end_time
FROM $system.discover_sessions

As you can see there is a lot of info in session records. For example it is quite easy to get session ID and resources used by the session.

To list SSAS sessions that are currently active you can filter session DMV using WHERE session_status = 1:

To kill session, get session id from DMV and execute XMLA:

<Cancel xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine“>
  <SPID>###HereGoesSessionID###</SPID>
</Cancel>

Note: In 2008 cancel is reliable and should kill session instantly.

To get a list of columns available in DMV you can execute following queries: 

select * FROM $system.DBSCHEMA_columns WHERE table_name = ‘discover_sessions’
select * FROM $system.DBSCHEMA_columns WHERE table_name = ‘discover_connections’

I am sure DMVs will be very big help in finding runaway queries and monitoring SSAS.

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

5 Responses

  1. Cristian Lefter, SQL Server MVP Says:

    Hi Vidas, actually DMVs in this case stands for Dynamic Management Views and not for Data Management Views as they reflect server state info, info that is dynamicaly changing.

  2. Vidas Matelis Says:

    Cristian, Thanks for pointing this out. I fixed this.

  3. Ramesh Says:

    Hi Vidas,
    I like the information provided in this blog. I did download the Nov CTP and played with it for some time. As I understand from the webcast by MS, there should be a ‘Default Resource Cube’. But I could not find it on the server. Is it shipped with AS by default or do we need to create it manually.
    Thanks in advance
    Ramesh

  4. Ramesh Says:

    Hi Vidas,
    Thanks for the information. I was exploring Nov 2008 CTP and was wondering where is ‘Default Resource cube’. Is it shipped with AS installation or do we need to build it? Please confirm.
    Ramesh

  5. Vidas Matelis Says:

    Ramesh,

    You do not have to build resource cube.
    You can simply write queries. You can see examples here:
    http://www.ssas-info.com/VidasMatelisBlog/39_katmai-analysis-services-2008-november-ctp5-tests-on-metadata-rowsets

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.

XL Cubed