Why does my OLAP Cube SUM measure always show the same result?
So I'm working on an SSIS OLAP data cube. During the cube setup wizard I originally set up a count measure that counts the number of 'attendance marks'. If I query this I can see the overall number, and then drill down to finer numbers by querying it via other dimensions such as 'full name'. Now after creating the cube I want to create another measure, a sum of a field that can be either 1 or 0, which is a calculated field in the data source view. I do this by adding a new measure through the 'cube structure' tab- selecting usage 'sum', the source table and then highlighting the column I wish to sum in the source column list. This new measure seems to work initially, but the issue comes up when I add another dimension to query the measure, drilling down to more detail. It displays the full sum from the entire system for every entry in the dimension. This is as opposed to the first measure that displays the results from within the scope of that dimension- so it shows the number of attendance marks that are linked to a Full Name, whereas this new measure shows it's sum of the total values in the system- not those within the scope of the Full Name. This was a bit awkward to explain, as is often the case with multi-dimensional problems! So does anyone know why this doesn't work? Am I missing something in my measure setup?
Best Answer
It looks like you have added the new measure into new Measure Group. Ff it is so, check the Dimension Usage tab of the cube designer and check that your dimensions are connected to the new Measure Group properly. Also if the measure is in separate measure group, you can drop this measure adn create it in existing measure gorup.
Totally right, my relationships were setup wrong. Ultimately I fixed it by moving the calculated field to my Fact table rather than the Dimension table, removing the new measure group. Thanks very much Pavel
