Automate Testing SSAS Tabular Fashions

In actual world SSAS Tabular initiatives, you have to run many various testing situations to show your buyer that the information in Tabular mannequin is right. If you’re operating a Tabular Mannequin on high of a correct information warehouse then your life could be a bit simpler than once you construct your semantic mannequin on high of an operational database. Nonetheless it could be nonetheless a reasonably time-consuming course of to run many check instances on Tabular Mannequin, then run related assessments on the information warehouse and examine the outcomes. So your check instances at all times have two sides, one facet is your supply database that may be a knowledge warehouse and the opposite facet is the Tabular Mannequin. There are various methods to check the system, you possibly can browse your Tabular Mannequin in Excel, connecting to your Knowledge Warehouse in Excel and create pivot tables then examine the information coming from Tabular Mannequin and the information coming from the Knowledge Warehouse. However, for what number of measures and dimensions you are able to do the above check in Excel?

The opposite means is to run DAX queries on Tabular Mannequin facet. In case your supply database is a SQL Server database, then you have to run T-SQL queries on the database facet then match the outcomes of each side to show the information in Tabular Mannequin is right.

On this publish I’d prefer to share with you a method to automate the DAX queries to be run on a Tabular mannequin.

Right away, that is going to be an extended publish, so you may make or take a cup of espresso whereas having fun with your studying.

Whereas I cannot cowl the opposite facet, the supply or the information warehouse facet, it’s value to automate that half too as it can save you heaps of occasions. I’m positive an identical course of might be developed in SQL Server facet, however, I depart that half for now. What I’m going to elucidate on this publish is only one of many potential methods to generate and run DAX queries and retailer the leads to SQL Server. Maybe it isn’t excellent, however, it’s a good place to begin. In case you have a greater concept it could be nice to share it with us within the feedback part under this publish.

• SQL Server Evaluation Companies Tabular 2016 and later (Compatibility Degree 1200 and better)
• An occasion of SQL Server
• SQL Server Administration Studio (SSMS)

What I’m going to elucidate could be very easy. I need to generate and run DAX queries and seize the outcomes. Step one is to get all measures and their related dimensions, then I slice all of the measures by all related dimensions and get the outcomes. On the finish I seize and retailer the leads to a SQL Server temp desk. Let’s take into consideration a easy situation:

• you’ve got only one measure, [Internet Sales], from ‘Web Gross sales’ desk
• The measure is expounded to only one dimension, “Date” dimension
• The “Date” dimension has solely 4 columns, Yr, Month, Yr-Month and Date
• you need to slice [Internet Sales] by Yr, Month, Yr-Month and Date

So you have to write 4 DAX queries as under:

EVALUATE
SUMMARIZE(
    'Web Gross sales'
    , Date'[Calendar Year]
    , "Web Gross sales", [Internet Total Sales]
)

EVALUATE
SUMMARIZE(
   'Web Gross sales'
   , 'Date'[Month Name]
   , "Web Gross sales", [Internet Total Sales]
)

EVALUATE
SUMMARIZE(
    'Web Gross sales'
   , 'Date'[Year-Month]
   , "Web Gross sales", [Internet Total Sales]
)

EVALUATE
SUMMARIZE(
     'Web Gross sales'
    , 'Date'[Date]
    , "Web Gross sales", [Internet Total Sales]
)

It’s straightforward isn’t it? However, wait. What if in case you have 10 measures associated to 4 dimension and every dimension has 10 columns? That sounds laborious doesn’t it? Effectively, in actual world situations you received’t slice all measures by all related dimensions, however, you continue to have to do loads. What we’re going to do is to generate and run the DAX queries and retailer the leads to a desk in SQL Server. How cool is that?

OK, that is the way it works…

• Making a Linked Server for SSAS Tabular occasion from SQL Server
• Producing DAX queries utilizing Tabular DMVs
• Operating the queries by means of Tabular mannequin and getting/storing the leads to a SQL Server temp desk

Creating Linked Server for SSAS Tabular (OLAP Service)

