Microsoft Dynamics GP: Installing Analysis Cubes

Exclusive offer: get 50% off this eBook here
Microsoft Dynamics GP 2010 Reporting

Microsoft Dynamics GP 2010 Reporting — Save 50%

Create and manage business reports with this Microsoft Dynamics GP 2010 Reporting book and eBook

$35.99    $18.00
by Christopher Liley | May 2011 | Enterprise Articles Microsoft

To celebrate the recent publication of the Microsoft Dynamics GP 2010 Reporting book, Packt is pleased to announce a series of attractive discounts on our wide range of Dynamics books. For more information click here.

The installation of Analysis Cubes for Excel is actually a quite simple product to install, but it is also very dependent on having the proper permissions to perform the installation as well as completing steps in a particular order. The installation process is made up of the following three steps, which we'll cover in this article by Christopher Liley, author of Microsoft Dynamics GP 2010 Reporting:

  • Pre-Installation Checklist
  • Installing the Server Configuration Wizard
  • Using the Server Configuration Wizard to Deploy the Cubes

 

Microsoft Dynamics GP 2010 Reporting

Microsoft Dynamics GP 2010 Reporting

Create and manage business reports with Dynamics GP

        Read more about this book      

(For more resources on Microsoft Dynamics, see here.)

Pre-installation checklist

Before proceeding with our installation of the Analysis Cubes, we need to review our Pre-installation checklist.

  • Review system requirements for server components – This includes determining which of the deployment configurations we will be using in our environment.
  • Review security requirements for installing and configuring the server components:
    • To install and configure the server components, the Windows user account must be in the Windows groups in the following chart, as described in the latest Analysis Cubes for Excel manual:

      Server Computer Windows Groups
      Microsoft Dynamics GP Server Computer Administrators
      SQL Server Computer for Microsoft Dynamics GP Company Databases All versions of SQL Server: Administrators
      SQL Server 2005: SQLServer2005MSSQLUser
      SQL Server 2008: SQLServerMSSQLUser
      Data Warehouse Database Server Computer (with Integration Services Installed) All versions of SQL Server: Administrators
      SQL Server 2005: SqlServer2005MSSQLUser; SQLServer2005DTSUser
      SQL Server 2008: SQLServerMSSQLUser; SQLServerDTSUser
      SQL Server Analysis Services Computer All versions of SQL Server: Administrators
      SQL Server 2005: SQLServer2005MSOLAPUser
      SQL Server 2008: SQLServerMSOLAPUser

    • Dynamics GP server must be given access to read and write data on the data warehouse database server if it is a different physical machine.
  • Gather information required for configuring the server components:
    • The Dynamics GP company databases that will be used to populate the data warehouse database
    • The name that will be used for the data warehouse database
    • The SQL server name or instance name that the data warehouse database will reside on
    • The locations of the data and log files for the SQL server instance
    • The SQL Server Integration Services packages to install
    • Whether to populate the warehouse database with detailed General Ledger transaction information for various modules or to use summary information
    • Determine whether to populate each cube with all transactions or to enter an earliest date to import from the company databases
    • Determine whether to include multicurrency information and if so, which reporting currency and exchange rate table to use for each company
    • Define a password for the Dynamics User SQL Server login account that will be used by the company databases to access the data warehouse database using SQL Server Authentication

Installing the Server Configuration Wizard

Now that we have ensured we have met all system requirements and obtained the necessary security permissions to perform the installation, we can proceed with installing the Server Configuration Wizard. The installation can be started from the Dynamics GP 2010 Installation Media or by downloading the Analysis Cubes Installer package from either the PartnerSource or CustomerSource portals.

Be sure that when downloading the package from these sites, you download the correct file whether you are on Dynamics GP 2010 RTM or Service Pack 1.

To install the Server Configuration Wizard, follow these steps:

  1. Locate the Microsoft_DynamicsGP11_AnalysisCubesServer_x86.msi file you downloaded and double-click the file to open the Dynamics GP Server Setup Wizard.

    Microsoft Dynamics GP 2010 Reporting

  2. Click Next to proceed.
  3. On the License Agreement window, select the I Agree radio button and click Next.

    Microsoft Dynamics GP 2010 Reporting

  4. Enter the path for the destination folder to install the server configuration wizard program to. The default path is C:\Program Files\Microsoft Dynamics\Analysis Cubes for Microsoft Dynamics GP Server\. Click Next.
  5. Click Next to complete the installation.
  6. Click Close to close the wizard.

Using the Server Configuration Wizard to deploy the cubes

Once we have installed the Server Configuration Wizard, we need to run the wizard to actually deploy the cubes. During the configuration wizard installation process, two files, named Microsoft.Dynamics.GP.AnalysisCubes.ConfigurationWizard2005.exe and Microsoft.Dynamics.GP.AnalysisCubes.ConfigurationWizard2008.exe are extracted to the destination folder. Shortcuts to these files, named Analysis Cubes Configuration Wizard for SQL Server 2005 and Analysis Cubes Configuration Wizard for SQL Server 2008 also are created on the desktop.

To use the Server Configuration Wizard to Deploy the Cubes, follow these steps:

  1. Browse to the folder location where the Server Configuration Wizard was installed, or run the Analysis Cubes Configuration Wizard from the desktop.

    (Move the mouse over the image to enlarge.)

  2. On the Data Warehouse Setup window, enter the values for the fields based on the information you gathered in the Pre-Installation checklist section.

  3. On the Microsoft Dynamics GP database selection window, enter your Dynamics GP server name and select each company database(s) that will be included in the Data Warehouse and click Next.

  4. On the Analysis Cubes Integration Setup window, select the modules to include in the data warehouse and whether to import transactions in detail or in summary. A cutoff date can also be selected to limit transactions that are loaded. Click Next.

 

Microsoft Dynamics GP 2010 Reporting Create and manage business reports with this Microsoft Dynamics GP 2010 Reporting book and eBook
Published: April 2011
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

 

        Read more about this book      

(For more resources on Microsoft Dynamics, see here.)

  1. On the Analysis Services Cube Setup window, select the Analysis Services SQL Server name defined in the Pre-Installation checklist and choose which cubes to install. Click Next.

  2. On the Analysis Cubes Multicurrency Information window, if there is a need to use a reporting currency for multicurrency, check the box to include multicurrency information. Choose the Reporting currency and then which Exchange table to use. Click Next.

  3. On the Analysis Cubes Scheduling Options window, choose whether to have the wizard create a job schedule or to install without a schedule. The job schedule can be set up at a later time.

  4. On the Analysis Cubes Installation Information window, review the information and click Install.

  5. The Analysis Cubes Installation Complete window will provide information about the SQL Server configuration changes that were made. Click Exit.

Though these are fairly detailed installation instructions, it is always recommended to download the latest Analysis Cubes User Guide from the PartnerSource or CustomerSource portals and check for any updates or other important information for the installation process.

Populating the data warehouse and processing the cubes

Once the server configuration is completed and the data warehouse is installed, three master SQL Server Integration Services packages will be installed for each company. These packages are what actually populate the data warehouse database with data from the Microsoft Dynamics GP company databases. To run these packages, connect to Integration Services in SQL Server Management Studio, browse to Stored Packages | MSDB | DynamicsGPWarehouse, and right click them to run them. The names of the master packages are as follows:

DynamicsGP_<source_db>_to_<warehouse_db>_PackageMaster

DynamicsGP_<source_db>_to_<warehouse_db>_Run_GL_Budgets_Master

DynamicsGP_<source_db>_to_<warehouse_db>_Run_AA_Budgets_Master

Where <source_db> is the name of the Dynamics GP company (there will be these three packages for each company you selected to install) and <warehouse_db> is the name of the data warehouse database.

The master package for Analytical Accounting should only be run if Analytical Accounting budget data should be included in the data warehouse.

If during the installation a scheduled job was not created to run the packages, this should be created now.

Once the master packages have been run and the schedule created, the OLAP processing package needs to be run in SQL Server Management Studio to process the cubes. The name of the OLAP processing package is DynamicsGP_<warehouse_db>_OLAP_DB_<server name>_<analysis services database> where <warehouse_db> is the name of the data warehouse database. To run this package, connect to Integration Services in SQL Server Management Studio, browse to Stored Packages | MSDB | DynamicsGPWarehouse, and right click the OLAP package and click run package and execute.

