博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
SSAS: Using DMV Queries to get Cube Metadata
阅读量:4842 次
发布时间:2019-06-11

本文共 20061 字,大约阅读时间需要 66 分钟。

References

https://msdn.microsoft.com/en-us/library/ms126079.aspx

 

Schema Rowset1

Description

Describes the instances on the server.

Enumerates a list of words reserved by the provider.

Describes the actions that may be available to the client application.

Describes the structure of cubes within a database.

Describes the shared and private dimensions within a database.

Describes the functions that are available to client applications connected to the database.

Describes each hierarchy that is contained in a particular dimension.

Describes the data sources defined within the database.

Describes the key performance indicators (KPIs) within a database.

Describes each level within a particular hierarchy.

Enumerates the dimensions of measure groups.

Describes the measure groups within a database.

Describes each measure within in a cube.

Describes the members within a database.

Describes the properties of members within in a database.

Describes any sets that are currently defined in a database, including session-scoped sets.

  

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Articles posted by Benny Austin, check below URL for more details

https://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/

--All Cubes in databaseSELECT [CATALOG_NAME] AS [DATABASE],CUBE_CAPTION AS [CUBE/PERSPECTIVE],BASE_CUBE_NAMEFROM $system.MDSchema_CubesWHERE CUBE_SOURCE=1 --All dimensions in CubeSELECT [CATALOG_NAME] as [DATABASE],CUBE_NAME AS [CUBE],DIMENSION_CAPTION AS [DIMENSION] FROM $system.MDSchema_DimensionsWHERE CUBE_NAME  ='Adventure Works'AND DIMENSION_CAPTION <> 'Measures'ORDER BY DIMENSION_CAPTION --All AttributesSELECT [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_hierarchiesWHERE CUBE_NAME  ='Adventure Works'AND HIERARCHY_ORIGIN=2ORDER BY [DIMENSION_UNIQUE_NAME] --All Attributes with key and name columnsSELECT [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_levelsWHERE CUBE_NAME  ='Adventure Works'AND level_origin=2AND LEVEL_NAME <> '(All)'order by [DIMENSION_UNIQUE_NAME] --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_hierarchiesWHERE CUBE_NAME  ='Adventure Works'and HIERARCHY_ORIGIN=1ORDER BY [DIMENSION_UNIQUE_NAME]--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_hierarchiesWHERE CUBE_NAME  ='Adventure Works'AND HIERARCHY_ORIGIN=3ORDER BY [DIMENSION_UNIQUE_NAME] --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] --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] --All MeasuresSELECT [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] --Calculated Measures/MembersSELECT [MEMBER_UNIQUE_NAME] AS [CALCULATED_MEASURE],    [MEMBER_CAPTION] AS [CAPTION],    [EXPRESSION]FROM $system.MDSCHEMA_MEMBERSWHERE CUBE_NAME ='Adventure Works'AND [MEMBER_TYPE]=4 --MDMEMBER_TYPE_FORMULA --Dimension Usage/Fact-Dimension Bus MatrixSELECT  [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_DIMENSIONSWHERE [CUBE_NAME] ='Adventure Works'    AND [MEASUREGROUP_NAME] ='Internet Sales'

 

DMV may not be able to report on partition count. Try AMO instead. The following AMO code will return partition count

using System;using System.Text;using Microsoft.AnalysisServices;namespace AmoPartitionCount{class Program{static void Main(string[] args){Server svr = new Server();svr.Connect(“localhost”);Database db =svr.Databases.FindByName(“AdventureWorks”);Cube cbe = db.Cubes.FindByName(“Adventure Works”);MeasureGroup mg = cbe.MeasureGroups.FindByName(“Internet Sales”);int count =mg.Partitions.Count;Console.WriteLine(count);}}}

 

Articles posted by Alex Whittles

http://www.purplefrogsystems.com/blog/2010/09/olap-cube-documentation-in-ssrs-part-1/

We can even enhance it with DISTINCT and WHERE clauses, although they are more restricted than basic SQL. One of the main limitations is the lack of a JOIN operator. A number of the queries that I’ll perform below need to use JOIN, so to get around this I wrap up each query in an SQL OPENROWSET command, executed against a SQL database with a linked server to the cube. This enables me to perform JOINs using queries such as

SELECT *FROM OPENQUERY(CubeLinkedServer,   'SELECT *    FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS') mgdINNER JOIN OPENQUERY(CubeLinkedServer,   'SELECT *    FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS') mgON mgd.XXX = mg.XXX

 

etc.

I’m therefore going to create a number of stored procs to wrap up this functionality, the SSRS reports can then just call the procs.

Within BIDS, every item (cube, measure group, measure, dimension, attribute, hierarchy, KPI, etc.) has a description in the properties pane which is a multi-line free text property. These are exposed by the DMVs, so I’m going to make use of them and bring them out in the reports. This allows you to create the descriptions within BIDS as you’re developing the cube, meaning they’re version controlled and always in sync with the code.

I should also point out that I’m using SQL Server 2008 R2. All of the queries below will work with SQL 2008, but I want to use the spatial report functionality of SSRS 2008 R2 to generate dynamic star schema visualisations, which is only supported in R2.

In this post I’ll script out the stored procedures used as the basis of the documentation. In my next post I’ll put these into SSRS reports.

Lets get started.

Firstly we need to create our linked server. This script will create a linked server called CubeLinkedServer pointing to the Adventure Works DW 2008R2 OLAP database on the local server.

EXEC master.dbo.sp_addlinkedserver   @server = N'CubeLinkedServer',   @srvproduct=N'MSOLAP',   @provider=N'MSOLAP',   @datasrc=N'(local)',   @catalog=N'Adventure Works DW 2008R2'

 

You’ll have to set up the security according to your requirements. So now lets start creating the source procs.

The first proc lists all of the cubes. The MDSCHEMA_CUBES DMV returns not only cubes, but also dimensions, I’m filtering it to only return cubes by specifying CUBE_SOURCE=1.

CREATE PROCEDURE [dbo].[upCubeDocCubes]  (@Catalog       VARCHAR(255) = NULL  )AS  SELECT *  FROM OPENQUERY(CubeLinkedServer,    'SELECT *     FROM $SYSTEM.MDSCHEMA_CUBES     WHERE CUBE_SOURCE = 1')  WHERE CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog    OR @Catalog IS NULLGO

 

The next proc returns all measure groups found within a specified cube.

CREATE PROCEDURE [dbo].[upCubeDocMeasureGroupsInCube]  (@Catalog       VARCHAR(255)  ,@Cube          VARCHAR(255)  )AS  SELECT *  FROM OPENQUERY(CubeLinkedServer,    'SELECT *     FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS ')  WHERE CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog    AND CAST([CUBE_NAME] AS VARCHAR(255))    = @CubeGO

 

This next proc returns a list of measures within a specified measure group.

CREATE PROCEDURE [dbo].[upCubeDocMeasuresInMeasureGroup]  (@Catalog       VARCHAR(255)  ,@Cube          VARCHAR(255)  ,@MeasureGroup  VARCHAR(255)  )ASSELECT * FROM OPENQUERY(CubeLinkedServer,  'SELECT *   FROM $SYSTEM.MDSCHEMA_MEASURES     WHERE [MEASURE_IS_VISIBLE]')   WHERE CAST([CATALOG_NAME] AS VARCHAR(255))      = @Catalog     AND CAST([CUBE_NAME] AS VARCHAR(255))         = @Cube     AND CAST([MEASUREGROUP_NAME] AS VARCHAR(255)) = @MeasureGroupGO

 

The following proc queries all dimensions available within a specified cube. I’m filtering using the DIMENSION_IS_VISIBLE column to only show visible dimensions.

CREATE PROCEDURE [dbo].[upCubeDocDimensionsInCube]  (@Catalog       VARCHAR(255)  ,@Cube          VARCHAR(255)  )ASSELECT * FROM OPENQUERY(CubeLinkedServer,  'SELECT *   FROM $SYSTEM.MDSCHEMA_DIMENSIONS     WHERE [DIMENSION_IS_VISIBLE]')   WHERE CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog     AND CAST([CUBE_NAME] AS VARCHAR(255))    = @CubeGO

 

Then we can query all available attributes within a dimension. This DMV returns a bitmask field (LEVEL_ORIGIN) which defines whether the attribute is a key, attribute or hierarchy. I’m using bitwise AND (&) to split this into three seperate fields for ease of use. I’m also filtering out invisible attributes, as well as those with a level of 0. Level 0 is the [All] member of any attribute, which we can ignore for this purpose.

CREATE PROCEDURE [dbo].[upCubeDocAttributesInDimension]  (@Catalog       VARCHAR(255)  ,@Cube          VARCHAR(255)  ,@Dimension  VARCHAR(255)  )AS  SELECT *    , CASE WHEN CAST([LEVEL_ORIGIN] AS INT) & 1 = 1        THEN 1 ELSE 0 END AS IsHierarchy    , CASE WHEN CAST([LEVEL_ORIGIN] AS INT) & 2 = 2        THEN 1 ELSE 0 END AS IsAttribute    , CASE WHEN CAST([LEVEL_ORIGIN] AS INT) & 4 = 4        THEN 1 ELSE 0 END AS IsKey  FROM OPENQUERY(CubeLinkedServer,    'SELECT *     FROM $SYSTEM.MDSCHEMA_LEVELS     WHERE [LEVEL_NUMBER]>0       AND [LEVEL_IS_VISIBLE]')  WHERE CAST([CATALOG_NAME] AS VARCHAR(255))          = @Catalog    AND CAST([CUBE_NAME] AS VARCHAR(255))             = @Cube    AND CAST([DIMENSION_UNIQUE_NAME] AS VARCHAR(255)) = @DimensionGO

 

The next proc returns measure groups with their associated dimensions. We have to join two DMVs together in order to get the description columns of both the dimension and measure group.

CREATE PROCEDURE [dbo].[upCubeDocMeasureGroupsForDimension]    (@Catalog       VARCHAR(255)    ,@Cube          VARCHAR(255)    ,@Dimension     VARCHAR(255)    )AS  SELECT    mgd.*    , m.[DESCRIPTION]  FROM OPENQUERY(CubeLinkedServer,    'SELECT       [CATALOG_NAME]       , [CUBE_NAME]       , [MEASUREGROUP_NAME]       , [MEASUREGROUP_CARDINALITY]       , [DIMENSION_UNIQUE_NAME]     FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS       WHERE [DIMENSION_IS_VISIBLE]') mgd   INNER JOIN OPENQUERY(CubeLinkedServer,     'SELECT       [CATALOG_NAME]       ,[CUBE_NAME]       ,[MEASUREGROUP_NAME]       ,[DESCRIPTION]     FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS') mg        ON  CAST(mgd.[CATALOG_NAME] AS VARCHAR(255))           = CAST(mg.[CATALOG_NAME] AS VARCHAR(255))        AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255))           = CAST(mg.[CUBE_NAME] AS VARCHAR(255))        AND CAST(mgd.[MEASUREGROUP_NAME] AS VARCHAR(255))           = CAST(mg.[MEASUREGROUP_NAME] AS VARCHAR(255))  WHERE CAST(mgd.[CATALOG_NAME] AS VARCHAR(255))            = @Catalog    AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255))               = @Cube    AND CAST(mgd.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255))   = @DimensionGO

 