I’m not going to an excessive amount of particulars on this. You could find a variety of sources over the web on how you can create a Linked Server for an occasion of SSAS in SQL Server. Right here  is the way in which you possibly can create a Lined Server for SSAS from SSMS GUI:

• Open SSMS and connect with an occasion of SQL Server
• Increase “Server Objects”
• Proper click on “Linked Servers”
• Click on “New Linked Server…”

• Within the “New Linked Server” window, underneath “Basic” pane, enter a reputation for the linked server
• Be sure to choose “Microsoft OLE DB Supplier for Evaluation Companies”
• Enter the SSAS Server within the “Location” part
• Enter a desired database title within the “Catalog” part
• Click on “Safety” pane
• Click on “Add” button and choose a “Native Login” from the dropdown checklist
• Tick “Impersonate”
• Click on “Be made utilizing the login’s present safety context” then click on OK

Notice: Your safety setting could also be completely different from above.

Now that we bought our linked server sorted, let’s run some queries utilizing the linked server and ensure it’s working as anticipated. The question construction for an SSAS Linked Server is as under:

choose * from openquery([LINKED_SERVER_NAME], ‘DESTINATION QUERY LANGUAGE‘)

As a easy check I run the next question which certainly is passing the DAX question to the Tabular mannequin to run and retrieve the outcomes:

choose * 
from openquery([TABULAR2017], 'EVALUATE ''Date''')

The above question brings all values from the ‘Date’ desk from Tabular mannequin into SQL Server. The outcomes clearly might be saved in any type of regular SQL tables.

Let’s have a more in-depth take a look at the above question:

We now have to make use of OPENQUERY to move the DAX question by means of the Linked Server, run it in Tabular facet and get the outcomes. OPENQUERY  accepts DAX question in string format on the second argument. As we put desk names in a single quote in DAX then we now have so as to add an extra single quote to the desk title as proven within the screenshot under.

Earlier than we proceed let’s see what DAX question building we’d like and what the question sample we’re after. That is what we get if we run all of the DAX queries that talked about earlier on this article, in a batch, sure! We are able to run a number of DAX queries in a single run when utilizing Linked Server, which isn’t a shock. From SSMS viewpoint we’re simply operating a batch of SQL statements, aren’t we?

choose * 
from openquery([TABULAR2017]
                , 'EVALUATE SUMMARIZE(''Web Gross sales''
                                    , ''Date''[Calendar Year]
                                    , "Web Gross sales", [Internet Total Sales])')

choose * 
from openquery([TABULAR2017]
                , 'EVALUATE SUMMARIZE(''Web Gross sales''
                , ''Date''[Month Name]
                , "Web Gross sales", [Internet Total Sales])')

choose * 
from openquery([TABULAR2017]
                , 'EVALUATE SUMMARIZE(''Web Gross sales''
                , ''Date''[Year-Month]
                , "Web Gross sales", [Internet Total Sales])')

choose * 
from openquery([TABULAR2017]
                , 'EVALUATE SUMMARIZE(''Web Gross sales''
                , ''Date''[Date]
                , "Web Gross sales", [Internet Total Sales])')

It is a generic model of the above queries:

choose * from openquery([TABULAR2017], ‘EVALUATE SUMMARIZE(”FACT_TABLE”, ”RELATED_DIMENSION”[COLUMN_NAME], “MEASURE_GIVEN_NAME“, [MEASURE_NAME])’)

As you see we now have the next sample repeated in all queries:

