Dynamic Management Views for the Analysis Service Db Monitoring

SQL Server 2008 has some of the cool DMV (Dynamic Management View) which can be used for analysis and monitoring of analysis server databases. You can in fact drill to the details to figure out when the cube and other objects like dimesions, measure groups etc were last processed or modified, whether hierarchy is being used etc.

You can run these DMV’s either in DMX query window or using link server on the relational database query window.

Here find list of DMV’s and their categories –

Analysis Service database Schema

$SYSTEM.DBSCHEMA_CATALOGS

$SYSTEM.DBSCHEMA_COLUMNS

$SYSTEM.DBSCHEMA_PROVIDER_TYPES

$SYSTEM.DBSCHEMA_TABLES

Data Mining Schema

$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

Meta Data of Analysis Service Database objects like Cube and ther partitions, Hierarchies etc

$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

To run and get audit and monitoring details of Analysis Service Database, say find the details when the cubes were last updated and processed –

1. Open Sql Server Management Studio
2. Connect to your one of the analysis service database.
3. Right click on the database name and select DMX query.
   
4. Copy and paste following query in the DMX query window –
   SELECT * FROM $SYSTEM.MDSCHEMA_CUBES WHERE CUBE_SOURCE = 1
   
   Find below the result of above query. Only relevant and important columns are included -
   
   

   CUBE_NAME	LAST_SCHEMA_UPDATE	LAST_DATA_UPDATE
   Audit	5/19/2011 16:01	5/20/2011 6:46
   Cost Center	5/19/2011 16:01	5/20/2011 6:46
   Dashboard Metrics	5/19/2011 16:01	5/20/2011 6:46
   High Stability	5/19/2011 16:01	5/20/2011 6:46
   Manage Risk	5/19/2011 16:01	5/20/2011 6:46
   Resource Management	5/19/2011 16:01	5/19/2011 15:43
   SR Practices	5/19/2011 16:01	5/20/2011 6:46
   Superiror Delivery	5/19/2011 16:01	5/20/2011 6:46
   Top Programs	5/19/2011 16:01	5/19/2011 15:43
   World Class People	5/19/2011 16:01	5/20/2011 6:46

Alternatively you can run query connecting to the underline relation database query window. You would need to use link server for query to be able to connect to the analysis service database.

Above query can be run in the relation query window –

SELECT * FROM OPENQUERY(YOUR_LINK_SERVER_NAME,'select * from $SYSTEM.MDSCHEMA_CUBES WHERE CUBE_SOURCE = 1')

Format value using UDF based on format type

I encountered a situation where I had to return formatted value to the front end site based on the format type passed. Format type can be dollar ($), number (#) or percentage (%). I am not sure if this is good way to format value based on type passed, but worked for me perfectly –

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DW].[fnGetFormattedValue]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [DW].[fnGetFormattedValue]
GO

CREATE FUNCTION [DW].[fnGetFormattedValue](
    @Value FLOAT,
    @FormatType Varchar(50)
)

RETURNS VARCHAR(50) 
AS

BEGIN

    DECLARE @ReturnValue VARCHAR(100)
    DECLARE @TempValue DECIMAL(10, 2)

    SET @TempValue = CONVERT (DECIMAL(10, 2), ROUND(@Value, 2))
    SET @ReturnValue =
    
    CASE @FormatType WHEN '$' THEN '$' + CAST (@TempValue AS VARCHAR)
    WHEN '%' THEN CAST (@TempValue AS VARCHAR) + '%'
    WHEN '#' THEN CAST (@TempValue AS VARCHAR)
    ELSE CAST (@TempValue AS VARCHAR)
    END

    RETURN @ReturnValue

END
GO

SQL find duplicate using Group and Partition By

There are various ways to find duplicate records in SQL Server. Like using group and count, partitioning result set etc.

This article talks about finding duplicate records using two of these methods. I will straight away jump into the SQL example for you to understand easily and quickly.

Create a table name with [STG].[TMS_TOP_PROGRAMS] with following entries into it. This table I have all the programs along with some other attributes like ID, Sort Order etc -

PROGRAM_ID	PROGRAM_NAME	SORT_ORDER	BATCH_ID
11	Align the Right Customers to the Right Solutions at Optimized cost	1	100
11	Align the Right Customers to the Right Solutions at Optimized cost	1	100
12	Create competitive advantage through business analytics and benchmarking	6	100
13	Deploy future state CSSG operating model	2	100
13	Deploy future state CSSG operating model	2	100
14	Enhance employee engagement	7	100
14	Enhance employee engagement	7	100
15	Expand Direct Sales Capabilities to Grow Revenue	3	100
16	Optimize Service Contacts to Create Sales Opportunities	4	100
16	Optimize Service Contacts to Create Sales Opportunities	4	100
17	Simplify End to End Customer Experience	5	100
17	Simplify End to End Customer Experience	5	100
6	OpEx Enterprise-Wide Programs	6	100
6	OpEx Enterprise-Wide Programs	6	100

As you see there are lot of duplicate programs in this table. One ways to find duplicate records is by using Group clause as under -

SELECT 
    PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM, 
    SORT_ORDER,  [BATCH_ID], COUNT(PROGRAM_ID)
FROM [STG].[TMS_TOP_PROGRAMS] 
GROUP BY PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM, PROGRAM_SORT_ORDER, [BATCH_ID]
HAVING COUNT(PROGRAM_ID) > 1