The next proc is similar to the above, but the opposite way around. It returns all dimensions that are related to a measure group.

CREATE PROCEDURE [dbo].[upCubeDocDimensionsForMeasureGroup]  (@Catalog       VARCHAR(255)  ,@Cube          VARCHAR(255)  ,@MeasureGroup  VARCHAR(255)  )AS  SELECT    mgd.*    , d.[DESCRIPTION]  FROM OPENQUERY(CubeLinkedServer,    'SELECT        [CATALOG_NAME]       ,[CUBE_NAME]       ,[MEASUREGROUP_NAME]       ,[MEASUREGROUP_CARDINALITY]       ,[DIMENSION_UNIQUE_NAME]       ,[DIMENSION_CARDINALITY]       ,[DIMENSION_IS_VISIBLE]       ,[DIMENSION_IS_FACT_DIMENSION]       ,[DIMENSION_GRANULARITY]     FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS       WHERE [DIMENSION_IS_VISIBLE]') mgd  INNER JOIN OPENQUERY(CubeLinkedServer,    'SELECT       [CATALOG_NAME]       ,[CUBE_NAME]       ,[DIMENSION_UNIQUE_NAME]       ,[DESCRIPTION]     FROM $SYSTEM.MDSCHEMA_DIMENSIONS       WHERE [DIMENSION_IS_VISIBLE]') d   ON  CAST(mgd.[CATALOG_NAME] AS VARCHAR(255))       = CAST(d.[CATALOG_NAME] AS VARCHAR(255))   AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255))       = CAST(d.[CUBE_NAME] AS VARCHAR(255))   AND CAST(mgd.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255))       = CAST(d.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255))  WHERE  CAST(mgd.[CATALOG_NAME] AS VARCHAR(255))        = @Catalog     AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255))           = @Cube     AND CAST(mgd.[MEASUREGROUP_NAME] AS VARCHAR(255))   = @MeasureGroupGO

 

