Creando nuestro diccionario de medidas en SSAS

A menudo nuestros clientes nos hacen preguntas del tipo ¿A qué hace referencia esta medida?, ¿dónde puedo encontrar esta medida?, ¿de qué tipo es esta medida?. La mejor forma de parar este “acoso” y hacer que sean totalmente independientes es crear un Diccionario de Medidas.

Cuando realizamos un proyecto de SSAS debemos tener en cuenta tres puntos clave:

Que el valor de las medidas sea correcto. Si este punto falla no tenemos nada ya que el cliente no se fiará del proyecto y no mostrará interés alguno, ya que, estaremos mostrando datos sin sentido.
Que sea lo más escueto y sencillo de utilizar. No vale de nada tener 100 medidas si solo vamos a utilizar 10 y mucho menos si hay medidas duplicadas.
El cliente debe de entender a que hace referencia cada una de las medidas. Para ello debemos documentar todas las medidas en un diccionario de medidas.

Damos por hecho que tenemos un proyecto, en nuestro caso “SSAS_Aleson”, con algunas medidas ya creadas y desplegado en una instancia tabular.

Vamos a empezar a poner la descripción de cada medida, para ello, seleccionamos la medida y en propiedades rellenamos el campo descripción.

Una vez insertada la descripción de las medidas podemos proceder a crear un Linked Server para recoger la información que creamos necesaria de cada medida. Para ello abrimos SQL Server Management Studio y nos dirigimos a Server Objects – Linked Servers – New Linked Server…

Rellenamos los campos con la información correspondiente:

Linked Server: nombre que tendrá el Linked Server una vez creado.
Provider: seleccionamos Microsoft OLE DB Provider for Analysis Services 12.0
Product name: MSOLAP.4
Data source: nombre de la instancia tabular.
Catalog: nombre de nuestro proyecto de SSAS.

En la pestaña Security seleccionamos la opción que creamos conveniente. En nuestro caso y dado que el usuario de dominio tiene permisos en ambos servidores, seleccionaremos “Be made using the login’s current security context”.

Para probar que todo funciona correctamente, ejecutad la siguiente sentencia en la que debéis substituir [SSAS_ALESON] por el nombre que le hayáis dado al Linked Server y en el WHERE si habéis renombrado el model del proyecto también debéis modificarlo.

SELECT *
FROM OPENQUERY([SSAS_ALESON],'SELECT [catalog_name] as [DATABASE]  ,dimension_name AS [DIMENSION]
     , [description] AS [DESCRIPTION], dimension_is_visible AS [HIDDEN]
     FROM $system.MDSchema_Dimensions
     WHERE CUBE_NAME  =''Model''')

Si la sentencia devuelve las dimensiones que tenemos creadas en el proyecto con su descripción, significa que tenemos creado el linked server correctamente, por lo que podemos proceder a crear las vistas para visualizar toda la información que creamos conveniente.

Con el siguiente script crearemos las vistas con la información básica del proyecto. Al igual que en la sentencia anterior, será necesario substituir [SSAS_ALESON] y el model si lo habéis renombrado en el proyecto.

/****** Object:  View [dbo].[COLUMNS] ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[COLUMNS]
AS
     SELECT *
     FROM OPENQUERY([SSAS_ALESON],'select [CATALOG_NAME] as [DATABASE]
,[DIMENSION_UNIQUE_NAME] AS [DIMENSION]
,[LEVEL_CAPTION] AS [COLUMN]
,[DESCRIPTION] AS [DESCRIPTION]
,[LEVEL_IS_VISIBLE] AS [HIDDEN]
 ,[LEVEL_DBTYPE] AS [DATA_TYPE]
 from $SYSTEM.MDSCHEMA_LEVELS where level_type=0 AND CUBE_NAME=''Model''')
GO
/****** Object:  View [dbo].[MEASURES] ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[MEASURES]
AS
     SELECT *
     FROM OPENQUERY([SSAS_ALESON],' SELECT [CATALOG_NAME] AS [DATABASE],
            [MEASUREGROUP_NAME] AS [DIMENSION],
            [MEASURE_CAPTION] AS [MEASURE],
            [MEASURE_IS_VISIBLE] AS [HIDDEN],
            [DATA_TYPE],
		  [DESCRIPTION] ,
            [EXPRESSION]
  FROM $SYSTEM.MDSCHEMA_MEASURES
     WHERE CUBE_NAME = ''Model''
           AND measure_aggregator = 0')
GO
/****** Object:  View [dbo].[TYPES] ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[TYPES]
AS
     WITH cte
          AS (SELECT *
              FROM OPENQUERY([SSAS_ALESON],'select * from $SYSTEM.DBSCHEMA_PROVIDER_TYPES'))
          SELECT CAST(DATA_TYPE AS INT) AS DATA_TYPE
                ,CAST(TYPE_NAME AS NVARCHAR(50)) AS TYPE_NAME
          FROM cte
          UNION
          SELECT 20
                ,'INT'
          UNION
          SELECT 11
                ,'TRUE/FALSE'
GO
/****** Object:  View [dbo].[COLUMN_AND_MEASURES] ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
/****** Script for SelectTopNRows command from SSMS  ******/

CREATE VIEW [dbo].[COLUMN_AND_MEASURES]
AS
     SELECT a.*
           ,t.[TYPE_NAME]
     FROM
     (
       SELECT 'Measure' AS TIPO
             ,CAST([DATABASE] AS    NVARCHAR(50)) AS [DATABASE]
             ,REPLACE(REPLACE(CAST([DIMENSION] AS NVARCHAR(50)),'[',''),']','') AS DIMENSION
             ,CAST([MEASURE] AS    NVARCHAR(50)) AS NAME
             ,[HIDDEN]
             ,[DATA_TYPE]
             ,CAST([DESCRIPTION] AS    NVARCHAR(255)) AS [DESCRIPTION]
             ,EXPRESSION
       FROM [dbo].[MEASURES]
       UNION ALL
       SELECT 'Column'
             ,CAST([DATABASE] AS    NVARCHAR(50)) AS [DATABASE]
             ,REPLACE(REPLACE(CAST([DIMENSION] AS NVARCHAR(50)),'[',''),']','') AS DIMENSION
             ,CAST([COLUMN] AS    NVARCHAR(50)) AS NAME
             ,[HIDDEN]
             ,[DATA_TYPE]
             ,CAST([DESCRIPTION] AS    NVARCHAR(255)) AS [DESCRIPTION]
             ,NULL
       FROM [dbo].[COLUMNS]
     ) AS a
     LEFT JOIN [dbo].[TYPES] AS t ON t.[DATA_TYPE] = a.[DATA_TYPE]
     WHERE a.DIMENSION <> 'Measures'
GO
/****** Object:  View [dbo].[DIMENSIONS] ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[DIMENSIONS]
AS
     SELECT *
     FROM OPENQUERY([SSAS_ALESON],'SELECT [catalog_name] as [DATABASE]  ,dimension_name AS [DIMENSION]
, [description] AS [DESCRIPTION], dimension_is_visible AS [HIDDEN]
FROM $system.MDSchema_Dimensions
WHERE CUBE_NAME  =''Model''')
GO

Estas son las vistas que nos debería haber creado.

La vista en la que se unifica casi toda la información es dbo.COLUMN_AND_MEASURES, en la que podemos ver de izquierda a derecha si es una medida o una columna, en que proyecto está situada, a que dimensión pertenece, su nombre, si esta oculta, el id del tipo de dato…