GP Analysis Cubes - Invalid Debit and Credit amounts for some accounts in GPDataWarehouse

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hi all,

Hope you can help me with this problem.

My client has an implementation of GP Analysis Cubes for GP 10.0. The version of cubes is 2005.

We have a problem whereby for some accounts open year transaction debits and credits in the GP DataWarehouse do not match the corresponding amounts in GP source table GL20000. The corresponding transaction amounts in GP source table GL20000 are correct.

In some instances and for some transactions not only the transaction amounts are wrong, sometimes even transaction dates are wrong in the GP DataWarehouse which is really worrying.

The nightly DW SSIS update runs with no problems.

I am very puzzled by this. Questions:

1. Has anyone experienced this problem ? Any idea under which circumstances this could happen ? (e.g. some stray late update of source tables or something)

2. Is there a way to force re-population of transactions in GPDataWarehouse for the offending company and table (for e.g. GLTransactions) ?

3. Can GPDataWarehouse table LastUpdated be used for forced re-population by somehow changing the LastRow and DateUpdated columns to force re-population as the data in the source table is correct ?

4. What would happen if I delete offending records in the GPDataWarehouse ? Would they be re-inserted from source tables presumably with correct values and dates ?

5. More generally, could some explain how the records insert/update in GPDataWarehouse via SSIS packages actualy work for GP Analysis Cubes? What is the logic for records insert/update ? I couldn't find any information on this important aspect nor can I inspect involved SSIS packages as they are password protected.

I really hope that someone can help me.

Many thanks.

Regards,

Davor

*This post is locked for comments

All responses (4)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

RE: GP Analysis Cubes - Invalid Debit and Credit amounts for some accounts in GPDataWarehouse

Hi Harry,

Thanks for your reply.

I've read your blog post, but it doesn't tell me how to repopulate GPDataWarehouse. The SSAS GP Cube is not my problem.

The problem is that source data from GP gets somehow corrupted on the way from GP to the GPDataWarehouse database. The data for this particular company and GL account is correct in GP but not in GPDataWarehouse. In my GPDataWarehouse some data is wrong: some transaction amounts are wrong, sometimes even transaction dates are wrong for that particular account . There is only one account exhibiting this problem.

So my question is how to repopulate offending records in the GPDataWarehouse by somehow fiddling GPDataWarehouse control table LastUpdated has LastRow(ID) and DateUpdated columns ? Is this even possible ?

Also, would offending records be repopulated if in the GP source we change for e.g. description of the offending transaction thus forcing the change in Timestamp ? Would this trigger update of the changed records ?

This is what I don't understand, i.e. the logic of inserts/updates of records destined to GPDataWarehouse and what triggers them.

Regards,

Davor
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

RE: GP Analysis Cubes - Invalid Debit and Credit amounts for some accounts in GPDataWarehouse

Davor,

1. Yes, from time to time, the original deployment of GP Analysis Cubes throws no errors, but returns incomplete data.

2. It is possible for data to be repopulated in the GPDataWarehouse - please review my blog post on a similar issue.

redbeardblogging.blogspot.com/.../dynamics-gp-analysis-cubes-deployment.html

3. Connect to the Microsoft Analysis Server, then Right Click on the Dynamics GP Analysis Cubes at the top level and select Process, then click Ok,

4. I would strongly recommend against deleting anything.

5. You can refresh the data as described above. After that, it is typical to refresh data on demand via the Excel client.
Community Member Microsoft Employee on at

Like (0)

Report

RE: GP Analysis Cubes - Invalid Debit and Credit amounts for some accounts in GPDataWarehouse

Really appreciate for your reply, Jonathan.

As far I know, we haven't change the default behaviour of GP Analysis Cubes in any way. We only installed the GP Analysis Cubes without changing any of SSIS packages .

On the other hand, the fact that GP Analysis Cubes GPDataWarehouse table LastUpdated has LastRow(ID) and DateUpdated columns would indicate that apart from record insertion some updates are also meant to happen as well.

So that is why I wanted to know:

- What source data change triggers the update ? ( e.g. timestamp change)

- How to force a re-population of offending records for a company and a table (e.g. GLTransactions) for the company ?

I tried to open some relevant SSIS packages in BIDS but they are password protected.

Would you know if the password is set by Microsoft, because we did not introduce password protection on GP Analysis Cubes SSIS packages ?

Many thanks,

Davor
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: GP Analysis Cubes - Invalid Debit and Credit amounts for some accounts in GPDataWarehouse

The default behavior for the SSAS objects in GP is to purge all the data populated in the DW and populate it back. You can validate this behavior by following these steps.

1. Open SSMS and connect to the SQL server hosting the DW.

2. Expand SQL Agent > Jobs

3. Open the job that populates the data. This will tell you what SSIS package is called.

You can then connect to the SSIS server and export that package to file and open it with BIDS and validate what all steps are being taken.

So if you are seeing incorrect data in your DW and you are 100% certain that the source data in Dynamics is accurate then the default behavior may have been altered and the data isn't deleted it's just appending new data to the DW.