Topic: ‘Business Intelligence’

One of the default cubes in the Analysis Cubes for Excel product for Dynamics GP is the Receivables cube.  It can be used to generate refreshable aging reports via an Excel Pivot Table.

To do this, users should add the Company ID attribute to the Report Filter area to filter the results for a single company.  Select the Aging Periods attribute for the Column Labels and the Customers attribute as the Row Label.  After that, all that’s left is to add the Aging Amount measure as the Values section in the PivotTable.

If the aging routine hasn’t been run recently for your company database, however, an area of confusion may arise as users try to tie the cube aging report to the one found in GP.  This has to do with how the aging routine and the cube data load work together to populate the cubes in the data warehouse.

When the aging routine is run in GP (Tools >> Routines >> Sales >> Aging), it updates several fields in the RM20101 table.  If documents in this table have aged to the point that they should be bucketed in a different aging period, this routine ensures that the aging period index for that document is updated appropriately in the underlying GP table.   Coincidentally, it is from this very same table from which the Receivables cube draws its data.  Not surprisingly, the data load for the Receivables cube will only be correct as of the settings identified during the last time the aging routine has been run. 

Therefore, in order to ensure that amounts from the various Receivables documents are bucketed in the correct periods, you may want to consider automating the Receivables aging routine to run just prior to the data load for the cubes.  Additionally, you’ll want to make sure that both procedures occur on the same day to ensure that documents are bucketed correctly.  Doing so will ensure that the data in the cube will tie out to any Aged Trial Balance report printed from GP during the course of the business day immediately following the data load.

Although several methods exist for automating the aging process, including requiring your users to run the process manually at COB.  One of the best descriptions of how to accomplish this process I have seen resides over on Michael Johnson’s blog.  Be sure to check the following link for a detailed description for automating your aging process: http://mbsguru.blogspot.com/2009/04/automatic-aging-for-gp.html

We have had a lot of feedback from customers on the Excel reports available for Dynamics GP 10.0. The most common question is how much do the reports cost and what does it cost to implement them? The good news here is that if you are licensed and currently running GP 10.0, you already own them and it is a simple installation. I thought I would provide a step by step of installing the reports, so here you go. Read the rest of this entry »

While setting up Analysis Cubes on one of our new internal environments, one of our consultants came across an error we had never seen before.

This particular install was being completed for a 64 bit machine  containing Windows Server 2008 and SQL Server 2008, so the consultant had to use a 32 bit install workstation.  When entering the server name for the data warehouse and tabbing off of the field, he received the following “SQL 2008 Server connection failed. Please enter a valid SQL 2008 Server name” error:

First, we confirmed that SQL Server 2008 was, in fact, installed on the target machine and that the correct Analysis Cube install package was being used (note: Service Pack 3 must be used to install Analysis Cubes with SQL Server 2008). 

In the end, we discovered that SQL Server 2005 Tools were still installed on the 32 bit install workstation from a previous install of SQL Server 2005.  Once we installed SQL Server 2008 on the same machine, it created a conflict for the install process.  This appeared to be the hang-up, as once we tried installing from another 32 bit machine on which SQL Server 2005 had never been installed, the installation worked properly.

I’ve been wanting to spend more time with Microsoft Office Sharepoint Server 2007.  I’ve found that to be difficult because virtual pc images don’t provide the spontaneity to try a quick feature (plus they can be slow) and MOSS 2007’s hefty requirements make it difficult to install on just any old machine.  I really wanted MOSS on Vista to let quickly try a few things like Excel Services. Well, if you are considering MOSS 2007 and just want to try it out there is a better option.

Bamboo Nation provides a free piece of software that allow MOSS 2007 or Windows Sharepoint Services to run on a Vista Machine. (Not XP so here is another good reason to upgrade.)  MOSS 2007 is available as a trial too so you can play for 3 months for free. Bamboo Nation’s install runs easily and all you have to deal with is the MOSS install.

