I got a very interesting case few months back, one of my customers implemented the Management Reporter and tried to implement their existing reports, it went well but for the reports that uses the Analytical Accounting Budgets that has filters over specific dimensions, budget amounts were not pulled at all.
At that time I tried very hard to resolve the issue with no luck, and therefore I had to install the FRx and work out the reports there till getting this resolved by Microsoft.
During the last days I have noticed many articles by Microsoft that they has identified the case and fixed this issue with the product updates, therefore this morning I decided to install Management Reporter 2012 with the final update to see if the issue was resolved, I created a dummy report and tested the case with no luck! Same issue is still there while I couldn’t replicate the same issue on a clean database.
After many researches I have seen a question answered in the community and located here, it is the same issue I am currently investigating, and the provided cause of issue is the gap in serials for “Analytical Accounting Transaction Dimension IDs”, simply the data inside AAG00400 table should look like the below:
What if I been having one of the dimensions coded as “15” instead of “7” as the case I had? The proposed solution was to create dummy dimensions to cover the gap between 9 and 15 which is doable and safe solution.
The problem is what if I had one of the dimensions coded as “700”! I am sure that there is no was to create 684 dummy dimensions to cover the gap where I had to reindex the whole database.
The operation I followed is very risky and requires a real expert in the SQL, below steps I used to fix my issue:
1. I searched for the tables that I need to affect, simply these are all the tables that has “aaTrxDimID” column, went to the SQL and wrote the following query:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE Column_Name = 'aaTrxDimID' AND COLUMN_DEFAULT IS NOT NULL
2. I didn’t take a backup of the database but you must take a full backup!
3. I took a backup for the tables that I need to update by running the following query:
| SELECT * INTO BAK_AAG00401 FROM AAG00401 |
| SELECT * INTO BAK_AAG00901 FROM AAG00901 |
| SELECT * INTO BAK_AAG01001 FROM AAG01001 |
| SELECT * INTO BAK_AAG01002 FROM AAG01002 |
| SELECT * INTO BAK_AAG02000 FROM AAG02000 |
| SELECT * INTO BAK_AAG02001 FROM AAG02001 |
| SELECT * INTO BAK_AAG40003BAK FROM AAG40003BAK |
| SELECT * INTO BAK_AAG30003BAK FROM AAG30003BAK |
| SELECT * INTO BAK_AAG04001 FROM AAG04001 |
| SELECT * INTO BAK_AAG20003 FROM AAG20003 |
| SELECT * INTO BAK_AAG10003 FROM AAG10003 |
| SELECT * INTO BAK_AAG00202 FROM AAG00202 |
| SELECT * INTO BAK_AAG30003 FROM AAG30003 |
| SELECT * INTO BAK_AAG00312 FROM AAG00312 |
| SELECT * INTO BAK_AAG00400 FROM AAG00400 |
| SELECT * INTO BAK_AAG00316 FROM AAG00316 |
| SELECT * INTO BAK_AAG00801 FROM AAG00801 |
| SELECT * INTO BAK_AAG40003 FROM AAG40003 |
| SELECT * INTO BAK_AAG00402 FROM AAG00402 |
| SELECT * INTO BAK_AAG00403 FROM AAG00403 |
| SELECT * INTO BAK_AAG00404 FROM AAG00404 |
| SELECT * INTO BAK_AAG00405 FROM AAG00405 |
| SELECT * INTO BAK_AAG00406 FROM AAG00406 |
| SELECT * INTO BAK_AAG00407 FROM AAG00407 |
4. I have identified what are the existing IDs and what it should be and ran the following set of commands for each record:
| UPDATE AAG00401 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00901 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG01001 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG01002 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG02000 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG02001 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG40003BAK SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG30003BAK SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG04001 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG20003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG10003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00202 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG30003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00312 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00400 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00316 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00801 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG40003 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00402 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00403 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00404 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00405 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00406 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
| UPDATE AAG00407 SET aaTrxDimID = 3 WHERE aaTrxDimID = 47 |
Viola! The budgets are now generating data and everything looks fine for the time being, I will keep you posted with any other issues that might occur due to this operation.
Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo
No comments:
Post a Comment