We have a Fiscal Time (starts Jan-Dec) and Seasonal Time(Oct-Sep) Dimension in our Sales Cube.
Our requirement is to keep 4 years history for both dimensions. So the data in our source table contains Jan 2011 -Present
This is how our category looks like:
Fiscal - 2011,2012,2013,2014
Seasonal - 2011 (EXCLUDE), 2012 (2012 Q1 contains Oct -Dec 2011...) , 2013,2014, 2015
The categories looks correct in transformer.
In Analysis studio, the dimensions contains all the available categories.
However when we display 2011 from Fiscal dimension sales, it only contains Oct - Dec 2011 sales. Jan - Sep 2011 is missing.
Is this because we are excluding 2011 from our Seasonal Dimension? This contains Jan-Sep 2011.
Is there a way to fix this?
We don't want to show 2011 in Seasonal since we do not have Oct-Dec 2010 in our file which will make our year incomplete.
We want Jan-Dec 2011 Fiscal sales to show up.
Thank you in advance.
If you are trying to show fiscal and seasonal on a single crosstab, depending on the underlying structure I could see it dropping numbers for the 'missing' sales time period. Is this the case - you are trying to show fiscal and seasonal numbers on the same report (same query)? How are you excluding the seasonal data - cube custom view, keys in the data, slicers, something else? Which cube technology are you using?
We have separate reports for Fiscal and Seasonal Sales.
When we use Analysis Studio and drag the Fiscal Time dimension in our Sales Cube as columns, 2011 shows up but no sales for Quarter 1 - 3. We need to show the sales for all 2011 months.
When we drag the Seasonal Time dimension as columns, 2011 is NOT showing up which is what we want. This is because in Transformer, Show Diagram, we set 2011 category in our Seasonal Time Dimension itself to EXCLUDE.
I tried to set the Inclusion Property in Fiscal Dimension for the the 2011 Year categories to Always Include but this does not fix it.
From the Transformer help: "The Exclude option not only omits categories and their descendants from the dimension view; it also omits the data associated with those categories." So that is probably the issue.
The way I think of it, the dimensional views (which act like filters) are always in effect, even if the custom view is from a dimension not in a give query/report. If that sound odd, it has good use cases. For instance we have an org tree dimension and assign people (groups) to apex views on that dim depending on their position in the tree. Then no matter how they look at the data, it is filtered to just their organizational level, even if they do not include the org tree. They cannot see data outside their area. Which is great for me, but does not help you.
Kind of thinking out loud here, but is this package-based data and are the measures in the same data source fact table? Or do you have the option to split them out into separate tables? Or perhaps just separate query subjects in an FM package? I am thinking of separate fact tables with conformed dimensions in the data source. Then you could have the the seasonal fact table or query subject not include the data you don't want to show up, so it would never get to be in the cube in the first place. Does that make some sense? I think it is worth trying out if you can.
It works when creating a Dimension view and using cloak on Seasonal Time Dimension.
Thank you!!!
OK, cool. I did not think I was very clear, but maybe it was enough to get you pointed in the right direction. :D