CODEWITHSUNDEEP
ssasrelationshipdimensioncross-join
peno
peno

Reputation: 11

forcing SSAS respecting relationships between dimensions

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.

Upvotes: 1

Views: 137

Answers (2)

MoazRub
MoazRub

Reputation: 2911

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]

enter image description here

Upvotes: 0

GregGalloway
GregGalloway

Reputation: 11625

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.

Upvotes: 1

Related Questions