Granting security access to the cubes

Security to the cubes is controlled through the use of SQL Server Analysis Services security roles. Domain users or groups are assigned to one or more security roles, each of which allows a different level access to a single cube, multiple cubes, or specific objects within a cube. Although custom security roles can be set up in an Analysis Services database, the Analysis Cubes installation actually creates several default security roles for us. For us, then, allowing users to access the cubes is simply a matter of deciding which roles the users should be assigned to.

Accessing and modifying the security roles is fairly straightforward. In this example, we'll use a computer that has SQL Server Management Studio installed on it to accomplish this:

  1. Open SQL Server Management Studio by selecting Start | Programs | Microsoft SQL Server | SQL Server Management Studio.
  2. In the Connect to Server window, select Analysis Services as the Server Type.
  3. In the Server Name field, enter the name of the server containing the Analysis Services database.
  4. The Authentication method should be set to Windows Authentication. The Windows account with which we are logged should be a member of the Analysis Services server role to ensure that we can connect properly. For best results, use the account that was used to perform the Analysis Cubes installation.
  5. Once the connection is made, expand the Databases node.
  6. Find and expand the node for the Analysis Services database created by the installation. If the default name was kept, then expand the Dynamics GP Analysis Cubes node.
  7. Expand the Roles node. This will show us a list of the available security roles in this SSAS database.

We can see that a single role exists for each cube that was installed. Essentially, assigning a Windows user or group to a specific security role will allow that user or members of that group read-only access to the cube identified by the role name.

Microsoft Dynamics GP 2010 Reporting

For example, to grant a user access to the Financials cube, we can double-click the role called Financials Cube – read only. Selecting the Membership tab brings us to a window from which we can select to add users and groups who should have read only access to the Financials cube. That's all it takes!

One recommendation that we can make for organizations that will have numerous users accessing the cubes is to utilize Windows groups. If we have ten users who will need access to six cubes, that will require us to add each user to each separate security role. Six assignments per user multiplied by ten users can quickly add up to a time-consuming chore that is difficult to maintain going forward!

Instead, we recommend setting up a Windows group called AnalysisCubesReadOnly or similar. By adding the ten users to this group via Active Directory, we can then just add this single group to each of the cube security roles. This greatly reduces the time required to maintain our cube security!

Summary

Analysis Cubes for Excel is a highly customizable product with several different components that must be maintained on the back-end. This article has covered the numerous aspects related to installing and setting up Analysis Cubes for first time use.


Further resources on this subject:


Microsoft Dynamics GP 2010 Reporting Create and manage business reports with this Microsoft Dynamics GP 2010 Reporting book and eBook
Published: April 2011
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

About the Author :


Christopher Liley

Chris Liley is a Principal Consultant with Microsoft Partner I.B.I.S., Inc.in Norcross, GA and a Microsoft Certified Information Technology Professional for Dynamics GP.  He is a graduate of Georgia State University with a B.B.A in Accounting.  Chris has worked with Dynamics GP since 2001.

Chris’ experience ranges from financial analysis, software implementations, data conversions, integrations to designing and developing customizations in both the functional and technical area of consulting for Dynamics GP.  Chris also has extensive experience designing Business Intelligence solutions.

Books From Packt


Microsoft Dynamics GP 2010 Implementation
Microsoft Dynamics GP 2010 Implementation

Microsoft Dynamics GP 2010 Cookbook
Microsoft Dynamics GP 2010 Cookbook

Microsoft Dynamics Sure Step 2010
Microsoft Dynamics Sure Step 2010

Microsoft Dynamics NAV Administration
Microsoft Dynamics NAV Administration

Microsoft Dynamics AX 2009 Administration
Microsoft Dynamics AX 2009 Administration

Microsoft Dynamics NAV 2009 Programming Cookbook
Microsoft Dynamics NAV 2009 Programming Cookbook

Microsoft Dynamics NAV 2009 Application Design
Microsoft Dynamics NAV 2009 Application Design

Implementing Microsoft Dynamics NAV 2009
Implementing Microsoft Dynamics NAV 2009


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
X
h
N
d
H
G
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software