Everything I have tried so far runs fine with one caveat. Business Portal for GP will not install because the installer detects that Windows server is not running. I haven’t found a way around that so you can’t use this technique to try out BP. Also, don’t even think about running this in a production environment. I know, if you can figure out how to install and run MOSS, you can figure out not to run it on Vista in a production environment but I had to say it.

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!

On Friday, January 23, Microsoft announced significant changes to its business intelligence strategy.  You can find the detail behind this at http://www.microsoft.com/presspass/features/2009/jan09/01-27KurtDelbeneQA.mspx as well as a number of great blog entries.

To help our customers and partners, I want to summarize those changes then talk about how they will impact you.

Here are the announced changes:

1. Performance Point Monitoring and Analytics will be downgraded into MOSS Enterprise CALS:  This is the part of PPS that handled all dashboarding and KPI’s.  It is the very visual, very sexy part of the product that really helped it sell and also the part that worked the best.  If you own MOSS Enterprise, you now get M&A for free.
2. Performance Point SP3 Release in July/Planning Discontinued:  Planning is the portion of PPS that is the hardest to implement and was intended to replace Forecaster and Enterprise Reporting.  MS will release a performance update service pack (SP3) and then will stop development of new features.  As of July, when SP3 releases, PPS will cease to exist as a stand alone product.
3. Management Reporter To Be Managed by Dynamics:  Management reporter, the FRx replacement reporting tool in PPS, will be given to the Dynamics team to own and enhance.
4. Enterprise Reporting is Back on Support: All previous announcement of Enterprise Reporting’s support discontinuation (planned for Jan 2010) are revoked and its back on the price list.
5. Proclarity Desktop:  Gone.  Not much more comment other than that.

Why did they decide to do this?  Answering solely for myself:

1. The planning module was definitely a v1 release and required significant technical and programming expertise to implement.  However, the Dynamics channel already had good forecasting tools (Forecaster, ER) and wasn’t willing/able to adopt PPS quickly solely for that.  The channel that was adopting it (infrastructure and MOSS partners) weren’t very adept at financial planning and forecasting, so an automatic channel disconnect was setting in.
2. PPS was expensive.  In this environment, it wasn’t going to get good play.  In addition, customers with Enterprise Agreements were feeling "nickled and dimed" over the additional cost in the EA for PPS.
3. MS truly believes in "Democratizing BI", especially in the role Excel and SQL play in this space.  By pushing core BI functionality back into MOSS, Excel and SQL, they allow the widest possible audience to buy and use these features.
4. Lastly, I truly believe someone finally said "Hey, don’t you think the Dynamics team should be working on the finance stuff?".

So, what does this mean to the MS customer?

1. If you own PPS Planning, stick with it, but start looking for a transition in 2012 or thereabouts.
2. If you own PPS  and only use it for M&A, renegotiate your EA when it comes up to lower the cost of it by the PPS piece that got added.  Other than that, you are in the catbird seat.  Good job!
3. If you were looking at PPS for planning, kill the search and talk to your reseller about alternatives.
4. If you were looking at PPS for M&A, continue.  Having it in MOSS Enterprise is great news!
5. If you were looking at Forecaster, FRx, Enterprise Reporting, stop being fearful of the decision.  The products are great and will be supported into the foreseeable future.

As to Management Reporter, we don’t know what’s going to happen to that.  It was billed as the next-gen FRx, but its feature set is, IMHO, pretty crappy still.  I think it will be enhanced significantly by the Dynamics team and become a truly awesome product, but don’t look for short term results.

MS promises it will deliver more roadmap information at 2009 Convergence in New Orleans.  Will have our usual large team there, so we’ll update you after the conference.

As promised at iSight, our customer conference from last week, here is the document comparing Microsoft Office Sharepoint Server with Performance Point for Business Intelligence needs. There is some overlap between the products depending on what you want to do and this document highlights where they overlap and where they differ.

Phew…3 days of hardcore BI geekdom come to a close.  The action for today was very representative of the first two days:

Keynotes

