Skip to main content

Notifications

Microsoft Dynamics GP (Archived)

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

Posted on by 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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Redbeard 12,931 on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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.  

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,113 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,918 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans