Friday, October 27, 2017

Using AMO to query cube , partitions and other SSAS objects- How to find query definition of the partition

Using AMO to query cube , partitions and other SSAS objects- How to find query definition of the partition
I was doing some work around AMO and SSAS and I need to load a partition from database and read some of its properties . I was able to do most of them but the <QueryDefinition> was a bit of issue. I thought I will write a small blog on it. It might be useful for someone.
But if this Partition uses a dsvTable binding than this wont work. Anyway this is a small console application that does that plus it also serves a intro to how to use AMO to connect to Analysis service Server and use the Object model to program against the SSAS Objects.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.AnalysisServices;
using System.IO;

namespace TestBed
{
    class Program
    {
        static void Main(string[] args)
        {
            Server srv = new Server();
            srv.Connect("Data source=IMGLT119;Initial catalog=Adventure Works DW");
            Database db = srv.Databases.FindByName("Adventure Works DW");
            Console.WriteLine("Connected to Database {0} on server {1}", db.Name, srv.Name);
            foreach (Cube cb in db.Cubes)
            {
                if (cb.Name == "Adventure Works")
                {
                    foreach (MeasureGroup mg in cb.MeasureGroups)
                    {
                        foreach (Partition pt in mg.Partitions)
                        {
                            
                                //You can access the partition properties . Well Most of it.
                                Console.Write(" partition name is {0} partition slice before is {1}", pt.Name, pt.Slice);
                                QueryBinding qb = new QueryBinding();
                                //I had a bit of glitch with finding the Query Definition . If you have table binding than this wont work . 
                                qb = (QueryBinding)pt.Source;
                                Console.WriteLine("Query Definition {0}", qb.QueryDefinition.ToString()  );
                                break;
                            }

                        }
                    }

                    break;
            }
            Console.Read();
        }
    }
}
Posted by at No comments:

SSAS: Using DMV Queries to get Cube Metadata


SSAS Dynamic Management Views (DMV’s) are very useful to query metadata of a cube. For developers, this is quite handy to provide simple documentation for the cubes they build.

There are some excellent posts on SSAS DMVs by Yaniv Mor and Vidas Matelis
Given below are some of the queries which I found particularly useful. A complete reference to the DMV schema rowset is available in msdn – http://msdn.microsoft.com/en-us/library/ms126079.aspx. Execute these queries from SQL Server Management Studio (SSMS) using MDX or DMX query editor.
1
2
3
4
--All Cubes in database
SELECT [CATALOG_NAME] AS [DATABASE],CUBE_CAPTION AS [CUBE/PERSPECTIVE],BASE_CUBE_NAME
FROM $system.MDSchema_Cubes
WHERE CUBE_SOURCE=1

1
2
3
4
5
6
7
--All dimensions in Cube
SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],DIMENSION_CAPTION AS [DIMENSION]
 FROM $system.MDSchema_Dimensions
WHERE CUBE_NAME  ='Adventure Works'
AND DIMENSION_CAPTION <> 'Measures'
ORDER BY DIMENSION_CAPTION

1
2
3
4
5
6
7
8
9
--All Attributes
SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
HIERARCHY_DISPLAY_FOLDER AS [FOLDER],HIERARCHY_CAPTION AS [DIMENSION ATTRIBUTE],
HIERARCHY_IS_VISIBLE AS [VISIBLE]
 FROM $system.MDSchema_hierarchies
WHERE CUBE_NAME  ='Adventure Works'
AND HIERARCHY_ORIGIN=2
ORDER BY [DIMENSION_UNIQUE_NAME]

1
2
3
4
5
6
7
8
9
10
11
--All Attributes with key and name columns
SELECT [CATALOG_NAME] as [DATABASE],
      CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
      LEVEL_CAPTION AS [ATTRIBUTE],
      [LEVEL_NAME_SQL_COLUMN_NAME] AS [ATTRIBUTE_NAME_SQL_COLUMN_NAME],
      [LEVEL_KEY_SQL_COLUMN_NAME] AS [ATTRIBUTE_KEY_SQL_COLUMN_NAME]
FROM $system.MDSchema_levels
WHERE CUBE_NAME  ='Adventure Works'
AND level_origin=2
AND LEVEL_NAME <> '(All)'
order by [DIMENSION_UNIQUE_NAME]

1
2
3
4
5
6
7
8
9
--All Hierarchies (user-defined)
SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
HIERARCHY_DISPLAY_FOLDER AS [FOLDER],HIERARCHY_CAPTION AS [HIERARCHY],
HIERARCHY_IS_VISIBLE AS [VISIBLE]
 FROM $system.MDSchema_hierarchies
