forcing SSAS respecting relationships between dimensions

Question

with regard to the following question and the given response I have the following question:

I am facing the same issue, but creating a bridge table like mentioned above is no option in my case as I still need the separate dimensions for other operations and different analysis.

For budgeting reasons I have to show all customers even if they have no match with the fact table. Enabling the corresponding option in EXCEL obiously leads to a cross join of the selected dimensions resulting in a complete list of all customers in all countries, although not each customer exists in all countries.

Is there another altrnative to force SSAS respecting relationships defined?

Many thanks in advance for assistance.

Analysis Services Multidimensional or Tabular? – GregGalloway Jan 22 '19 at 12:03 — GregGalloway, Jan 22 '19 at 12:03

score 1 · Answer 1 · answered Jan 23 '19 at 02:11

Let’s say you have FactSales with CustomerKey, CountryKey, DateKey and SalesAmount. When you build a PivotTable in Excel with Customer and Country on rows and SalesAmount on columns it only shows customers with sales. If you set Excel to show rows with no data it shows all customers in all countries.

Try building a new FactCustomerCountry table with CustomerKey and CountryKey only. Create a measure Customer Country Count.

Then create a new calculated measure:

CREATE MEMBER CURRENTCUBE.[Measures].[Sales Amount with Zeros]
AS
IIF(
 IsEmpty([Measures].[Customer Country Count]),
 Null,
 CoalesceEmpty([Measures].[SalesAmount], 0)
);

Use that measure in your PivotTable instead of SalesAmount. Do not show rows with no data. The measure should return meaningful Customer-Country combinations and should return all customers.

Hi Greg, I already have a customer count on my actual customer dimension. This works indeed. But I found, this may is very confusing for the user as he/she do not understand why to use this auxilliary measure. More over this will blow up Excel Analysis. Do you get what I mean? — peno, Jan 23 '19 at 14:45
@peno I get it. But you can’t have it both ways. Either you want only customers with sales and relevant customer-country combinations... or you want all customers-Country combinations by showing rows with no data... or you have to define what combinations you want — GregGalloway, Jan 23 '19 at 15:05

score 0 · Answer 2 · answered Jan 24 '19 at 17:54

Hi you can solve your issue in MDX. Take a look at the query below, its an example on Adventureworks. This will fetch all the products that were sold and the quaters that they were sold in.Now some products were never sold, I still want to see those product.

select [Measures].[Internet Sales Amount] on columns, --non empty {filter(([Product].[Subcategory].[Subcategory],[Date].[Calendar Quarter of Year].[Calendar Quarter of Year]),[Measures].[Internet Sales Amount]>0), filter(([Product].[Subcategory].[Subcategory],[Date].[Calendar Quarter of Year].defaultmember),[Measures].[Internet Sales Amount]=null) } on rows from [Adventure Works]

forcing SSAS respecting relationships between dimensions

2 Answers2