Simple (?) MDX Query

[arduk]

I have constructed a sales cube (using MS SQL Server)
Customers can be grouped by Region, and also by Corporate Banner (eg Caltex, McDonalds)
SalesItem is the fact table, and there are two dimensions - Customer.Region and Customer.Banner

I am trying to build a query that will display the count of customers (and the count as a percentage of total customers) for each Region, for each Banner. So the results will be Banners across the top, Regions down the side, and the cells will contain the number of customers for the given Banner & Region

I have the following MDX which returns the banners and Regions:

SELECT {[Customer].[Banner].[Banner Code].Members} on COLUMNS,
{[Customer].[Location].[Region Name].Members} on ROWS
FROM [sales]

but I can't get the count of customers for banner and region

as a newbie to olap and MDX, I am struggling a little with simple queries, so any help on this would be appreciated!!!

Thanks in advance for any help

 

[SarkMan]

I don't know exactly how your dimension are but here is an example using the food mart 2000 database. It counts the number of customers by city

with member measures.[CountOfCustomers] as ' sum(descendants(customers.currentmember, [Customers].[Name]),1) '
member measures.[TotalCustomers] as ' sum(descendants([Customers].[All Customers], [Customers].[Name]),1) '
member measures.[Percent TotalCustomers] as ' measures.[CountOfCustomers] /measures.[TotalCustomers] ', format_string='###.00%'
select
{measures.[CountOfCustomers],measures.[TotalCustomers],measures.[Percent TotalCustomers] } on columns,
{[Customers].[City].members} on rows
from Sales

 

[arduk]

Hi Sarkman,

Thanks very much for your help. That will certainly help me to calculate the count of customers and the percentage of total in my cube.

Is it possible to add another dimension on the columns, so that I would have Corporate Banners on the columns, Regions on the rows, and two rows for each Region - one as the Count of customers for the banner/Region, and the other as the Percent of Total
ie it would look something like this:
Banner1 Banner2 ... TotalForAllBanners
Region1 CustCount 13 17 30
%OfRegTot 43.3% 56.7% 100%
Region2 CustCount 5 16 21
%OfRegTot 23.8% 76.2% 100%
...
TOTAL CustCount 18 33 51
%OfRegTot 35.3% 64.7% 100%


I hope you can get an idea of what I am trying to achieve. I really don't know if it is possible, so any feedback on this would be very much appreciated.

Thanks again for your help!!

 

[SarkMan]

You can add a dimension but not like you are showing.

here is an example on what I think you are trying to do

with member measures.[CountOfItems] as ' sum(filter(crossjoin({descendants(product.currentmember,4 )},{Customers.currentmember} ) ,[Measures].[Unit Sales]>0 ),1) '
member measures.[TotalProducts] as ' sum({[Product].[All Products].[Food].children},measures.[CountOfItems] ) '
member measures.[PercentProducts] as ' measures.[CountOfItems]/measures.[TotalProducts] ', format_string='###.00%'


member measures.[CustomersName] as 'customers.currentmember.name'
member measures.[ProductName] as 'product.currentmember.name'
select
{measures.[CountOfItems], [Measures].[Unit Sales], measures.[CustomersName],measures.[ProductName], measures.[TotalProducts],measures.[PercentProducts] } on columns,
{crossjoin([Customers].[City].members,{[Product].[All Products].[Food].children})} on rows
from Sales

This query sums up the number of food items by city.


Hope this helps

 

[arduk]

Hmmmmm...Ok - will have to rethink how I display the info then...

Thanks very much for your help - I really appreciate you putting the time in to give me a hand on this!!

I have been having real trouble trying to get my head around MDX queries - can you recommend a good site or reference source??

Thanks again for your help!

 

[SarkMan]

here is a good intro to MDX.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnolap/html/olap_webbrs.asp

Also MDX Reference

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmdxfunctions_9elw.asp

Also, I use a book called Microsoft OLAP Solutions
written by
Erik Thomsen
George Spofford
Dick Chase

 

[SarkMan]

posted the wrong link for the intro to MDX

here is the correct one

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnolap/html/intromdx.asp?frame=true

 

[arduk]

Hi Sarkman - thanks for those links, they're very helpful...

FYI, I got the query to work as I wanted, using the following MDX

SELECT
{[Banner].[Banner Code].Members, [All Banner]} on Columns,
{{[Territory].[Region Name].Members, [All Territory]} * { [Measures].[Cust Count],[Measures].[Percent Of Banner Customers]}} on Rows
FROM Customer

Your help on this was very much appreciated - I'm sure I'll be back with more questions shortly

Thanks again