Kurt DelBene did a nice job, along with Kristina Kerr, Senor Product Mgr.  Kurt gave a pretty good, but very stock analyst\marketing\can’t-tell-you-anything preso on the future of the BI stack and integration of the office solutions to same.  If you think self-service via Excel and greater integration with MOSS, you think right.  However, Kurt also commented that we are only "halfway through Wave 14", so you won’t see anything for at least another year.

Kristina did a very catchy demo of Microsoft Surface as a BI UI by showing water usage data for the downtown Seattle core, mapped against Visual Earth, against temperature and time.  Very cool, very 3-D.  Not a single customer I work with will ever buy it.  K:  Come up with a self service app that lets me order and pay in a restaurant without talking to a waiter, and I think you may have a winner.

From an overall perspective, the keynotes (with the exception of Ben Stein) were the definite weak point of the conference.  Little solid info, lots of marketing stuff. 

Sessions

The only one I did today was Avoiding Common Mistakes in Analysis Services:  Very nicely done by Craig Utley.  Between his discussion around Attribute Hierarchies and Attribute Relationships, he solved 4 separate problems I have currently with customer deployments.  Also, he has a great book called Business Intelligence with Microsoft Office Performance Point Server 2007. 

Vendor Pavilion

Today was the first day I spent time in the Vendor Pavilion and spent the entire time with Robert Sterling, VP of Partner Alliances, at Strategy Companion.  SC is a great vendor of BI plug in solutions for Dynamics CRM and is one of the few that produce a truly powerful and highly integrated solution. 

Hands on Lab

The balance of the day was spent in the HOL working on Performance Point Dashboards, Excel Services\MOSS, and Dynamics AX integration with SSAS.  I think I set a conference record of almost 4 hours in front of the training machines, but was very impressed by the content.

Overall Review

In general, this is a good conference and worth the money, provided you look closely at the conference agenda BEFORE you sign up.  If you’re a client of IBIS and customer of Microsoft, send your CIO or high level tech team combined with at least one SQL Server DBA.  If you are a partner, send an architect and at least on business application (ERP or CRM) consultant.  This year, only I went.  Next year, we’ll probably want to take at least one more person.

Avoid the keynotes.  Split the sessions up to cover maximum ground.  Spend lots of time in the HOL. 

Thanks for reading, folks.

Today’s keynotes varied between thought-provoking and mind numbing.  Ben Stein, noted comic economist, opened the day with a cogent overview of the current financial crisis and how it came about.  He followed this with a listing of the country’s problems, a touching personal story and then wrapped it all into a simplistic solution:  help make good families.  No ideas on how, but certainly it was entertaining.

The second keynote was a panel of BI guys from Accenture, Dell, HP, Profit Base and Hitachi being asked "What will BI look like in 2020?".  Kill me.  The answers were about what you’d expect – "blah, blah, blah…and that’s why Dell/HP/Accenture/whatever is a particularly good solution for blah, blah, blah".  After the Accenture guy coined the term Information Resource Planning ("IRP"), I bailed to prevent any inadvertent vomiting from hitting the guy seated in front of me.  Really, does this industry need one more fill-in-the-blank-resource-planning acronym?

Content today was, in a word, rock solid.  Here’s what I did:

Data Mining with Excel:  Okay, this sounds a dry as can be…but WOW!  The presenter covered the use of Excel’s Data Mining add in to do predictive analysis on example data sets.  Where this should be of interest to all of IBIS’s clients is the power to mine your existing ERP and CRM data (even better with CRM, but if you have Analysis Cubes for Excel installed with GP, it would work great) with an easy to use predictive analysis tool the better to identify hidden relationships and trends in customer buying behavior. When coupled with basket analysis ("when x bought y, what else did they buy") and category analysis ("what do people that upgrade to X have in common"), you really, really get fast information you can leverage into tighter, more focused marketing behavior.  Best of all, you own this stuff already with MS Office. 

