CODEWITHSUNDEEP
sasmdxolapcube
max00d
max00d

Reputation: 11

Marketing penetration in OLAP cube - Help with specific MDX measure definition

I am pretty new to MDX but I know what I want accomplish but its proving very hard. Basically, I have a dataset where each row is a sale for a customer. I also have postcode data and the UK population at each ward.

The total population in each ward is then divided by the count of the wardcode within the data set - e.g. ward A had a population of 1,000. I have ten customers who live in ward A and so the population value is therefore 1,000/10.

So as long as there are no other dimensions selected, only the region hierarchy, I can then drill up and down and the population penetration as count of customers / calculated population value is correct. However, as soon as I introduce more dimension the total population will not sum to its true value.

So I therefore need to do the calculation above within the cube and I am trying to find the MDX function(s) to do this.

Esentially something like -

step 1) sum the number of ward codes (the lowest level of the Geographic hierarchy) and group this by the distinct ward code, eg wardcodeA = 5, wardcodeB=10 etc.

Step 2) Then take the population in each ward (which could be stored as the total at ward level and taking the average) and then divide this by the result of the previous step

step 3) sum the results from each ward at the currently select Geographical level

Upvotes: 1

Views: 363

Answers (1)

ic3
ic3

Reputation: 7680

The fact other dimensions are changing the value of customers / population means that something in your modeling is wrong.

You should have a fact table (can be a view/concept) like this :

REGION_ID, CUSTOMER_COUNT, POPULATION_COUNT

Once you got this create a fact table and a specific measure for counting customers and population with a single dimension linked. This is the main point, do not link your measures with dimension that are not needed.

Upvotes: 0

Related Questions