0

I have the following SQL query which I am trying to convert into MDX:

select avg(skucount)
from
(
SELECT count(distinct [SKUCode]) as skucount

      --,[SHOPCODE_WITHOUT_DIST]

  FROM [HFPL_DW].[dbo].[FactSecondarySales]
  where DISTCODE in
  (
  SELECT [DISTRIBUTORCODE]
  FROM [HFPL_DW].[dbo].[DimDistHierarchy]
  where REGION = 'KARACHI'
  )
  and month(saledate) = 7 and year(saledate) = 2018 
  group by [SHOPCODE_WITHOUT_DIST]

  ) as inner_query

The inner query returns the count of SKU saled on each shop(which is fulfilled by using "Group by ShopCode")

First I am trying to convert the inner query to MDX, I have tried the following:

WITH MEMBER [Measures].[SKU Count] AS
    COUNT( NonEmpty( {  [Product Hierarchy].[SKU].[SKU].Members }, ( [Shop Hierarchy].[SHOPCODE WITHOUT DIST] ) )   )
SELECT
    {
        [Measures].[SKU Count]
    } ON COLUMNS,
    NonEmpty(
        { [Product Hierarchy].[SKU].[SKU].Members },
        ([Shop Hierarchy].[SHOPCODE WITHOUT DIST] )
    ) ON ROWS
FROM
    [Consolidated Sales]
    where(
    [Time Analysis].[Month].&[2018-07-01T00:00:00],

[Distribution Hierarchy].[DISTRIBUTORCODE].&[1002]
)

Reference: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/51988607-78cc-4520-88db-c6d3e99dd1fc/mdx-to-count-the-number-of-members-in-a-dimension-based-on-another-dimension?forum=sqlanalysisservices

It is not returning anything.

Kindly help me acheive the desired output of the Average SKUs saled(The outer query), the number of SKUs saled per shop (inner query)

Alina Anjum
  • 1,178
  • 6
  • 30
  • 53
  • [Time Analysis].[Month].&[2018-07-01T00:00:00] - is this is the format on the month dimension in your cube ? – Daniel Hanczyc Oct 03 '18 at 07:54
  • @DanielHanczyc this is month filter. you may create the query without using this where clause – Alina Anjum Oct 03 '18 at 10:06
  • @DanielHanczyc , you may ignore WHERE clause in both queries. The basic requirement is to get an average of a column where we have derived counts against 1st column data. And we have to get data in aggregate function then average on that aggregated data. – Alina Anjum Oct 03 '18 at 18:27
  • Example: Shop A has 03 products Shop B has 03 products Shop C has 05 products Shop D has 01 product Raw Data in Sales Table: SHOP A | PRODUCT 1 | 380 Ctns SHOP A | PRODUCT 2 | 100 Ctns SHOP B | PRODUCT 1 | 180 Ctns SHOP C | PRODUCT 2 | 80 Ctns and so on We get counts of products against each shop that how many product one shop is selling. (This data fetched by Aggregation 'Group By' on shop codes and getting their count of products) Now we have to get an average of those all counts of products we got against each shop. SQL is returning perfect data but how through MDX? – Alina Anjum Oct 03 '18 at 18:33
  • Are you able to replicate specific example in AdwentureWorks ? – Daniel Hanczyc Oct 09 '18 at 11:25

0 Answers0