The next proc builds a BUS matrix, joining every dimension to its related measure groups. Later we’ll use the SSRS tablix control to pivot this into matrix form.

CREATE PROCEDURE [dbo].[upCubeDocBUSMatrix]    (@Catalog       VARCHAR(255),     @Cube          VARCHAR(255)    )AS  SELECT     bus.[CATALOG_NAME]    ,bus.[CUBE_NAME]    ,bus.[MEASUREGROUP_NAME]    ,bus.[MEASUREGROUP_CARDINALITY]    ,bus.[DIMENSION_UNIQUE_NAME]    ,bus.[DIMENSION_CARDINALITY]    ,bus.[DIMENSION_IS_FACT_DIMENSION]    ,bus.[DIMENSION_GRANULARITY]    ,dim.[DIMENSION_MASTER_NAME]    ,1 AS Relationship  FROM    OPENQUERY(CubeLinkedServer,      'SELECT        [CATALOG_NAME]        ,[CUBE_NAME]        ,[MEASUREGROUP_NAME]        ,[MEASUREGROUP_CARDINALITY]        ,[DIMENSION_UNIQUE_NAME]        ,[DIMENSION_CARDINALITY]        ,[DIMENSION_IS_FACT_DIMENSION]        ,[DIMENSION_GRANULARITY]       FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS        WHERE [DIMENSION_IS_VISIBLE]') bus    INNER JOIN OPENQUERY(CubeLinkedServer,      'SELECT        [CATALOG_NAME]        ,[CUBE_NAME]        ,[DIMENSION_UNIQUE_NAME]        ,[DIMENSION_MASTER_NAME]       FROM $SYSTEM.MDSCHEMA_DIMENSIONS') dim    ON CAST(bus.[CATALOG_NAME] AS VARCHAR(255))     = CAST(dim.[CATALOG_NAME] AS VARCHAR(255))    AND CAST(bus.[CUBE_NAME] AS VARCHAR(255))     = CAST(dim.[CUBE_NAME] AS VARCHAR(255))    AND CAST(bus.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255))     = CAST(dim.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255))  WHERE  CAST(bus.[CATALOG_NAME] AS VARCHAR(255)) = @Catalog     AND CAST(bus.[CUBE_NAME] AS VARCHAR(255)) = @CubeGO

 