Another and yet more efficient way to find duplicate by partitioning the table based on the key, here is what I mean..

SELECT PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM, PROGRAM_SORT_ORDER, [BATCH_ID], RANK_ID FROM
(
    SELECT PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM, 
    SORT_ORDER, [BATCH_ID],
    ROW_NUMBER() OVER  (
       PARTITION BY PROGRAM_ID ORDER BY PROGRAM_ID
    ) AS RANK_ID
    FROM [STG].[TMS_TOP_PROGRAMS]
 ) AS INTBL WHERE RANK_ID > 1

Filter and Count in MDX

Filter and Count in MDX
If you have database table as shown and you requirement is to filter value out before you count the row. In my example, notice the highlighted row, I got 0 score for those 2 rows and I don't want such row included when I either create a calculated member in the cube or write report using MDX. In this example I need to get sum of the programs only if score > 0. This is how you the filter condition with count will look -
MEMBER [Measures].[Program_Count] AS
count (filter([TOP PROGRAMS].[PROGRAM ID].[PROGRAM ID], [Measures].[PROG SCORE]>0))

SQL Insert Update using MERGE

This will be a very short blog post to help you understand the syntax and usage of Merge SQL statement. I will use same program table ([STG].[TMS_TOP_PROGRAMS]) as source table which I am used in my some of of the other blog posts.

 

MERGE is very handy when it comes to update or insert using a single SQL statement. In my example I will use PROGRAM_ID to check whether row already exists and if so will update all other attributes else will insert into [STG].[TMS_TOP_PROGRAMS] table.

 

This is the source table ([STG].[TMS_TOP_PROGRAMS]).

 

PROGRAM_ID	PROGRAM_NAME	SORT_ORDER	BATCH_ID
11	Align the Right Customers to the Right Solutions at Optimized cost - New	1	100
12	Create competitive advantage through business analytics and benchmarking - New	6	100
13	Deploy future state CSSG operating model	2	100
14	Enhance employee engagement - New	7	100
15	Expand Direct Sales Capabilities to Grow Revenue	3	100
16	Optimize Service Contacts to Create Sales Opportunities - New	4	100
17	Simplify End to End Customer Experience	5	100
6	OpEx Enterprise-Wide Programs	6	100
7	Enterprise Services	8	100

This is how destination table ([DW].[TMS_DW_TOP_PROGRAMS]). Idea is, key from the source table (PROGRAM_ID in my example) will be compared against destination table (PROGRAM_ID column) and if match found a particulate row will be updated else new row will be inserted into the table.

PROGRAM_ID	PROGRAM_NAME	SORT_ORDER	BATCH_ID
1	Organization Alignment	7	100
11	Align the Right Customers to the Right Solutions at Optimized cost	1	100
12	Create competitive advantage through business analytics and benchmarking	6	100
13	Deploy future state CSSG operating model	2	100
14	Enhance employee engagement	7	100
15	Expand Direct Sales Capabilities to Grow Revenue	3	100
16	Optimize Service Contacts to Create Sales Opportunities	4	100
17	Simplify End to End Customer Experience	5	100
6	OpEx Enterprise-Wide Programs	6	100

Let’s write and check query now. Following query will insert Program ID 7 into destination table as it does not exist and will update all other records from the source. Note that Program ID 1 in the destination table will not have any impact as it is not present in the source.

MERGE [DW].[TMS_DW_TOP_PROGRAMS] AS SR
USING
(
SELECT PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM, SORT_ORDER, [BATCH_ID], RANK_ID FROM
(
    SELECT PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM, 
    SORT_ORDER, [BATCH_ID],
    ROW_NUMBER() OVER (
      PARTITION BY PROGRAM_ID ORDER BY PROGRAM_ID) AS RANK_ID
  FROM [STG].[TMS_TOP_PROGRAMS]
) AS INTBL WHERE RANK_ID = 1
) AS TR
ON SR.PROGRAM_ID = TR.PROGRAM_ID
WHEN MATCHED THEN 
    UPDATE SET SR.[PROGRAM_NAME] = TR.[PROGRAM_NAME], 
            SR.KEY_PROGRAM = TR.KEY_PROGRAM, 
            SR.SORT_ORDER = TR.SORT_ORDER, 
WHEN NOT MATCHED THEN 
    INSERT (
            PROGRAM_ID, [PROGRAM_NAME], KEY_PROGRAM,      
            PROGRAM_SORT_ORDER, [BATCH_ID]
            )
    VALUES (
            TR.PROGRAM_ID, TR.[PROGRAM_NAME], TR.KEY_PROGRAM,  
            TR.PROGRAM_SORT_ORDER, TR.[BATCH_ID]
           );

Null value is eliminated by an aggregate or other SET operation.

Null value is eliminated by an aggregate or other SET operation
You get this error while executing SQL statement or stored procedure, if you use aggregate function like sum, max, min etc on a column which contains NULL value.
Example:
SET @METRIC_ID = (SELECT MAX(Convert(int,Metric_ID))+1 from [WEB].[TMS_TOP_PROGRAMS_ENTRY]);
Change above statement with this to get rid of above warning (ANSI):
SET @Metric_ID = (SELECT MAX(Convert(int,isnull(Metric_ID,0)))+1 from [WEB].[TMS_TOP_PROGRAMS_ENTRY]);