I have a column of Median values in my data set being queried through SQL Query. I need display those Median Values as part of a Table as well as a Bar chart. So, I don't need any aggregator or other calculation being done on the values and just need to display them as it is.
But, the values get changed due to the 'Aggregator'. I only have options to select 'Average', 'Sum' or 'Count' as aggregator. How do I get 'None' as aggregator so that my data is displayed as it is?
In a multidimensional data model, all data is aggregated. This aggregator is how the individual facts (records) are combined together in your cube query if you have two facts with the same properties. NB. Regardless of your data source, you always have a cube powering your Metric set/Visualization, it just might be managed automatically.
For example lets say you have two (post code, measure) facts (M3C 3G8, $245543) and (M3C 3G8, $133743). These facts have the same property and so will be aggregated. A SUM aggregation would return a single fact of (M3C 3G8, [SUM(245543,133743)] $379286), an AVERAGE aggregation would be (M3C 3G8, [AVG(245543,133743)] $189643), a COUNT aggregation would be (M3C 3G8, [COUNT(245543,133743)] 2).
If you only have facts with unique properties in your data, e.g. (M3C 3G8, $923929) and (M3B 3N1, $352343), then aggregating via SUM (M3C 3G8, [SUM(923929)] $923929) or AVERAGE (M3C 3G8, [AVG(923929)] $923929) will output the same values. It will only have an impact if you use a COUNT aggregator.
If you have two or more facts with the same properties that you need to not be aggregated, you will need to have another hierarchy/dimension to make each fact more unique. For example. (M3C 3G8, $245543) and (M3C 3G8, $133743) will be aggregated, but (A, M3C 3G8, $245543) and (B, M3C 3G8, $133743) will not. This hierarchy that you include to create unqiueness does not need to be included as a column in the table visualization, but must be present in the Metric Set. To see if you need to do this, you should be able to switch to Raw data mode, however this is not really useful for production use, since formulas, sorting, grouping, etc aren't available.
Thanks, Michael.
