Analysis Cubes Basics for Microsoft Dynamics® GP | Dynamics Care Services

My colleague Rober Sawyer has an awesome look at Analysis Cubes Basics for Microsoft Dynamics® GP.

This is a recording of one of our webinars and if you're even thinking about Analysis Cubes you need to watch this.

Join us tomorrow for the CFO Special Interest Group

Join us tomorrow for the GPUG CFO Special Interest Group. We’ll be looking at and talking about Analysis Cubes for Excel.

Data Analytics with SQL Server 2012

For those of you looking to go beyond my basic Excel dashboard session and beyond some of the Excel Deep Dive information check out RunAs Radio’s new episode on Data Analytics with SQL Server 2012.

Dan's Corner for Dynamics GP: OLAP for Dynamics GP

Dan Edwards takes a look at Analysis Cubes in OLAP for Dynamics GP.

Value of 0 is not valid for Selected Index (Analysis Cubes Install)

Francisco Hillyer addresses Value of 0 is not valid for Selected Index for Analysis Cubes Installation

RunAs Radio | Steve Wright Improves the Performance of Analysis Services!

On the latest RunAs Radio podcast, Steve Wright looks at improving the performance of Analysis Services.

All of you Analysis Cube for Excel junkies should check this out.

Analysis Cubes and GP2010: More Retained Earnings Problems | DynamicsCare

Dwight Specht digs into Analysis Cubes and GP2010: More Retained Earnings Problems over at DynamicsCare.

Analysis Cubes: Conversion failed when converting Date and/or Time | DynamicsCare

Writing at DynamicsCare, Dwight Specht tackles a really obscure issue with Analysis Cubes: Conversion failed when converting Date and/or Time

Analysis Cubes and Arithmetic Overflow

Dwight Specht took advantage of Atlanta’s winter weather to tackle a problem with Analysis Cubes and Arithmetic Overflow. He may now forever be known as Mr. BigInt.

Microsoft Moves into Leaders Category on Forrester’s 2010 Enterprise BI Platforms Wave

 

Microsoft has moved into the “Leaders” category for Enterprise BI Platforms according to Forrester. The beauty of the BI offerings for Microsoft Dynamics GP is that the heavy lifting of extracting data, transforming it and preparing it for analysis is all done behind the scenes with Analysis Cubes for Excel for Dynamics GP. This leaves businesses free to concentrate on analyzing their data, not fooling around with loading it.

Installing Analysis Cubes for Excel for Dynamics GP is really 3 basic steps:

1. Run the GP Analysis Cubes Wizard – Answer a few questions like server location and where the analysis cubes should be created.
2. Run the SQL Server Agent job created in SQL Server to populate the cube data. This handles the whole Exchanges/Transform/Load process for Dynamics GP cube data.
3. Explore the data with Excel Pivot Tables

There are more nuances to really get the most out of Analysis Cubes but to get started, it doesn’t take any more than that.

Why aren’t you using Analysis Cubes today?

Exciting Additions to Analysis Cubes for Dynamics GP

It’s nice to see new Analysis Cubes available for Analytical Accounting and the removal of password protection for SSIS packages. Both of these are huge. AA can create large numbers of transactions so cubes are great for analyzing them. Now that the SSIS packages are no longer protected, partners and customers can make changes without a lot of hassle. Inside Microsoft Dynamics GP has the full scoop in their post Exciting Additions to Analysis Cubes! - Inside Microsoft Dynamics GP.

I’m doing  a session on Analysis Cubes at PartnerConnections. You won’t want to miss it.

Microsoft and Dynamics GP Business Intelligence

The Edge is reporting that Microsoft's Business Intelligence platforms have moved into Gartner's 2010 magic quadrant. Personally, I've never placed much stock in Gartner's analyses but other people do.

Here is my take away. I hate the fact that one department of MS of doesn't know what other departments are doing. It often looks like MS has 3 competing strategies on any one technology. However, now that Performance Point is effectively just a few features in Sharepoint, PowerPivot, the Excel Data Mining add on and Excel 2010 are stepping up to fill in Microsoft's BI gap.

At I.B.I.S. we've been awfully busy with BI work around Dynamics GP. Dwight Specht really got the ball rolling and now David Duncan has taken over and is really driving value in BI. Thanks to David's work we now have standard GP cubes around Fixed Assets, Field Service Contracts and Field Service Service Calls. We've also got some cool views available around security. Feel free to drop me a line if you're interested in digging deeper into your GP data.

GP Analysis Cubes Connection Error Fix

David Duncan provide some great troubleshooting information while installing Dynamics GP's Analysis Cubes on SQL Server 2008. Make sure that you take a look over at I.B.I.S.' DynamicsCare blog.

Dynamics GP 10 Analysis Cubes for Excel: Notes from the Field

Dwight Specht has some great notes from the field on deploying Analysis Cubes for Excel on the I.B.I.S. ERP blog. If you're starting an Analysis Cubes for Excel deployment or running into issues, take a good look at this post.

Analysis Services For Excel: Currency Formatting

An annoying, yet easily resolvable issue I've come across in Analysis Cubes for Excel is that the calculated measure formats do not come across to Excel (like YTD Budget - GL Trans which should be Currency but comes across as General). After going through the usual troublehsooting (is Excel 2007 setup to inherit the format from the cube, etc), I found this is a built in issue with SSAS when a default language isn't assigned to the database or cube. To resolve do the following:

1. Open Business Intelligence Development Studio, then open the Analysis Services Database in which you installed ACE.

2. Open the Financials Cube

3. Click the Calculations Button

