GPWindow.com - Dynamics GP- REPORTING > SQL Analysis Cubes

Analysis Cubes for Dynamics GP Configuration Wizard

Thu, 17 Jun 2010 04:19:49 GMT

Error:

Dynamics GP Professional is required to proceed.

Strange error upon trying to configure Analysis Cubes using wizard.

Cause:

SQL Server doesn't have Analysis Services Installed.

Resolution:

Install SQL Server Analysis Services and the wizard will proceed.

Regards,
--
Mohammad R. Daoud
MVP, MCP, MCBMSP, MCTS, MCBMSS
Software Development Manager
+962 - 79 - 999 65 85
Great Package For Business Solutions
daoudm@greatpbs.com
http://www.greatpbs.com

Analysis Cubes with Dynamics GP

Thu, 17 Jun 2010 02:08:15 GMT

This video shows you an example of how to use the Analysis Cubes function in Dynamics GP to provide enhanced analysis and reporting on your GP data.

Analysis Cubes provides a quick way to present data and allow users to manipulated the report and analyze the data using simple OLAP techniques.

Analysis Cubes for Microsoft Dynamics GP (on SQL Server 2008)

Mon, 21 Jun 2010 16:09:36 GMT

SQL Server Analysis Cubes Passwords

Thu, 17 Jun 2010 18:10:18 GMT

I've recently been getting a number of queries about the GP Analysis Cubes and how we can modify the existing cubes. The cubes are password protected and we don’t provide the passwords to the DTS Packages or the SSIS Packages (on SQL 2005) because modifying these would compromise any chances at upgrading the GP Analysis Cubes to future releases.

Not having the password, however, does not limit your ability to extend the cubes that we provide, including the addition of new dimensions. The DTS/SSIS packages that we provide simply pull the data from the GP company database(s) into the Data Warehouse (DW) relational database first. Then, the DW database is used as the data source for the Cubes. You should be able to create any number of additional DTS/SSIS packages to pull from any other data source and bring it into the DW database, so long as the DTS/SSIS package honors the schema of the destination DW and doesn’t mess with any of its existing data that our DTS/SSIS packages populate. The dimensions are defined in the cubes, not in the DW or DTS/SSIS packages.

Please send me an email if you have any specific queries on this.

Posted by Tom Brookes

Analysis Cubes

Mon, 21 Jun 2010 18:11:19 GMT

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.

Receivable Aging in Analysis Cubes for Excel

Thu, 17 Jun 2010 20:07:50 GMT

My colleague David Duncan is spending a lot of time with Analysis Cubes for GP. In his latest post up at DynamicsCare, he looks at creating a refreshable AR aging report using Analysis Cubes and an Excel Pivot Table.

GP Analysis Cubes Connection Error Fix

Mon, 21 Jun 2010 18:10:29 GMT

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.

Currency Formatting in Analysis Cubes for Excel

Thu, 17 Jun 2010 18:13:54 GMT

Fellow bloggers Dwight Specht a Mark Polino tackle the formatting of currency fields being pulled into Excel from Analysis Services. Dwight and Mark provide detail steps on how to overcome this issue, but fundamentally on how to get rid of it forever. Please stop by DynamicAccounting.net to read this very interesting article.

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

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

Mon, 21 Jun 2010 18:11:05 GMT

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 Cubes, Clustered Servers, and Named Instances

Mon, 21 Jun 2010 18:11:10 GMT

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.