How to avoid cross-join / cartesian product on Pivot table using Tabular Object Model?

Jordi Perreman 46 Reputation points
2021-12-27T15:42:58.22+00:00

Hi

The Tabular Object Model has multiple fact tables and multiple dimensions. Some fact tables does not have a relationship a dimension.

When using our model in Excel in a pivot table, it gives for all the combination between the fact and dimension a Cartesian product. It shouldn't give any results at all, just a blank value or no record at all. In the Multidimensional model this works correct.160658-screenshot-2.png.

Left side: Multi dimensional model, right side Tabular Object Model. All the measures are located in another fact table.

A possible fix is to make a relationship between all facts and dimensions, but we want to avoid this option.

Edit:
Found an old thread with some info, but no solution. Is it even possible to resolve this issue without having a relationship?
relationship-without-a-measure-doesnt-work-when-analyzing-in-excel-ssas-tabular

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,344 questions
{count} votes

Accepted answer
  1. Alexei Stoyanovsky 3,416 Reputation points
    2021-12-28T09:40:37.83+00:00

    The behavior of the MD model in your example is the result of a non-default setting for the IgnoreUnrelatedDimensions property. You can't have this in Tabular, seeing as there're no 'dimensions' in TOM.
    While this can be resolved, it could take quite a bit of coding. See e.g. this thread

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.