Troubleshooting Management Reporter Data Mart Staging Records
Management Reporter (CU10+) integration for Dynamics AX 2012 pulls subsets of AX financial data first into staging tables in the data mart database. The Maintenance Task, which is run every minute by the Management Reporter Process Service, attempts to resolve the staging records into the main Data mart tables. If a staging record cannot be resolved (typically due to invalid AX data), it will not be included in generated reports.
The steps below can be used to identify why these records cannot be resolved. Please see Management Reporter AX 2012 Integration Overview for more information about the Management Reporter Dynamics AX 2012 integration.
Steps to Review Staging Data Mart Data (CU14+)
1. Create the following stored procedure by running the attached script on the data mart database. This will be used to determine which natural FK (foreign key) is not resolving for each staged fact:
2. Execute the stored procedure by running this command:
exec ShowUnresolvableFacts
You should see a list that looks like the following:
Any unresolvable row will hold NULL in at least one of the four FK Id columns (DimensionCombinationId, PeriodId, ScenarioId, or OrganizationId) where its corresponding Key column (DimensionsKey, FactDate, ScenarioKey, OrganizationKey) is not null.
- If the PeriodId column is null, look in the source system for a period for the company indicated by the OrganizationKey column for the fact’s FactDate.
- If the ScenarioId column is null, look at the transaction in the source system having the key specified in the SourceKey column. Determine whether the transaction contains bad data for its scenario or whether the scenario integration has missed data needed by one of the transaction providers. In the result image above, the budget facts’ scenario key is bad data due to the BUDGETMODELDATAAREAID column in the BUDGETTRANSACTIONHEADER table containing an empty string.
- If the OrganizationId column is null, there is likely one or more companies missing from the DDM.
In most cases, we expect the cause of unresolved facts will be the DimensionsKey. This would surface as the DimensionCombinationId column containing null. In this case, you’ll need to determine whether the combinations referred to by the facts are in the DimensionCombinationStaging tables. If they are, then you should be able to run the attached stored procedure to determine what is causing the combination to not resolve.
1. Create the following stored procedure by running the attached script.
2. Execute the stored procedure by running this command:
exec ShowUnresolvableCombinations
You should see results like the following if there are any combinations in the staging table preventing facts from resolving.
Just like in the Fact query above, one or more pairings of Key/Id columns will contain a non-null value for the Key and a null for the Id column. In the example above, it appears the Department dimension in the for these records are causing the issue. To verify the data is missing/corrupt in the source system, one would need to copy the Dimension10Key from one of the rows having a null Dimension10Id and look for the dimension value in the appropriate source system table.
Review Dimension value in AX data
Two approaches:
-
Go straight to AX Dimension source view/table and look for the missing dimension value. This is likely the fastest method.
Example1: Transaction was posted using a dimension that doesn’t exist. It won’t be in source view/table.
Example2: Transaction was posted using a dimension that doesn’t exist for the company it was posted to. It will be in the source view/table, but not for the expected company.
- Run the dimension query used during the MR integration to identify why the DimensionValue isn’t being pulled over.
Approach 1: Going straight to AX Dimension source view/table:
Identify the DimensionXKey‘s that didn’t have a corresponding DimensionXId. This means that the dimension combination used on the transaction has a dimension that doesn’t exist in the data mart.
- Note the DimensionX (where X is a number) on the same record as this is the dimension we’ll be looking at in the steps below.
- Note the DimensionXKey that you are interested in as this is the specific dimension code that we’ll be looking for in the steps below.
- Note the OrganizationKey for the record. Example: “5637146826|Ledger” where 5637146826 is the RECID from the LEDGER table. This tells you what company the transaction is posted to.
Pull up the AX source information for that dimension:
You can use one of the statements below, depending on the AX version, to do this:
AX 2012 R1:
select DA.TYPE, DA.NAME, DA.RECID, DA.KEYATTRIBUTE, DA.VALUEATTRIBUTE,
DA.NAMEATTRIBUTE, DA.VIEWNAME, DA.BACKINGENTITYTABLENAME, DA.BACKINGENTITYKEYFIELDNAME, 0 as
Partition
from DIMENSIONATTRIBUTE DA
where DA.TYPE <> 3
order
by DA.NAME
AX 2012 R2/R3:
select DA.TYPE, DA.NAME, DA.RECID, DA.KEYATTRIBUTE, DA.VALUEATTRIBUTE, DA.NAMEATTRIBUTE, DA.VIEWNAME,
DA.BACKINGENTITYTABLENAME, DA.BACKINGENTITYKEYFIELDNAME, DA.PARTITION, DHL.LEVEL_
from DIMENSIONATTRIBUTE DA
left
join
(select DHL.DIMENSIONATTRIBUTE, DHL.LEVEL_, DHL.PARTITION
from DIMENSIONHIERARCHYLEVEL DHL inner
join DIMENSIONHIERARCHY DH on DH.RECID=DHL.DIMENSIONHIERARCHY and DH.NAME =
‘SystemGeneratedHierarchyAllAttributeStructure’
and DH.PARTITION = DHL.PARTITION) DHL
on DA.RECID=DHL.DIMENSIONATTRIBUTE and DA.PARTITION = DHL.PARTITION
where DA.TYPE <> 3
order
by DHL.LEVEL_, DA.NAME
1. Find the record where NAME equals the dimension you are interested in (the DimensionX from the prior steps).
2. Get the value for the VIEWNAME column for this record and run a select SQL statement against it.
3. Modify your select statement and add a WHERE clause to search for a specific dimension.
4. Make sure the DATAAREAID or LEDGER on the source record matches the same company that the OrganizationKey represents. A common issue is for a transaction to somehow have been posted with dimension values that don’t exist for the company it was posted to, but they exist for a different company.
Approach 2: Run the query used during the MR integration
To get the query used during the MR integration you can do one of the following:
- Use the Management Reporter 2012 Dynamics AX Integration Query Export Diagnostic to get the integration query for the dimension in question
- Capture a SQL Profile during the integration map
Option 1: Use the Management Reporter 2012 Dynamics AX Integration Query Export Diagnostic
Note: If the diagnostic fails due to lack of permissions or some other unexpected failure, the text file output will not contain the expected queries.
In the output text file, you will need to copy two queries into a SQL query against the AX database:
-
The ReportingVirtualDataAreaView query directly after this header:
Example snippet of query:
declare @ErrorMessage NVARCHAR(4000);
…
…
commit
transaction
end
- The query directly after the header line for the dimension you are interested in.
Example header line for the Fund dimension:
–DimensionName=Fund}{TableKeyColumnName=RecId}{ViewName=DimAttributeLedgerFund_PSN}{Partition=5637144577}
Run the ReportingVirtualDataAreaView query once against the AX database then run the dimension query. These results are the dimension values MR pulls for that specific dimension. If there are missing values then either the dimension value is not valid (as in the previously mentioned example where a transaction is posted using dimensions that don’t exist for the company in question) or one of the tables included in the query joins is missing a required record. If it is the latter scenario, then you will need to remove the joins on the query until the expected dimension record shows up and identify why that join is excluding the dimension record.
Option 2: Capture a SQL Profile during the integration map
To reduce the amount of SQL queries you need to look through you can:
- Disable the integration (using the Configuration Console)
- Useful SQL Profiler filters:
Application Filter: .Net SqlClient Data Provider
Database name: AX database name
You can search the resulting trace for the dimension name you are interested in and identify the main query used to pull the values.