WHERE CUBE_NAME  ='Adventure Works'
and HIERARCHY_ORIGIN=1
ORDER BY [DIMENSION_UNIQUE_NAME]

1
2
3
4
5
6
7
8
9
--All Hierarchies (Parent-Child)
SELECT [CATALOG_NAME] as [DATABASE],
CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
HIERARCHY_DISPLAY_FOLDER AS [FOLDER],HIERARCHY_CAPTION AS [HIERARCHY],
HIERARCHY_IS_VISIBLE AS [VISIBLE]
FROM $system.MDSchema_hierarchies
WHERE CUBE_NAME  ='Adventure Works'
AND HIERARCHY_ORIGIN=3
ORDER BY [DIMENSION_UNIQUE_NAME]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--All Levels of Hierarchies (user-defined)
SELECT [CATALOG_NAME] as [DATABASE],
    CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
    [HIERARCHY_UNIQUE_NAME] AS [HIERARCHY],
    LEVEL_CAPTION AS [LEVEL],
    [LEVEL_NAME],
    [LEVEL_NUMBER] AS [LEVEL NUMBER],
    [LEVEL_NAME_SQL_COLUMN_NAME] AS [NAME_COLUMN],
    [LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME] AS [UNIQUE_NAME_COLUMN],
    [LEVEL_KEY_SQL_COLUMN_NAME] AS [KEY_COLUMN]
 FROM $system.MDSchema_levels
 WHERE CUBE_NAME  ='Adventure Works'
 AND level_origin=1
 order by [DIMENSION_UNIQUE_NAME]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--All Levels of Hierarchies (Parent-Child)
SELECT [CATALOG_NAME] as [DATABASE],
    CUBE_NAME AS [CUBE],[DIMENSION_UNIQUE_NAME] AS [DIMENSION],
    [HIERARCHY_UNIQUE_NAME] AS [HIERARCHY],
    LEVEL_CAPTION AS [LEVEL],
    [LEVEL_NAME],
    [LEVEL_NUMBER] AS [LEVEL NUMBER],
    [LEVEL_NAME_SQL_COLUMN_NAME] AS [NAME_COLUMN],
    [LEVEL_UNIQUE_NAME_SQL_COLUMN_NAME] AS [UNIQUE_NAME_COLUMN],
    [LEVEL_KEY_SQL_COLUMN_NAME] AS [KEY_COLUMN]
 FROM $system.MDSchema_levels
 WHERE CUBE_NAME  ='Adventure Works'
 AND LEVEL_ORIGIN=3
 order by [DIMENSION_UNIQUE_NAME]

1
2
3
4
5
6
7
--All Measures
SELECT [CATALOG_NAME] as [DATABASE],
    CUBE_NAME AS [CUBE],[MEASUREGROUP_NAME] AS [FOLDER],[MEASURE_CAPTION] AS [MEASURE],
    [MEASURE_IS_VISIBLE]
FROM $SYSTEM.MDSCHEMA_MEASURES
 WHERE CUBE_NAME  ='Adventure Works'
 ORDER BY [MEASUREGROUP_NAME]

1
2
3
4
5
6
7
--Calculated Measures/Members
SELECT [MEMBER_UNIQUE_NAME] AS [CALCULATED_MEASURE],
    [MEMBER_CAPTION] AS [CAPTION],
    [EXPRESSION]
FROM $system.MDSCHEMA_MEMBERS
WHERE CUBE_NAME ='Adventure Works'
AND [MEMBER_TYPE]=4 --MDMEMBER_TYPE_FORMULA

1
2
3
4
5
6
7
8
9
10
11
--Dimension Usage/Fact-Dimension Bus Matrix
SELECT  [MEASUREGROUP_NAME] AS [MEASUREGROUP],
        [MEASUREGROUP_CARDINALITY],
        [DIMENSION_UNIQUE_NAME] AS [DIM],
        [DIMENSION_GRANULARITY] AS [DIM_KEY],
        [DIMENSION_CARDINALITY],
        [DIMENSION_IS_VISIBLE] AS [IS_VISIBLE],
        [DIMENSION_IS_FACT_DIMENSION] AS [IS_FACT_DIM]
FROM $system.MDSCHEMA_MEASUREGROUP_DIMENSIONS
WHERE [CUBE_NAME] ='Adventure Works'
    AND [MEASUREGROUP_NAME] ='Internet Sales'

Posted by at No comments:
Subscribe to: Posts (Atom)