Sql-server – Get Results from DMV Query into SQL Server Table

dmvsql serverssas

today I began to troubleshoot an issue that I was asked to resolve, and for the first time I found myself querying in the SSAS database. I obtained a very useful set of results (who was connecting to what applications/cubes and for how long), but for the life of me I cannot figure out how to get the results from that query into a database table so that I can track the data. Any help would be much appreciated, and thank you in advance for any assistance you are able to provide.

Here is the query I am running:

Select connection_id , connection_user_name ,  
connection_impersonated_user_name , connection_host_name ,   
connection_host_application , connection_start_time , 
connection_elapsed_time_ms , connection_last_command_start_time , 
connection_last_command_end_time , connection_last_command_elapsed_time_ms , 
connection_idle_time_ms , connection_bytes_sent , connection_bytes_received , 
connection_data_bytes_received FROM $system.discover_connections

This returns all of the information I am currently interested in seeing.

Best Answer

In SSAS, there's no such thing as a table; it's all multi-dimensional stuff. What you need to do is get the results into a table in a database engine instance.

Here's one way to do it:

Create a linked server to SSAS on your management SQL Server database engine instance. (Note: you'll need to install the SSAS drivers if the two instances aren't on the same box.)
Create a job in SQL Agent that periodically queries the SSAS DMV over the linked server and dumps the results into a relational table.

Related Solutions

Sql-server – Getting current date as part of an Stored Procedure

1) I'm not going to argue database design, since it seems that it's already in place.

2) Looking at the code, I have to presume that StartOnCallDate, StartOnCallTime, and Duration are Integers. If you're trying to do that with DATETIME, then there are other issues to deal with.

To answer your question...

First off, the math in your where clause is still including the time (hours and seconds). So this stuff:

DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
            DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE()))

Gives this result:

SELECT DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
        DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE()))
-----------------------
2011-08-12 15:35:00.000

So, when you compare DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899') to that mess above, it will almost never be equal. That's because when you use DATEADD(DAY...) to that hard-coded date, you will only end up with values that are days:

SELECT DATEADD(DAY, 40800, '12/31/1899')
-----------------------
2011-09-15 00:00:00.000

To drop the hours and seconds, you could do a bunch more DATEADD stuff, or you could just do something like this:

(SQL Server 2008):
CONVERT (date, GETDATE())

(SQL Server 2005/2008 options):
CAST(CONVERT(VARCHAR(10), getdate(), 101) AS DATETIME)
CAST(FLOOR(CAST(GETDATE() AS DECIMAL(12, 5))) AS DATETIME)

Those will get you JUST the date. If you throw one of those into your where clause, it will allow you to filter the data, showing you all values that match the current DAY (anything that is going to occur today):

WHERE DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')= 
    CAST(CONVERT(VARCHAR(10), getdate(), 101) AS DATETIME) AND
    mOnCallAdd.SchedName = 'arc im'

To give you an idea of the values you are comparing, pull each of those out (with sample data) into a select statement:

SELECT 
    DATEADD(DAY, 40766, '12/31/1899'), 
    CAST(CONVERT(VARCHAR(10), getdate(), 101) AS DATETIME)
----------------------- -----------------------
2011-08-12 00:00:00.000 2011-08-12 00:00:00.000

Now, to filter out anything that might have already passed, you want to add a condition to your WHERE clause that checks the ending time to determine if that is earlier that the current time.

 DATEADD(MINUTE, mOnCallAdd.duration,
         DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
         DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > GetDate()

This makes the entire where clause:

WHERE DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')= 
        CAST(CONVERT(VARCHAR(10), getdate(), 111) AS DATETIME) AND
     DATEADD(MINUTE, mOnCallAdd.duration,
             DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > GetDate() AND
     mOnCallAdd.SchedName = 'arc im'

EDIT:

If you only want items to show up that are currently happening, you need to qualify on both your end time (as above) and your start time. To qualify your start time, you need to get your start time and make sure that the current time is greater than or equal to the current time:

DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
        DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) <= GetDate()

This makes the entire WHERE clause:

WHERE DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')= 
        CAST(CONVERT(VARCHAR(10), getdate(), 111) AS DATETIME) AND
     DATEADD(MINUTE, mOnCallAdd.duration,
             DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > GetDate() AND
     DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
             DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) <= GetDate() AND
     mOnCallAdd.SchedName = 'arc im'

SCD Type 2 Dimension -> Is this the correct layout for a type 2 scd with this type of data present

Making SSAS hierarchies on slowly changing dimensions is a bit of a fiddle. You need to make surrogate keys for each historical version at each level of the hierarchy. Then the key has the actual business facing name, which the user selects or reports by.

As an example, imagine worker BloggsJ in Division1, which is in LineOfBusiness1. Now Division1 gets moved to LineOfBusiness2. Logically you have the Division entity with two rows now:

DivisionKey     Division     LineOfBusinessKey
          1     Division1                   11 
          2     Division2                   12
          3     Division1                   12

and

LineOfBusinessKey    LineOfBusiness
               11    LineOfBusiness1
               12    LineOfBusiness2

Now, we have worker BloggsJ, who is assigned to Division 1, which is subsequently moved

WorkerKey  WorkerName   DivisionKey   Division   LineOfBusinessKey   LineOfBusiness
      101     BloggsJ             1   Division1                 11  LineOfBusiness1
      102     BloggsJ             3   Division1                 12  LineOfBusiness2
      103     SmithF              2   Division2                 12  LineOfBusiness2

In this case the keys remain in a strictly hierachical order:

LineOfBusiness2 (Key=12) has two children: Division2 (Key=2) and Division1 (Key=3). Division1 (Key=3) has one child: BloggsJ (Key=102) and Division2 (Key=2) has one child: SmithF (Key=103).
LineOfBusiness1 (Key=11) has one child: Division1 (Key=1), which has one Child: BloggsJ (Key=101)

Displaying the name in the cube allows you to build a hierarchy that can support drill-down operations. You will also probably want to hide the base attributes for this hierarchy and display another set with just the names, so there is something in the dimension that will produce a clean, unique list of members at each level without opaque, confusing repeated names.

*****Update 05012012 3:22 PM CST

Here is an image of my data example.

enter image description here

Best Answer

Related Solutions

Sql-server – Getting current date as part of an Stored Procedure

SCD Type 2 Dimension -> Is this the correct layout for a type 2 scd with this type of data present

Related Question