MDX Query Can't connect Fiscal Month/Quarter to my Measures

Question

I've been building an MDX query using excel's powerpivot. I connect to my cube, drag and drop Measures /Dimensions and my query has been working just fine. Up until I try to pull different dimensions.

A simple version of my query:

SELECT 
NON EMPTY { [Measures].[EP Projected Impressions] } ON COLUMNS,     
NON EMPTY { ([EP Hierarchy].[EP Tactic ID].[EP Tactic ID].ALLMEMBERS ) } ON ROWS     
FROM [MI_Cube]

This will return:

(EP Tactic ID) (EP Projected Impressions)

1            10    
2            20             
3            30    
4            40    
5            50

Now when I try to pull in date information for each tactic from the Time dimension it just gives me a copy of the above results with each time dimension member.

Example query:

SELECT 
NON EMPTY { [Measures].[EP Projected Impressions] } ON COLUMNS, 
NON EMPTY { ([EP Hierarchy].[EP Tactic ID].[EP Tactic ID].ALLMEMBERS * [Time].[Fiscal Year].[Fiscal Year].ALLMEMBERS ) }  ON ROWS 
FROM [MI_Cube]

Results:

(EP Tactic ID) (EP Projected Impressions)     (Fiscal Year)

1            10                          FY2015    
1            10                          FY2014           
1            10                          FY2013    
1            10                          FY2012    
1            10                          FY2011    
2            20                          FY2015    
2            20                          FY2014           
2            20                          FY2013    
2            20                          FY2012    
2            20                          FY2011

etc....

Does this mean that I cannot pull the Time.FiscalYear dimension for each TacticID? Or do I need to restructure my query? EP Hierarchy has lots of dimension members I can pull successfully, but when I try to pull anything from EP Hierarchy and Time my results get multiplied instead of combined.

Thanks for any advice, trying to wrap my head around cubes and mdx queries.

Your queries are returning just what I expect them to. I don't understand when you say "when I try to pull in date information for each tactic from the Time dimension" so perhaps you need to draw us a picture of what you want the results to look like! Where will the years go? How many numbers will be displayed, etc? — Magnus Smith

Dan Dan · Accepted Answer · 2014-12-16T08:28:38

It seems that you are simply missing a relation between the fact table holding the [EP Projected Impressions] member, and the dimension table holding your [Time] dimension.

By adding a relation between a foreign key on the fact table and the primary key on the dimension table, your measures should get correctly filtered by any attributes you slice on the dimension.

MDX Query Can't connect Fiscal Month/Quarter to my Measures

2 Answers