4. Click the Script View Button and in any calculation where FORMAT_STRING = Currency, add the line LANGUAGE=1033. Careful of commas - if you get a squiggly red line, you have a syntax error

6. 1033 is US Currency. You'd have to add the appropriate language code for other formats. Also, this implies the calculcation is language specific (ie: US Dollars only) so you'd have to do something slightly more complex if the measure supported multiple formats.

7. Close the window, save the changes and reprocess the cube.

8. Repeat with other measures in other cubes as needed

This should now allow you see the Currency format default into your Excel worksheet.

Dwight

Analysis Cubes, Clustered Servers, and Named Instances

Mark was kind enough to let me borrow his corner of the blogoshpere to pass along some fun information about Analysis Cubes.

Yesterday, I was working at a client (a public, very well known company running Dynamics GP as a financial package) that has a very complex internal environment on which they wished to run Analysis Cubes for Excel (ACE). We had:

1. A GP transactional Server running in a remote data center
2. A SQL Cluster with two physical machines and two virtual nodes setup in overlapping active / passive cluster (A is active, B is passive but B is also active with A as passive). The SQL Cluster would serve as the second tier in the ACE installation running both Analysis Services and storing the Data Warehouse. This means SSIS would also run on this machine.
3. Both were 64 bit OS
4. Several TSE machines, all 32 bit.
5. No domain priviledges, no sa user access, but my doman user id was granted access as sysadmin to the GP transactional server and to the SQL Cluster.
6. And, lastly, the SQL Cluster Virtual Machince (node) which would run the data warehouse, SSAS and SSIS was a named instance rather than a default instance.

One thing: I am not a MCSE, nor do I have tremendous amounts of hardware/infrastructure/high performance clustering background, so forgive me if some details around the clustering don't make sense to a more educated reader.

As you know, you cannot install ACE on a 64 bit machine. To compound matters, ACE, by default, won't install into a cluster because SSIS is not cluster aware. This causes a problem with the ACE install when it tries to create the SSIS packages - you get a "login timeout" error. You can replicate this by going into Management Studio and trying to expand the MSDB folder under the SSIS service on the virtual server. The error is the same.

To solve the first problem is pretty easy: Just install the server configuration utility onto a 32 bit workstation or TSE from which you can make a connection to the SQL and Analysis Servers. In this case, we installed the Server Configuration utility onto a TSE running Windows 2003.

To solve the second problem is tricky - you basically have two options:

1. Make SSIS cluster aware: This allows you to have SSIS run as a cluster service and manage the SSIS packages from Management Studion on any cluster node. The best source for understanding how to do this is here: http://msdn.microsoft.com/en-us/library/ms345193(SQL.90).aspx. However, YOU MUST read the correction entitled "Corrections for registry keys and file system paths" at the bottom of the page - if you don't it won't work.

There's one with pictures at: http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx

Honestly, we couldn't get this to work yesterday. I think because we didn't apply the registry entry from the correction and because we had a named instance, we had some problems. Also, DCOM security settings seemed to get in the way. So, we dropped this in favor of option 2.

2. Make SSIS Manageable from the Cluster Node but Not Cluster Aware:
You do this by updating the MsDtsSrvr.ini.xml file on each physical node of the cluster to point to the various virtual nodes in the cluster. The best link for this is here: http://support.microsoft.com/kb/942176.

Basically, you edit the MsDtsSrvr.ini.xml file found in %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn. Under the Folder node of the xml file, you'll find an entry for MSDB (that's the name child node) and "." (that's the Server Name child node). Change the value of the server name child node to the fully qualified instance name of the virtual server on which you will run the SSIS packages. For example, SERVERNAME\InstanceName.

By making this change, you are telling SSIS that it is running on that particular virtual node and instance, so you can then manage SSIS from Management Studio from that node.

Personally, I like this last option better as it is less work and I don't see where ACE really needs to be part of a high availability fail over scheme for most companies. If the failover occurs, you would probably need to reinstall the package files manually to get the system back up and running.

By they way, if you ARE using a named instance of SQL Server and NOT using a cluster, the same technique works to get ACE to install on the named instance.

The only problem we had was as follows: we have two virtual machines in the cluster and so created two entries in the MsDtsSrvr.ini.xml file, one for each machine, both with the name MSDB (see the above link and you'll get a sense for what this would look like). The odd thing that happened inside Management Studio was that I could connect to the SSIS service under virtual server A and see two folders for MSDB, both showing the same package list from A. If you refreshed the service, I then saw the packages on B. Refresh again, and I saw A. Basically, it toggled back and forth. I think that's because I named them both MSDB, but haven't finished the testing around that.

Once the above is done, you can now run the ACE Server Configuration utility. However, DEFINITELY use the SP2 version of ACE available on partnersource or you may get an error around the creation of the linked server entry on the GP transaction server. Use the cluster virtual server node fully qualified name (SERVERNAME\Instance) as the Analysis Cubes and Data Warehouse server and you should be good to go.

I'd love to hear comments, feedback or your experience with this.

Using Dynamics GP to Save $ In a Downturn

My latest MSDynamicsWorld.com article:

Five Ways to Use Microsoft Dynamics GP to Save Cash and Aid Productivity During a Downturn

is up. In it, I take a look at some things you can do to with your existing GP implementation to save money and protect your cash.

Get Cubed

For those of you interested in Analysis Cubes, the Analysis Cubes for GP product is great place to start but as a supplement, MSSQLTips has a nice article on building a cube from scratch using an existing data source.

Analysis Cubes

Steve Chapman and Rose Consulting have a nice post on using Analysis Cubes with Dynamics GP. It's a couple weeks old but I just found it today. Enloy. Go get cubed.