MicroStrategy Report SQL is giving you the correct output – But when you see the values in Report Template seems to be higher than what’s expected.
Issue Possible Causes:
1) Check for the Dynamic Aggregation enabled in the Metric or not
2) Check for the Attribute Relationships
3) Check for the Report Objects Attributes
4) Check that there are no duplicate attributes/objects available in the project (Redundant objects also may cause – worst cases)
5) Check if System Objects does not have any redundant objects available in the project (Say if those are deleted)
6) Check whether user has OLAP privilege to perform Dynamic Aggregations (if any attributes are available in report objects means)
Tip Here :
I was breaking my head on all these possible causes, but even after checking all i couldn’t determine where exactly the issue was. Then, i had no other go and hence i raised an P1 Support ticket to discuss this with MicroStrategy to see if it’s an known issue or not. After discussing with MicroStrategy PS engineer (Nic – Glad that he was very sharp in identifying the issue quickly 🙂 ), found that all are correct from the MicroStrategy layer – the problem was with the DB Login
I was thinking for a while and smiled – told the PS engineer that “Why my mind did not think in that angle to determine the issue 🙁 ”
Issue was actually, in MicroStrategy Project – i had used the user connection to DB in Database Instance as super user which has access to different marts/chunk of data in a single schema/table. But when i was checking the same in DB Query tool/DB2 — I was running the query with Child user.
So, finally found that – the problem was with DB Login of connecting with Super user had caused this issue to happen.
ie) We have security lable for each row of data in the table in DB2 using LBAC security.
Say a table ABC have company X(100 records) & Y data(1000 records)
When i connect to ABC with super user –> It runs the SQL for 1100 records –> Though i had filter on Company = Y , i got the output as incorrect –> as the same security lable is available on some other records for Company X or Lable is missed for few transactions for Company X & Y and hence the issue was in terms of aggregating them together.
Glad & i was happy that there’s no issue in MicroStrategy & it’s wrt DB Storage.
Hope it would help someone else to think in this angle as well when you face such issue in near future..
Cheers and Have a Good Day!!