Next, in order to make it easier for users to find items within the cube, I’ve created a searching proc which will scour a number of the DMVs for anything containing the search term.

CREATE PROCEDURE [dbo].[upCubeDocSearch]    (@Search        VARCHAR(255)    ,@Catalog       VARCHAR(255)=NULL    ,@Cube          VARCHAR(255)=NULL    )AS  WITH MetaData AS  (   --Cubes    SELECT CAST('Cube' AS VARCHAR(20))            AS [Type]      , CAST(CATALOG_NAME AS VARCHAR(255))     AS [Catalog]      , CAST(CUBE_NAME AS VARCHAR(255))           AS [Cube]      , CAST(CUBE_NAME AS VARCHAR(255))           AS [Name]      , CAST(DESCRIPTION AS VARCHAR(4000)) AS [Description]      , CAST(CUBE_NAME AS VARCHAR(255))           AS [Link]    FROM OPENQUERY(CubeLinkedServer,      'SELECT [CATALOG_NAME], [CUBE_NAME], [DESCRIPTION]       FROM $SYSTEM.MDSCHEMA_CUBES       WHERE CUBE_SOURCE = 1')    WHERE  (CAST([CATALOG_NAME] AS VARCHAR(255))       = @Catalog OR @Catalog IS NULL)    UNION ALL   --Dimensions    SELECT CAST('Dimension' AS VARCHAR(20))         AS [Type]      , CAST(CATALOG_NAME AS VARCHAR(255))       AS [Catalog]      , CAST(CUBE_NAME AS VARCHAR(255))             AS [Cube]      , CAST(DIMENSION_NAME AS VARCHAR(255))        AS [Name]      , CAST(DESCRIPTION AS VARCHAR(4000))   AS [Description]      , CAST(DIMENSION_UNIQUE_NAME AS VARCHAR(255)) AS [Link]    FROM OPENQUERY(CubeLinkedServer,      'SELECT [CATALOG_NAME], [CUBE_NAME]          , [DIMENSION_NAME], [DESCRIPTION]          , [DIMENSION_UNIQUE_NAME]       FROM $SYSTEM.MDSCHEMA_DIMENSIONS         WHERE [DIMENSION_IS_VISIBLE]')    WHERE  (CAST([CATALOG_NAME] AS VARCHAR(255))        = @Catalog OR @Catalog IS NULL)      AND (CAST([CUBE_NAME] AS VARCHAR(255))        = @Cube OR @Cube IS NULL)      AND LEFT(CAST(CUBE_NAME AS VARCHAR(255)),1)        <>'$' --Filter out dimensions not in a cube    UNION ALL   --Attributes    SELECT CAST('Attribute' AS VARCHAR(20))         AS [Type]      , CAST(CATALOG_NAME AS VARCHAR(255))       AS [Catalog]      , CAST(CUBE_NAME AS VARCHAR(255))             AS [Cube]      , CAST(LEVEL_CAPTION AS VARCHAR(255))         AS [Name]      , CAST(DESCRIPTION AS VARCHAR(4000))   AS [Description]      , CAST(DIMENSION_UNIQUE_NAME AS VARCHAR(255)) AS [Link]    FROM OPENQUERY(CubeLinkedServer,      'SELECT [CATALOG_NAME], [CUBE_NAME]         , [LEVEL_CAPTION], [DESCRIPTION],         , [DIMENSION_UNIQUE_NAME]       FROM $SYSTEM.MDSCHEMA_LEVELS       WHERE [LEVEL_NUMBER]>0         AND [LEVEL_IS_VISIBLE]')    WHERE  (CAST([CATALOG_NAME] AS VARCHAR(255))         = @Catalog OR @Catalog IS NULL)      AND (CAST([CUBE_NAME] AS VARCHAR(255))         = @Cube OR @Cube IS NULL)      AND LEFT(CAST(CUBE_NAME AS VARCHAR(255)),1)         <>'$' --Filter out dimensions not in a cube    UNION ALL   --Measure Groups    SELECT CAST('Measure Group' AS VARCHAR(20))   AS [Type]      , CAST(CATALOG_NAME AS VARCHAR(255))     AS [Catalog]      , CAST(CUBE_NAME AS VARCHAR(255))           AS [Cube]      , CAST(MEASUREGROUP_NAME AS VARCHAR(255))   AS [Name]      , CAST(DESCRIPTION AS VARCHAR(4000)) AS [Description]      , CAST(MEASUREGROUP_NAME AS VARCHAR(255))   AS [Link]    FROM OPENQUERY(CubeLinkedServer,       'SELECT [CATALOG_NAME], [CUBE_NAME]          , [MEASUREGROUP_NAME],          , [DESCRIPTION]        FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS')    WHERE  (CAST([CATALOG_NAME] AS VARCHAR(255))       = @Catalog OR @Catalog IS NULL)     AND (CAST([CUBE_NAME] AS VARCHAR(255))       = @Cube OR @Cube IS NULL)     AND LEFT(CAST(CUBE_NAME AS VARCHAR(255)),1)       <>'$' --Filter out dimensions not in a cube    UNION ALL   --Measures    SELECT CAST('Measure' AS VARCHAR(20))         AS [Type]      , CAST(CATALOG_NAME AS VARCHAR(255))     AS [Catalog]      , CAST(CUBE_NAME AS VARCHAR(255))           AS [Cube]      , CAST(MEASURE_NAME AS VARCHAR(255))        AS [Name]      , CAST(DESCRIPTION AS VARCHAR(4000)) AS [Description]      , CAST(MEASUREGROUP_NAME AS VARCHAR(255))   AS [Link]    FROM OPENQUERY(CubeLinkedServer,      'SELECT [CATALOG_NAME], [CUBE_NAME]         , [MEASURE_NAME], [DESCRIPTION],         , [MEASUREGROUP_NAME]       FROM $SYSTEM.MDSCHEMA_MEASURES          WHERE [MEASURE_IS_VISIBLE]')    WHERE  (CAST([CATALOG_NAME] AS VARCHAR(255))          = @Catalog OR @Catalog IS NULL)      AND (CAST([CUBE_NAME] AS VARCHAR(255))          = @Cube OR @Cube IS NULL)      AND LEFT(CAST(CUBE_NAME AS VARCHAR(255)),1)          <>'$' --Filter out dimensions not in a cube    )    SELECT *    FROM MetaData    WHERE @Search<>''        AND ([Name] LIKE '%' + @Search + '%'          OR [Description] LIKE '%' + @Search + '%'        )GO

 

转载于:https://www.cnblogs.com/hopecho/p/4488331.html

你可能感兴趣的文章
正则表达式与unicode
查看>>
abp(net core)+easyui+efcore实现仓储管理系统——ABP总体介绍(一)
查看>>
div水平居中与垂直居中的方法【摘自美浩工作室官方博客 】
查看>>
UITableView 滚动条
查看>>
Android已有的原生Camera框架中加入自己的API的实现方案。
查看>>
Learn python the ninth day
查看>>
Debian+Django+uWsgi+nginx+mysql+celery
查看>>
docker 基本操作
查看>>
无缝滚动的float属性
查看>>
价值观作业
查看>>
char , unsigned char 和 signed char 区别
查看>>
挂起布局逻辑与恢复布局逻辑
查看>>
back to back
查看>>
Linux/Unix笔记本
查看>>
博弈问题之SG函数博弈小结
查看>>
30天敏捷生活(12): 整理你的空间
查看>>
纯虚函数
查看>>
线程安全总结
查看>>
Java获取正在执行的函数名
查看>>
vue 运行npm run dev报错
查看>>