Search code examples
ssasmdxolapcubessas-tabular

Retrieve only existing combinations of dimensions

Here is a basic model with 2 tables in a 1-1 relationship:

CREATE TABLE CountriesNames(countryId INT, countryName VARCHAR(50));
INSERT INTO CountriesNames VALUES
    (1, 'France'),
    (2, 'Germany'),
    (3, 'Italy');

CREATE TABLE CountriesCodes(countryId INT, countryCode VARCHAR(50));
INSERT INTO CountriesCodes VALUES
    (1, 'FR'),
    (2, 'DE'),
    (3, 'IT');

Now creating an SSAS Tabular model from it:

{
  "create": {
    "database": {
      "name": "TEST_CUBE",
      "compatibilityLevel": 1500,
      "model": {
        "name": "Sales",
        "culture": "en-US",
        "dataSources": [
          {
            "name": "DS",
            "connectionString": "Provider=SQL Server Native Client 11.0;Data Source=.\\MSSQLSERVER01;Integrated Security=SSPI;Initial Catalog=TEST_CUBE",
            "impersonationMode": "impersonateServiceAccount"
          }
        ],
        "tables": [
          {
            "name": "Country Name",
            "columns": [
              {
                "name": "Country ID",
                "dataType": "int64",
                "isHidden": true,
                "sourceColumn": "countryId"
              },
              {
                "name": "Country Name",
                "dataType": "string",
                "sourceColumn": "countryName"
              }
            ],
            "partitions": [
              {
                "name": "Partition",
                "mode": "import",
                "source": {
                  "type": "query",
                  "query": "SELECT * FROM CountriesNames",
                  "dataSource": "DS"
                }
              }
            ]
          },
          {
            "name": "Country Code",
            "columns": [
              {
                "name": "Country ID",
                "dataType": "int64",
                "isHidden": true,
                "sourceColumn": "countryId"
              },
              {
                "name": "Country Code",
                "dataType": "string",
                "sourceColumn": "countryCode"
              }
            ],
            "partitions": [
              {
                "name": "Partition",
                "mode": "import",
                "source": {
                  "type": "query",
                  "query": "SELECT * FROM CountriesCodes",
                  "dataSource": "DS"
                }
              }
            ]
          }
        ],
        "relationships": [
          {
            "name": "Relation",
            "fromTable": "Country Name",
            "fromColumn": "Country ID",
            "toTable": "Country Code",
            "toColumn": "Country ID"
          }
        ]
      }
    }
  }
}

How to query the model with MDX to get only the 3 possible combinations of ("Country Name", "Country Code") ?

The same result as this SQL query:

SELECT
    countryName,
    countryCode
FROM
    CountriesNames cn JOIN
    CountriesCodes cc ON cc.countryId = cn.countryId

Which gives:

Country Name | Country Code
---------------------------
France       | FR
Germany      | DE
Italy        | IT

Whereas this naive MDX query returns all the 9 combinations:

SELECT
    ([Country Name].Children, [Country Code].Children) ON 0
FROM Sales

I suspect a measure is mandatory, but if so I wonder why, probably missing something obvious.

Solution

  • You have right, a measure is mandatory; This is because the engine works this way - decision on engine design level (as DAX and MDX): If you don't select measure, then Tablular generates underhood two separate queries without joins (like this one) - and generate crossjoin:

    SET DC_KIND="AUTO";
SELECT
'CountriesCodes'[countryCode]
FROM 'CountriesCodes';

SET DC_KIND="AUTO";
SELECT
'CountriesNames'[countryName]
FROM 'CountriesNames';

    The simplest way to return a correct/existing combination is measure countrows('TableName'). now the engine knows it has to use relations.

    SET DC_KIND="AUTO";
SELECT
'CountriesNames'[countryName], 'CountriesCodes'[countryCode],
COUNT (  )
FROM 'CountriesCodes'
    LEFT OUTER JOIN 'CountriesNames' ON 'CountriesCodes'[countryId]='CountriesNames'[countryId];

    You can try to check what is going on yourself using DaxStudio -> Server Timing feature.

    One important thing! IF you query column from one table (then the behavior of auto-exist is fired).

    enter image description here

    And we get 5 insted of 10 occurence.

    https://www.sqlbi.com/articles/understanding-dax-auto-exist/