• A “SELECT” assertion with “OPENQUERY” together with the linked server title
• Within the question argument we now have “EVALUATE SUMMARIZE(“

Then we now have:

• two single quotes
• FACT_TABLE: the desk that hosts the measure
• two single quotes and a comma
• one other two single quotes
• RELATED_DIMENSION: it is a dimension tables which has a associated to the measure
• once more two single quotes
• open bracket
• COLUMN_NAME: the column from the dimension that’s getting used to slice the measure
• shut bracket
• double quote, sure! this one is double qoute
• MEASURE_GIVEN_NAME: that is the title that we gave to the measure, like an alias
• double quote
• open bracket
• shut bracket
• an in depth parentheses
• a final single quote
• and eventually one other shut parentheses

Thus far we simply ran a DAX question from SQL Server by means of a Linked Server, within the subsequent few strains we’ll run DMVs to get the metadata we have to generate the DAX queries and run them from SQL Server by means of the Linked Server.  To generate the DAX question with the above sample we’d like the next 5 DMVs:

• DISCOVER_CALC_DEPENDENCY
• TMSCHEMA_TABLES
• TMSCHEMA_MEASURES
• TMSCHEMA_COLUMNS
• TMSCHEMA_RELATIONSHIPS

Learn extra about Dynamic Administration Views (DMVs) right here.

Whereas we don’t want all columns from the DMVs, I choose simply the columns we’d like and I additionally put some circumstances within the the place clause that I clarify the rationale for utilizing these circumstances afterward. However for now the queries that we’re after seem like under DMV queries:

choose [Object]
     , [Expression]
     , [Referenced_Table]
from $SYSTEM.DISCOVER_CALC_DEPENDENCY 
the place [Object_Type] = 'measure'

choose [Name]
     , [ID] 
from $SYSTEM.TMSCHEMA_TABLES 
the place not IsHidden

choose [TableID]
     , [Name]
     , [Expression] 
from $SYSTEM.TMSCHEMA_MEASURES 
the place not IsHidden 
       and [DataType] <> 2

choose [TableID]
     , [ExplicitName] 
from $SYSTEM.TMSCHEMA_COLUMNS 
the place not [IsHidden] 
        and [Type] <> 3  
        and never [IsDefaultImage]  
        and [ExplicitDataType] = 2  
        and [State] = 1

choose [FromTableID]
     , [ToTableID] 
from $SYSTEM.TMSCHEMA_RELATIONSHIPS 
the place IsActive

As you see I used some enumerations within the above queries as under:

• In TMSCHEMA_MEASURES, “DataType” reveals the information kind of the measure. The potential values are:

So including “DataType <> 2” to the the place clause when querying TMSCHEMA_MEASURES implies that we’re NOT fascinated by textual measures like once you outline a measure to point out the consumer title utilizing USERNAME() perform in DAX.

• In TMSCHEMA_COLUMNS, I used “Sort”, “ExplicitDataType” and “State” enumerations. The potential values for the above enumerations are:

So including “Sort <> 3  and ExplicitDataType = 2  and State = 1” to the the place clause when querying “TMSCHEMA_COLUMNS” implies that we’re solely within the columns which can be NOT inside row numbers and their information kind is string and they’re queryable and able to use.

The following step is to place the above queries within the OPENQUERY. The queries on the finish will seem like the under queries:

choose [Object] MeasureName 
     , [Expression] 
     , [Referenced_Table] ReferencedTable 
from openquery([TABULAR2017]
                , 'choose [Object] 
                        , [Expression] 
                        , [Referenced_Table]  
                   from $SYSTEM.DISCOVER_CALC_DEPENDENCY  
                   the place [Object_Type] = ''measure'''
                 )

choose [TableID] 
     , [Name] MeasureName 
     , [Expression] 
from openquery([TABULAR2017]
                ,  'choose [TableID] 
                         , [Name] 
                         , [Expression]    
                from $SYSTEM.TMSCHEMA_MEASURES  
                the place not [IsHidden]   
                      and [DataType] <> 2'
                )

choose [FromTableID]
    , [ToTableID] 
from openquery([TABULAR2017], 'choose [FromTableID]
                                    , [ToTableID] 
                                from $SYSTEM.TMSCHEMA_RELATIONSHIPS 
                                the place [IsActive]'
                )

choose [Name] TableName
    , [ID] 
from openquery([TABULAR2017], 'choose [Name]
                                    , [ID] 
                                from $SYSTEM.TMSCHEMA_TABLES 
                                the place not IsHidden'
                )

choose [TableID] 
    ,  [ExplicitName] RelatedColumn 
from openquery([TABULAR2017], 'choose [TableID]
                                    , [ExplicitName] 
                                from $SYSTEM.TMSCHEMA_COLUMNS 
                                the place not [IsHidden] 
                                        and [Type] <> 3 
                                        and never [IsDefaultImage] 
                                        and [ExplicitDataType] = 2 
                                        and [State] = 1'
                )

Now we need to be part of the above tables to get:

• Seen measures
• The bottom tables utilized in measures (referenced tables)
• Associated dimension to the measures
• Columns of these associated dimensions

Having the 4 above components we are able to dynamically generate the DAX question that we would like by becoming a member of the above 5 queries. I used CTE building to hitch the above queries:

;with 
    MeasureReferences as (
        choose [Object] MeasureName
            , [Expression]
            , [Referenced_Table] ReferencedTable 
        from openquery([TABULAR2017], 'choose [Object]
                                            , [Expression]
                                            , [Referenced_Table] 
                                        from $SYSTEM.DISCOVER_CALC_DEPENDENCY 
                                        the place [Object_Type] = ''measure'' '
                         ) 
        ) 
    , Measures as (
        choose [TableID]
            , [Name] MeasureName
            , [Expression] 
        from openquery([TABULAR2017], 'choose [TableID]
                                            , [Name]
                                            , [Expression] 
                                        from $SYSTEM.TMSCHEMA_MEASURES 
                                        the place not [IsHidden] and [DataType] <> 2'
                        )
        the place  charindex('SUM', ltrim(rtrim(forged([Expression] as varchar(max))))) = 1
        )
    , Relationships as (
        choose [FromTableID]
            , [ToTableID] 
        from openquery([TABULAR2017], 'choose [FromTableID]
                                            , [ToTableID] 
                                       from $SYSTEM.TMSCHEMA_RELATIONSHIPS 
                                       the place [IsActive]'
                       ) 
        )
    , Tables as (
        choose [Name] TableName
            , [ID] 
        from openquery([TABULAR2017], 'choose [Name]
                                            , [ID] 
                                        from $SYSTEM.TMSCHEMA_TABLES 
                                        the place not IsHidden'
                        )
        ) 
    , Columns as (
        choose [TableID] 
            ,  [ExplicitName] RelatedColumn 
        from openquery([TABULAR2017], 'choose [TableID]
                                            , [ExplicitName] 
                                       from $SYSTEM.TMSCHEMA_COLUMNS 
                                       the place not [IsHidden] 
                                             and [Type] <> 3 
                                             and never [IsDefaultImage] 
                                             and [ExplicitDataType] = 2 
                                             and [State] = 1'
                        )
        )

choose forged(mr.ReferencedTable as varchar(max)) TableName
     , forged(m.MeasureName as varchar(max)) MeasureName
     , forged((choose TableName 
                 from Tables 
                 the place [ID] = r.[ToTableID]
                 ) as varchar(max)
               ) RelatedDimension
     , forged(c.RelatedColumn as varchar(max)) RelatedColumn
from Measures m 
    be part of MeasureReferences mr on forged(mr.MeasureName as varchar(max)) = forged(m.MeasureName as varchar(max))
    be part of Relationships r on (choose ID 
                                 from Tables 
                                 the place forged(mr.ReferencedTable as varchar(max)) = forged(TableName as varchar(max))
                                 ) = r.[FromTableID]
    be part of Columns c on c.[TableID] = r.[ToTableID]

Let’s revisit the DAX question that we’re going to generate utilizing the outcomes of the above question.

EVALUATE 
SUMMARIZE(
       'Web Gross sales'
     , 'Date'[Calendar Year]
     , "Web Gross sales", [Internet Total Sales]
)

If we run the above question that is what we get:

That appears fantastic, however, once we generate DAX queries, we’ll robotically detect all associated dimensions to all measures and generate the question in order that it slices every measure by each single columns of associated dimensions. In that case our column names shall be completely different from what we see within the above screenshot for every question that we run.

So we now have to hard-code the column names which isn’t splendid. As well as, we’re going to insert that information in a SQL Server desk. With hardcoded column names then we may have some meaningless dimension values within the left column and a few measure values in the appropriate column. Subsequently, we now have to alter the above question just a little bit in order that it dynamically use the column names as values for 2 extra columns. So the results of the question brings 4 columns, the primary column (from the left) accommodates the column title together with its worth subsequent to it within the second column. The third column reveals the measure title and the fourth column reveals the measure values.

This appears to be like a lot better. I ran the next DAX question to get the above end result:

EVALUATE
SELECTCOLUMNS (
    SUMMARIZE (
        'Web Gross sales'
        , 'Date'[Calendar Year]
        , "Measure Title", "Web Gross sales"
        , "Worth", [Internet Total Sales]
    ),
    "Dimension Title", "'Date'[Calendar Year]"
    , "Dimension Worth", 'Date'[Calendar Year]
    , "Measure Title", "Web Whole Gross sales"
    , "Measure Worth", [Internet Total Sales]
)

The following step is to dynamically generate the latter DAX question utilizing the outcomes of DMVs operating by means of the Linked Server.

Within the following question we outline a neighborhood variable to generate the DAX question, then we use “Print” T-SQL perform to see the outcomes.

Notice: The “Print” perform has a limitation on displaying giant strings, so we’ll solely a portion of the outcomes. Learn extra about “Print” right here.

declare @SQL varchar(max) = null

;with 
    MeasureReferences as (
        choose [Object] MeasureName
            , [Expression]
            , [Referenced_Table] ReferencedTable 
        from openquery([TABULAR2017], 'choose [Object]
                                            , [Expression]
                                            , [Referenced_Table] 
                                        from $SYSTEM.DISCOVER_CALC_DEPENDENCY 
                                        the place [Object_Type] = ''measure'' '
                         ) 
        ) 
    , Measures as (
        choose [TableID]
            , [Name] MeasureName
            , [Expression] 
        from openquery([TABULAR2017], 'choose [TableID]
                                            , [Name]
                                            , [Expression] 
                                        from $SYSTEM.TMSCHEMA_MEASURES 
                                        the place not [IsHidden] and [DataType] <> 2'
                        )
        the place  charindex('SUM', ltrim(rtrim(forged([Expression] as varchar(max))))) = 1
        )
    , Relationships as (
        choose [FromTableID]
            , [ToTableID] 
        from openquery([TABULAR2017], 'choose [FromTableID]
                                            , [ToTableID] 
                                       from $SYSTEM.TMSCHEMA_RELATIONSHIPS 
                                       the place [IsActive]'
                       ) 
        )
    , Tables as (
        choose [Name] TableName
            , [ID] 
        from openquery([TABULAR2017], 'choose [Name]
                                            , [ID] 
                                        from $SYSTEM.TMSCHEMA_TABLES 
                                        the place not IsHidden'
                        )
        ) 
    , Columns as (
        choose [TableID] 
            ,  [ExplicitName] RelatedColumn 
        from openquery([TABULAR2017], 'choose [TableID]
                                            , [ExplicitName] 
                                       from $SYSTEM.TMSCHEMA_COLUMNS 
                                       the place not [IsHidden] 
                                             and [Type] <> 3 
                                             and never [IsDefaultImage] 
                                             and [ExplicitDataType] = 2 
                                             and [State] = 1'
                        )
        )

choose @SQL = ISNULL(@SQL, '') + 'choose * from openquery ([TABULAR2017], ''EVALUATE SELECTCOLUMNS(SUMMARIZE ('''''+[TableName]+''''', '''''+RelatedDimension+'''''['+RelatedColumn+'], "Measure Title", "'+MeasureName+'", "Worth", ['+MeasureName+']) , "Dimension Title", "'''''+RelatedDimension+'''''['+RelatedColumn+']", "Dimension Worth", '''''+RelatedDimension+'''''['+RelatedColumn+'], "Measure Title",  "'+MeasureName+'", "Measure Worth",  ['+MeasureName+'])'')
'
from (
choose forged(mr.ReferencedTable as varchar(max)) TableName
     , forged(m.MeasureName as varchar(max)) MeasureName
     , forged((choose TableName 
             from Tables 
             the place [ID] = r.[ToTableID]
             ) as varchar(max)
            ) RelatedDimension
     , forged(c.RelatedColumn as varchar(max)) RelatedColumn
from Measures m 
    be part of MeasureReferences mr on forged(mr.MeasureName as varchar(max)) = forged(m.MeasureName as varchar(max))
    be part of Relationships r on (choose ID 
                             from Tables 
                             the place forged(mr.ReferencedTable as varchar(max)) = forged(TableName as varchar(max))
                             ) = r.[FromTableID]
    be part of Columns c on c.[TableID] = r.[ToTableID]
    ) as tbl

Print @SQL

You possibly can copy/paste and run each question that’s generated to get the outcomes.

I manually ran the next question that’s copied from the outcomes:

choose * from openquery ([TABULAR2017], 'EVALUATE SELECTCOLUMNS(SUMMARIZE (''Web Gross sales'', ''Foreign money''[Currency Code], "Measure Title", "Web Whole Gross sales", "Worth", [Internet Total Sales]) , "Dimension Title", "''Foreign money''[Currency Code]", "Dimension Worth", ''Foreign money''[Currency Code], "Measure Title", "Web Whole Gross sales", "Measure Worth", [Internet Total Sales])')

The final step is to execute all generated queries and retailer the leads to a SQL Server Desk.

That is a simple one. We simply have to execute the dynamic SQL saved in @SQL native variable then we retailer the leads to a desk we create in SQL Server. For the sake of this publish I create a worldwide momentary desk in SQL Server. So the ultimate question will seem like this:

if object_id('tempdb..##Outcomes') isn't null drop desk ##Outcomes
create desk ##Outcomes (DimensionName varchar(max)
                     , DimensionValue varchar(max)
                     , MeasureName varchar(max)
                     , MeasureValue bigint
                     ) --Create a worldwide temp desk
declare @SQL varchar(max) = null

;with --Get measures, their associated dimensions and dimenion columns
    MeasureReferences as (
        choose [Object] MeasureName
            , [Expression]
            , [Referenced_Table] ReferencedTable 
        from openquery([TABULAR2017], 'choose [Object]
                                            , [Expression]
                                            , [Referenced_Table] 
                                        from $SYSTEM.DISCOVER_CALC_DEPENDENCY 
                                        the place [Object_Type] = ''measure'' '
                         ) 
        ) 
    , Measures as (
        choose [TableID]
            , [Name] MeasureName
            , [Expression] 
        from openquery([TABULAR2017], 'choose [TableID]
                                            , [Name]
                                            , [Expression] 
                                        from $SYSTEM.TMSCHEMA_MEASURES 
                                        the place not [IsHidden] and [DataType] <> 2'
                        )
        the place  charindex('SUM', ltrim(rtrim(forged([Expression] as varchar(max))))) = 1
        )
    , Relationships as (
        choose [FromTableID]
            , [ToTableID] 
        from openquery([TABULAR2017], 'choose [FromTableID]
                                            , [ToTableID] 
                                       from $SYSTEM.TMSCHEMA_RELATIONSHIPS 
                                       the place [IsActive]'
                       ) 
        )
    , Tables as (
        choose [Name] TableName
            , [ID] 
        from openquery([TABULAR2017], 'choose [Name]
                                            , [ID] 
                                        from $SYSTEM.TMSCHEMA_TABLES 
                                        the place not IsHidden'
                        )
        ) 
    , Columns as (
        choose [TableID] 
            ,  [ExplicitName] RelatedColumn 
        from openquery([TABULAR2017], 'choose [TableID]
                                            , [ExplicitName] 
                                       from $SYSTEM.TMSCHEMA_COLUMNS 
                                       the place not [IsHidden] 
                                             and [Type] <> 3 
                                             and never [IsDefaultImage] 
                                             and [ExplicitDataType] = 2 
                                             and [State] = 1'
                        )
        )

choose @SQL = ISNULL(@SQL, '') + 'choose * from openquery ([TABULAR2017], ''EVALUATE SELECTCOLUMNS(SUMMARIZE ('''''+[TableName]+''''', '''''+RelatedDimension+'''''['+RelatedColumn+'], "Measure Title", "'+MeasureName+'", "Worth", ['+MeasureName+']) , "Dimension Title", "'''''+RelatedDimension+'''''['+RelatedColumn+']", "Dimension Worth", '''''+RelatedDimension+'''''['+RelatedColumn+'], "Measure Title",  "'+MeasureName+'", "Measure Worth",  ['+MeasureName+'])'')
'
from (
choose forged(mr.ReferencedTable as varchar(max)) TableName
     , forged(m.MeasureName as varchar(max)) MeasureName
     , forged((choose TableName 
             from Tables 
             the place [ID] = r.[ToTableID]
              ) as varchar(max)
            ) RelatedDimension
     , forged(c.RelatedColumn as varchar(max)) RelatedColumn
from Measures m 
    be part of MeasureReferences mr on forged(mr.MeasureName as varchar(max)) = forged(m.MeasureName as varchar(max))
    be part of Relationships r on (choose ID 
                             from Tables 
                             the place forged(mr.ReferencedTable as varchar(max)) = forged(TableName as varchar(max))
                             ) = r.[FromTableID]
    be part of Columns c on c.[TableID] = r.[ToTableID]
    ) as tbl --Generate DAX queries dynamically
insert into ##Outcomes
execute (@SQL) --Execute the DAX queries

choose DimensionName, DimensionValue, MeasureName, FORMAT(MeasureValue, '#,#.#') MeasureValue
from ##Outcomes
the place MeasureValue <> 0 and MeasureValue isn't null

The above question ought to work on any Tabular Mannequin in case you setup the linked server accurately. Nonetheless, as you could seen, it would generate a variety of queries for all potential combos of slicing a measure with all columns from all associated dimensions. The queries will run towards an occasion of SSAS Tabular one after the other. Subsequently, if in case you have a variety of measures and dimensions, then for positive you’ll face efficiency points. Sadly that is the case in actual world initiatives. However, what you are able to do is to choose a few of the most dimensions which can be crucial ones to the enterprise and limit the above question to generate just some potentialities. The opposite level is that in a lot of instances you actually don’t want to check all combos of measures and all columns from associated dimensions. So you possibly can add some extra circumstances to the question to generate much less queries as desired. For example, within the above question, take a look at the “Measures” CTE. I put a situation in to get solely the measures that their expressions begin with “SUM”. The explanation for that’s that I needed to get solely the measures which can be principally summation base measures. In actual world initiatives you could have tons of of measures and operating the above question with none circumstances doesn’t sound fairly proper.

Q: Is the question particular to SSAS Tabular Mannequin?

A: Sure, it’s. However, you are able to do one thing related for SSAS Multidimensional.

Q: Is that this technique depending on the Tabular server title and/or database title?

A: So far as you setup the Linked Server accurately there should not be any points.

Q: Can we use this technique for testing Energy BI fashions?

A: Sure. You simply have to open your Energy BI Desktop (pbix) file and discover its native port quantity. Then you possibly can create a Linked Server to your Energy BI file and you then’re good to go. Learn extra about discovering Energy BI Desktop Native Port quantity right here.

Q: This technique is simply to get measures and their associated dimensions’ columns in SSAS Tabular facet. Eventually we now have to match the outcomes with the underlying information supply(s) like a knowledge warehouse. How ought to we check towards the supply methods.

A: As talked about earlier, we’re solely masking the SSAS Tabular facet. You are able to do one thing related in your information warehouse facet and examine the outcomes. One of many challenges could be discovering the column mappings between your information warehouse and the SSAS Tabular mannequin. There’s a “SourceColumn” out there within the “$SYSTEM.TMSCHEMA_COLUMNS” DMV to get the supply column names. That may be an excellent place to begin. Then you should utilize dynamic SQL to generate the queries and run towards your information warehouse, getting the leads to a SQL Server desk. The remainder could be straightforward to match the 2 outcomes.

Q: Is that this technique legitimate for Azure Evaluation Companies too?

A: Sure it’s.

Associated