Strategic Planning and Scorecards for PPS:  This entire conference is really about three things:  SQL 2008, Excel and PPS.  PPS really is showing well in two areas:  consolidating and controlling Excel based planning and budgeting sheets, and fast dashboard construction of analysis data in Analysis Services, Excel, SQL Tables, etc.  This session was a little dry, but great to see the entire life cycle from plan to report.

Leveraging Your Business Intelligence Applications in Sharepoint:  In case you think BI is only a huge ticket item, think again.  Using basic Excel\WSS or Excel\MOSS, you can easily create awesome team template sites that have built in KPI and Metric functionality from a data source as easily managed as a Sharepoint-mounted Excel workbook.

Hands on Labs:  MS did an amazing job on these.  I spent about 3 hours in the lab working on Excel dashboards, Excel Services and Performance Point Dashboard construction.  From them, I take away the following points:

1. Everyone running GP and CRM needs to be using Analysis Services and Excel.  Period.  Its the best data mining and dashboarding tool every put into the hands of the end user.  Ever.  Period.  Really.
2. Excel Services and MOSS:  Done right, this solves all the problem related to mailing around Excel worksheets or storing them on a network.  You can centrally store them, see them in a web page, pull out only individual elements (like a single chart) for display, bury segments into a Sharepoint KPI, and the list goes on.    Small clients can buy this off the Dynamics price list fairly inexpensively and if you are BRL AM you already own a lot of it. 
3. PPS Dashboards:  This is where you should start with PPS, if you laid in the foundation of Analysis Services.  However, you can also use Excel, SQL Tables, Sharepoint Lists and any ODBC datasource as a dashboard datasource so its not a hard requirement.

Tomorrow is going to be more HOL and sessions and I’ll do a wrap post.  For those of you coming to iSight, I look forward to talking to you about all this stuff in person.

Dwight

Okay, once you get past the fact that the opening keynote by Stephen Elop was preceded by 80’s cover tunes played (loudly and early and before I had enough caffeine) by "The Dudley Manlove Quartet" (who had 5 members), the keynotes were definitely worth the wait.

The key (no pun intended) points:

1. MS wants to "democratize" BI. 

In essence, put easy to use tools, built in Excel, into the hands of the end user.  Then allow them to use those tools to build stuff that the rest of the organization can share through MOSS.  This "self service" BI is nicknamed Project Gemini and will start appearing in staged feature pack releases (I think that’s what they said). 

The most astonishing thing I saw was one of the program managers, working from a $1,000 workstation using Excel 2007 with the Gemini plug-in, pull in a data set of 20,000,000 rows (that’s right, 20 million rows), and dynamically build a pivot table whose dimensional relationships were created on the fly, upload the table to MOSS and, while doing so, dynamically create an Analysis Cube that everyone else could share.  And he did in in 20 seconds.  Almost any Excel knowledgeable accountant I know could do what he did if they had this loaded.

2.  SQL 2008 is totally, totally cool. 

You will now hear it here first:  I am leaving my wife for SQL 2008.  MS just set a data loading record of 30 minutes for 1TB of data. This is a world record for a wintel platform and beats Oracle by 15 minutes.  Combine that with its ability to scale massively up (they were doing demos with 150TB of data where reports were kicking out in 4 or 5 seconds) and still meet the needs of the SMB space, and SQL2008 is definitely a hotty.

The rest of the day was a plethora of preso’s on KPI’s and Dashboards in MOSS and Performance Point, combined with great session on using Excel and Excel Services to deploy good, solid BI solutions inexpensively (both on initial investment and TCO).

It appears IBIS is leading the pack in BI for the SMB space with our fixed fee offerings around Analysis Services and Excel, and especially in our work with deploying dashboards deployed in MOSS via Excel Services.

One last note:  MS has to learn not to let the marketing department try to make humorous Powerpoint preso’s for technical staff.  MS is a lot of things, but as a humorist, they really, really suck. 

More tomorrow.  If you want intra-day updates, check out my twitter feed at http://twitter.com/DwightSpecht.

Dwight