Saturday, September 13, 2014

Dynamics GP Analysis Cubes Deployment - Cutoff Date and Unknown Dates

For those of you new to deploying Dynamics GP Analysis Cubes for use with Business Analyzer, here is a link to an excellent post on doing just that.

Deploying Business Analyzer

I recently deployed GP Analysis Cubes, only to discover the Date fields were not populating correctly, they were coming up "Unknown."  This made it impossible to use date fields as filters, which prevents meaningful analysis of data.

First, I checked BIDS (SQL Business Intelligence Development Studio), and was able to validate I had data in the tables by Double Clicking on the Cubes, Opening the Cube Structure, then Right Clicking on various tables and selecting Explore Data (this previews the tables) - you should see data in Dimensions.  If you can do this, then you know the data is not the problem.

After validating the data was not the problem, I connected to the Microsoft Analysis Server, then Right Clicked on the Dynamics GP Analysis Cubes at the top level and select Process, then click OK.   This refreshed the Entire Analysis Cube Structure.  I was then able to go into Excel and click Refresh Data, and the Unknowns disappeared.

For some reason, using the Master Packages to create the Analysis Cubes, did not produce any errors, and did not successfully completely configure the Cubes.

As an aside - when following the documentation on the configuration tool for Analysis Cubes, you will encounter a window, which covers a cutoff date.  This is asking for a Historical Cut Off Date!   If you pick a date, everything prior to that date will be skipped, during Cube Creation.  This is not clearly spelled out in the documentation.

2 comments:

  1. I have followed the same instructions. checked BIDS and data was not the issue. Processed the Dynamics GP Analysis Cubes from top level, it was successful. But still Dates are Unknown.
    However, during installation/configuration of Analysis Cubes, I have not mention any Cut Off date, left that unchecked. Can that be the issue? Otherwise, kindly let me know how can I resolve this issue?

    ReplyDelete
  2. I have followed all the steps once again very carefully... and ta da, Unknown dates are vanished and everything is working great now. Thank you so much :)

    ReplyDelete