Analysis Cube for Excel – Issues and Resolutions
Posted Monday, February 9th, 2009
Our packaged service offering for Analysis Cubes continues to be well-received by those who choose to take advantage of the dynamic reporting capabilities offered by the product. One of the surprising consequences of installing Analysis Cubes for many of our clients has been to provide them multiple opportunities to validate data that currently exists in their accounting system. The ease with which Analysis Cubes provides drill through capability from summary-level to detail-level enables Controllers and other employees to target and identify areas in which data might have been miskeyed or never entered at all. By identifying these gaps and correcting them in GP, we are finding that users are gaining value from Analysis Cubes far beyond its immediate objective to provide dynamic reporting. In assisting our customers with this data validation process, we have uncovered a few issues with Analysis Cubes that we thought we’d share:
Outstanding Payables Amount is incorrect for Payables Transactions with multiple Purchase Lines:
One of the biggest issues we have identified occurs in the event that a Payables transaction has more than one Purchase distribution. If a transaction does have multiple Purchase distribution lines, the full Outstanding Amount for a Payables document is displayed in the Cube once for each separate line. If you have two such lines, the Outstanding Amount displayed in the Cube is double. To see for yourself, create a transaction in GP containing two Purchase distributions and the corresponding Payables distribution. Do not enter a corresponding payment.
Payables Transaction Entry Window
Payables Transaction Entry Distribution
Since no payment has been made on this transaction, one expects the Outstanding Amount for the Vendor on this date (assuming there are no other transactions that fit this criteria) will be the full amount of the document.
When you report on the Outstanding Amount for the Vendor in an Excel spreadsheet using Analysis Cubes, the Outstanding Amount will equal twice the expected amount. If you check the drill through (double-click the value cell) for this Outstanding Amount, you’ll see one line for each purchase distribution with the full remaining Outstanding Amount assigned to each line.
Payables Cube Report
Payables Cube Drillthrough
We have notified Microsoft of this issue and have learned that they are currently seeking a workaround for this issue. Even if no workaround is found, we do believe this is an issue that will be corrected in the next version of Analysis Cubes with GP 11.0.
Current Inventory Value Measure Ignores Negative Qty on Hand:
One of our clients pointed out a potential issue with Analysis Cubes unable to record an current inventory value for an item with a negative quantity on hand. To prove her point, she generated a stock status report by site in GP and replicated the same in Analysis Cubes by using ‘Items by Location’ and the ‘Qty On Hand – Inventory on Hand’ and ‘Current Inventory Value – Inventory on Hand’ measures. In every instance where a Qty on Hand was positive, the Inventory Value on the stock status report matched that in GP. In every instance where the Qty on Hand showed a negative stock, however, the stock status report showed the correct negative inventory value whereas the cube showed a zero. Because of this, the total inventory value of the stock status report from GP did not match that shown from the cube in Excel.
Negative Inventory Stock Status
Negative Inventory Cube Report
We’ve also identified this issue with Microsoft and are working with them for a resolution to this issue. We’ll be sure to use this blog to provide an update!
The last two issues have been encountered in both GP 9.0 and GP 10.0.
Resolution to an Earlier Issue:
We are happy to report a resolution to an issue with package and encryption levels we encountered with GP 9.0. This issue was reported on this blog in the September 29 posting. In that post, we reported that we were running into issues with Analysis Cubes requiring the install user to remain logged into the SQL Server console in order for a scheduled Analysis Cube job to run to completion. This created a number of issues, not the least of which involved dedicating a SQL Server license for that user to remain logged in at all times so the job could run as scheduled. Our ability to correct this issue was primarily limited since the SSIS packages are password protected. After multiple back and forth discussions with Microsoft, they have finally determined a workaround that we have used successfully. We’ve been told a KB article detailing this resolution will be posted shortly, and we’ll be sure to backtrack to this blog entry and post a link to that KB when it does become available on PartnerSource.
Update: As promised, Microsoft has released a KnowledgeBase article describing this issue and its resolution. You can find it on KnowledgeBase as article #967078.
Our thanks go out to those at Microsoft Support who have helped us resolve this issue!
Analysis Cube Compatibility with SQL Server 2008:
Finally, we’ve seen a few posts in the Dynamics GP forums questioning Analysis Cube compatibility with SQL Server 2008. We queried Microsoft on this and learned that there is no compatibility between GP 9.0 Analysis Cubes and SQL Server 2008. Compatibility between GP 10.0 Analysis Cubes and SQL Server 2008 is possible, but it requires use of Analysis Cubes Service Pack 3 (SP3) which can be found on PartnerSource.
As always, we’re interested in hearing about your experiences with Analysis Cubes and/or your comments on this post!
Tags: Analysis Cubes, BI, Dynamics GP
Posted in Business Intelligence, Reporting
Not yet using Dynamics GP? I.B.I.S., Inc. gives you the Dynamics GP options you want, and the business results you need.
Learn more about our Dynamics GP solutions
We are using Analysis Cubes but we’ve had limited success with it. The main problem is that it requires people to build the reports from scratch. Is there a pre packaged set of excel reports for Analysis Cubes???
Felipe,
Thanks for sharing an issue you’re having with Analysis Cubes. I am not aware of any pre-packaged set of Excel Reports for this product. I could be wrong, but I believe the reasoning for this boils down to Analysis Cubes being used best as a dynamic, ad-hoc reporting tool. In my opinion, one of the best values that can be gained from the Cubes lies in the ease with which reports can be created. This can often lead to new and interesting ways of looking at your data that you may have never considered before.
David
I have installed Analysis Cubes for GP 10.0 on SQL 2008 and 64 bit OS. I faced an issue while executing the master SSIS packages. After calling microsoft support, the agent told that it was a known issue and provided a fix. However, the agent did not provide the password for the SSIS packages and made the fix himself.
Hi I am trying to install Microsoft Dynamics GP Analysis Cubes on a windows server 2008 64 bit and SQL2008 and I am getting the error below
“Microsoft SQL Server workstation Components and tools must be installed in order to complete the Microsoft Dynamics GP Analysis Cubes Installation”
I have tried with the sp3 install for cubes.
Can anybody help please.
laurie
u can reply to laurie.fineberg@afgri.co.za
Many thanks
Have you any words of wisdom about beginning balances for balance sheet accounts with the Financial cubes?
I am having a very hard time verifying my cube data is “accurate”.
Stephanie,
A bit late, but in case you or anyone else checks back on this post:
We have confirmed from Microsoft that beginning balances in Analysis Cubes product are calculated only based on data that exists in the data warehouse. as part of the install, you have an option of establishing a cut-off date for your GL Transactions. If you opt NOT to include all financial data from GP in the AC data warehouse, then your beginning balances will be incorrect. You must include all financial data to have correct beginning balances.
Feel free to reach me at dduncan@ibisinc.com if you have questions about this!
David