iMIS Analytics

iMIS Analytics brings true business intelligence to your iMIS solution, giving you the power to understand and analyze the performance of your organization in ways that are tedious or impossible with standard reporting tools.

The information that you need to identify and respond to performance issues, market trends, and competitive forces is there in your iMIS database, but the sheer volume and variety of data makes it difficult to organize this information. For example, how do you determine whether a sharp revenue drop is just a one-time event in a specific region or is an indication of a larger trend that warrants a massive change in the budget?

iMIS provides many useful reports, and you have probably had many custom reports designed to help you see trends or to investigate performance issues. Unfortunately, it's not practical to continually develop new reports to deal with time-critical issues as they arise, which means that you are often making important decisions based on incomplete information.

Other issues that affect your ability to analyze your organization's performance include the following problems:

  • Frequent reports and queries by your executives and managers can drain the system of resources for your normal business activities.
  • Some of the information in your iMIS database is always current data, such as a customer name or address. However, if a customer address changes, for example, reports and queries cannot easily show you the historical addresses.

You might already know that implementing a data warehouse and online analytical processing (OLAP) database can solve these problems, but such an undertaking can be difficult and costly.

Analytics is a turn-key business intelligence solution for your iMIS system. Analytics creates a data warehouse and OLAP database that is tailored specifically for your iMIS database. Every night, your data is automatically extracted from the iMIS database and stored in the data warehouse so that you have a complete historical record of all your iMIS data. The OLAP database contains one iMIS cube comprising eight measure groups that are designed to give you the best combination of multi-dimensional views into your iMIS information, and the cube is automatically updated every night with the information from your data warehouse.

You use an OLAP viewer to analyze the information that is stored in the cube, which is a very fast and flexible process that doesn't require knowledge about designing reports or performing SQL queries. This approach eliminates the need to run reports on a daily basis for manual comparison. Instead, you have instant access to key performance indicators, can easily compare multiple information categories, and can easily drill down into any information category. Because you are examining the OLAP database instead of reporting and querying against your iMIS database, your organization's normal business activities are relatively unaffected.

Analytics can be configured to include certain types of custom demographics that you have added to your iMIS solution, making these custom demographics available in your data warehouse and OLAP database.

  • Extremely fast access to important business performance information
  • Easy access to all types of charts, graphs, and metrics
  • Great visuals for presentations
  • Dynamic charting and analysis of key performance indicators
  • Power to manage and plan more effectively
  • Ability to add your custom iMIS demographics
  • Less reliance on IT Staff for reports

Analytics does not include predictive/forecasting capabilities or pattern analysis. These capabilities can be achieved by using a predictive tool that is compatible with Microsoft Analysis Services, which is beyond the scope of ASI support.

Although you can use any OLAP viewer that is compatible with Microsoft Analysis Services to browse and report on the iMIS cube created by Analytics, ASI recommends using Microsoft Excel.

iMIS Analytics can be installed without any customization, and you will have access to many useful key performance indicators by using your preferred OLAP viewer. You don't need to understand data warehousing or online analytical processing (OLAP) to gain instant value from using Analytics.

  • If you understand some basic concepts, however, you can take advantage of the full power of Analytics.
  • If your executives and managers understand the basic elements of an OLAP cube, they can use OLAP viewers to create any view of iMIS data that they need.
  • If your IT staff understands some basic data warehousing concepts and the general architecture of Analytics, they can:
    • Determine how to implement Analytics as a data mart in the bus architecture of a larger enterprise data warehouse solution.
    • Determine which of your custom iMIS demographics (custom fields) should be added to the data warehouse to provide you with even stronger business intelligence than that offered by a non-customized implementation of Analytics.

In smaller organizations, iMIS Analytics might be the only data warehouse providing you with business intelligence. In larger organizations with several major databases besides your iMIS database, the data warehousing experts in your organization can implement Analytics as a data mart in the bus architecture of a larger enterprise data warehouse solution (per the Kimball model). Describing this type of data mart implementation is beyond the scope of this document, because such implementations are highly customized solutions. A data mart implementation is possible, however, because the Analytics data warehouse uses dimensional models and a star schema, and because the Analytics OLAP cube has been designed around conformed dimensions and conformed measures (facts). Your data warehousing experts can rely on documentation for Microsoft Analysis Services or any related third-party documentation to be applicable to the design of the Analytics data warehouse and analysis database.

Whether Analytics is implemented as your only data warehouse or as a data mart, there are major decisions that you must make when implementing Analytics:

  • Should the product be installed in a two-tier or single-tier server configuration?
  • Which of your custom iMIS demographics (custom fields) should be added to Analytics?
  • Which type of slowly-changing dimension (SCD) should you use for each custom demographic?

Analytics server components can be implemented on a single tier with your iMIS database, or spread across two hosts in a two-tier configuration:

  • Two-tier: The Analytics data warehouse and analysis database are located together on one SQL Server host (the Analytics host), and your iMIS database is located on a different SQL Server host (the iMIS host). Both the Analytics host and the iMIS host must be members of the same domain.
  • Single-tier: The Analytics data warehouse and analysis database are located on the same SQL Server host that contains your iMIS database. In this configuration, the Analytics host is also the iMIS host.

The single-tier configuration is the easiest to install and use, and generally won't affect performance of your iMIS database unless you have many people constantly using the analysis database. The following information about the three databases involved in an Analytics implementation can help you determine whether a two-tier or single-tier configuration is best for your organization:

  • iMIS database
    • current data stored in relational tables
    • Your iMIS database contains some historical records of things like donations, purchases, etc. Much of the information in your iMIS database, however, is always current data. For example, if a product description or customer's address changes, the old product description or the old customer address is not saved in the iMIS database.
  • data warehouse
    • historical data stored in dimensional tables using a star schema
    • The Analytics data warehouse is a special database that contains a copy of all your iMIS data. The data warehouse does not use relational tables, but instead structures your data in a dimensional model using a star schema. Because much of the copied data comprises slowly changing dimensions that preserve history (Type 2 SCDs), the data warehouse will grow over time, so space requirements for growth are a factor in determining whether to implement a two-tier or single-tier configuration. However, since the data warehouse is used only during the extraction, transform, and load (ETL) process during off-peak hours, the performance impact on the iMIS host in a single-tier configuration is negligible.
  • analysis database - OLAP cube
    • OLAP cube derived from data warehouse contents, using conformed dimensions and conformed measures (facts)
    • The Analytics analysis database contains a copy of the information from the data warehouse, transformed into a cube in an OLAP database. As with the data warehouse, the analysis database will grow over time, so space requirements for growth are a factor in determining whether to implement a two-tier or single-tier configuration. Unlike the data warehouse, this database might be used heavily during peak hours of the business day. However, OLAP viewers do not generate heavy SQL activity, so the performance impact on the iMIS host in a single-tier configuration is minimal.

The Customers feature of iMIS has a feature called the iMIS Customizer, which enables you to create custom tables for use in custom window tabs that capture your own demographic information about each customer. With some limitations, you can configure your implementation of Analytics to include your custom demographic information about each customer in the data warehouse and analysis database. The following limitations apply to custom iMIS demographics:

  • Fields from custom tables that are defined to allow multiple table rows for a single customer ID (multi-instance custom tables) cannot be included in Analytics.
  • Fields from single-instance custom tables are added as attributes to the Customer Ext Dim dimension.

Examining these limitations in more detail can help you plan how to configure Analytics to track custom iMIS demographics (see Configuring Analytics to track custom iMIS demographics).

A multi-instance custom table is one for which the Multiple Instances Allowed checkbox was selected when creating the custom table with the iMIS Customizer. In a multi-instance custom table, you can have multiple records for the same customer ID. For example, you might want to send out annual surveys to each of your customers and track the survey results in a custom table that is viewable on a custom Surveys tab in the Manage Customers window. In this scenario, the Surveys tab for a specific customer would show one row for each year's survey results.

In a single-instance custom table, by contrast, there would never be multiple records for the same customer ID. For example, you might have created a custom table that tracks general demographic information for customers, such as their spouse's name and the school they attended. In this scenario, there would never be more than one row needed per customer ID to store the demographic information.

Because multi-instance custom tables can have more than one record per customer ID, Analytics cannot determine which of those multiple records to display as attributes for a specific customer when you are examining the detail information for that customer in an OLAP viewer. Therefore, only fields from single-instance custom tables can be included in your implementation of Analytics.

The other limitation of custom tables is that the fields from these tables are essentially extended attributes of a specific customer ID, instead of being a category of business information that could be considered a dimension member in its own right. Therefore, the custom fields that you add to Analytics are not treated as members (levels) in a dimension that you can directly graph on a chart in an OLAP viewer. Instead, you can view the custom fields only as attributes of a specific customer, once you have drilled-down far enough into a view to display specific customers.

The most difficult decision to make when configuring Analytics to track your custom demographics is which type of slowly changing dimension (SCD) to use for each demographic. In a data warehouse, the measures (facts) change very rapidly, but by comparison, the data in your dimensions change slowly. For example, a custom demographic such as staff size is likely to change over time for a given customer.

Analytics supports two types of slowly changing dimensions, as well as giving you the option to not use a slowly changing dimension for demographics that never change over time. There are two questions you should consider for each demographic that you want to track in Analytics:

  • Is this demographic a value that might change over time?
    • If the answer is no, then you should configure the demographic to not use a slowly changing dimension.
    • If the answer is yes, then you should consider the following question to determine which type of slowly changing dimension to use.
  • Do I need to preserve a history of the different values of this demographic as it changes over time, or do I care only about the most current value of this demographic?
    • If you need to preserve the historical values of a demographic, you should use a Type 2 SCD.
    • If you need only the current value of a demographic, you should use a Type 1 SCD.

The following information about slowly changing dimensions can also help you determine which SCD type to use for each custom field:

  • Type 2
    • Preserves history
    • When changes occur to a Type 2 SCD, a new record is added with the new attribute information, preserving records of the old attribute information. Type 2 SCDs track history very efficiently because they create new records only when the attribute information changes. However, because Type 2 changes add new records, they can significantly increase the size of your data warehouse.
  • Type 1
    • Overwrites history
    • When changes occur to a Type 1 SCD, no new records are created. Instead, the change overwrites the existing attribute with new information. The old information is lost forever.
  • Type 0
    • Use no SCD
    • A Type 0 SCD is a special value used only in Analytics to indicate that the custom demographic is not a slowly changing dimension. The demographic is a value that will never change for a given customer. The advantage of using a Type 0 SCD for custom demographics that never change is that the data warehouse requires less overhead to maintain that demographic.

The iMIS cube in the analysis database contains the following dimensions and measures. Using an OLAP viewer with iMIS Analytics allows you to easily browse the dimensions and measures, and create any needed view of your iMIS business performance.

  • Campaign
  • Channel
  • Customer
  • Customer Ext Dim (see Configuring Analytics to track custom iMIS demographics)
  • Date
  • GL Account
  • Group Enroll Type
  • Group Expire Type
  • Group
  • Issue Status
  • Issue Type
  • Opportunity Status
  • Opportunity Type
  • Organization
  • Product
  • Promotion
  • Response Media
  • Sales Location
  • Sales Order Details
  • Sales Team
  • Shipper
  • Warehouse
  • GL Transaction Fact
    • GL Transaction Fact Count
    • Transaction Amount
  • Group Enrollment Fact
    • Count
    • Group Enrollment Fact Count
  • Group Stats Fact
    • Active Count
    • Downgrade
    • Dropped
    • Lapsed
    • New
    • Reinstate
    • Renew
    • Transfer In
    • Transfer Out
    • Upgrade
  • Inventory Level Fact
    • Cost
    • Inventory Level Fact Count
    • Quantity On Hand
    • Quantity On Order
    • Quantity Received
    • Quantity Shipped
    • Quantity Sold
  • Issue Fact
    • Issue Fact Count
    • Service Provider Key
    • Time to Resolve
    • Total Effort
  • Opportunity Fact
    • Component Price1
    • Component Price2
    • Component Price3
    • List Price
    • Opportunity Fact Count
    • Probability
    • Purchase Budget
    • Units
  • Pledge Fact
    • Adjustment Amount
    • Amount Paid
    • Open Balance
    • Pledge Amount
    • Pledge Fact Count
  • Sales Fact
    • Cost Amount
    • Fair Market Value
    • Gross Profit
    • Quantity
    • Sales Amount
    • Sales Fact Count

Business intelligence solutions can be complicated to implement because they require:

  • Understanding online analytical processing (OLAP) concepts and implementation.
  • Setting up special OLAP databases: the data warehouse and the analysis database.
  • Configuring and scheduling Integration Services packages that periodically extract data from your iMIS database, load the data into your data warehouse, and transform the contents of your data warehouse into cubes in the analysis database.
  • Learning and using OLAP viewer software to analyze the cubes in your analysis database.

Depending on your organization's experience with implementing business intelligence solutions, you might involve ASI Consulting to ensure you achieve your business intelligence goals quickly.

iMIS Analytics comprises some program files and OLAP database components. The OLAP database components can be installed to the same SQL Server host that contains your iMIS database (a single-tier configuration), or they can be installed to a separate SQL Server host (a two-tier configuration).

  • The server host on which the Analytics program is installed is the iMIS application server (appserver).
  • The SQL Server host on which the OLAP database components reside is the Analytics host.
  • The SQL Server host on which the iMIS database resides is the iMIS host.
  • If you installed the OLAP database components on the same host as the iMIS database, the Analytics host is also the iMIS host.

Analytics is installed as an optional component by performing with the setupAnalytics.exe installer on an application server host.

  • iMIS application server hosts must meet all stated application server requirements for iMIS:
    • 10/100 Mbps network speed (or faster) connection to all related iMIS application server hosts.
    • Microsoft SQL Server, tools only.
    • Microsoft SQL Server Integration Services (SSIS) installed. This is required by the Integration Services packages used by Analytics.
    • If Analytics is installed on a separate database server host from the iMIS database server host, both hosts must be members of the same Windows domain, and both hosts must be configured to allow network DTC access. This is required by the Integration Services packages used by Analytics. For more information, see Enabling network DTC access for multi-server configurations.
  • For best results, use the same versions of Windows Server and SQL Server on both the iMIS and Analytics host servers.
  • The Analytics host must meet all database server requirements for iMIS, as well as these more specific requirements:
    • 10/100 Mbps network speed (or faster) connection to all related iMIS application server hosts
    • Microsoft SQL Server
    • Microsoft Analysis Services, which must be installed in the default location: C:\Program Files\Microsoft Analysis Services
    • Microsoft SQL Server Business Intelligence Development Studio
    • The database server host that contains the SQL Server instance in which you want to create your Analytics data warehouse and analysis database must also have a default instance of SQL Server installed on it, and that default instance must have Microsoft SQL Server Integration Services (SSIS) installed. This is required by the Integration Services packages used by Analytics.
    • If Analytics is installed on a separate database server host from the iMIS database server host, both hosts must be members of the same Windows domain, and both hosts must be configured to allow network DTC access. This is required by the Integration Services packages used by Analytics. For more information, see Enabling network DTC access for multi-server configurations.
  • The iMIS host must meet all stated database server requirements for iMIS:
    • 10/100 Mbps network speed (or faster) connection to all related iMIS application server hosts.
    • Microsoft SQL Server, tools only.
    • Microsoft SQL Server Integration Services (SSIS) installed. This is required by the Integration Services packages used by Analytics.
    • If Analytics is installed on a separate database server host from the iMIS database server host, both hosts must be members of the same Windows domain, and both hosts must be configured to allow network DTC access. This is required by the Integration Services packages used by Analytics. For more information, see Enabling network DTC access for multi-server configurations.
  • For best results, use the same versions of Windows Server and SQL Server on both the iMIS and Analytics host servers.
  • The iMIS database must contain the license key for Analytics.
  • OLAP viewer software; Microsoft Excel is recommended.
  • Processor speed, memory, and disk space sufficient to meet the requirements of your OLAP viewer.

You can install iMIS Analytics in a two-tier or single-tier configuration. See Basic concepts for your IT staff to determine which configuration is best for your implementation of Analytics.

Note: BUILTIN\Administrators must be a valid login for your SQL Server.

  1. Log in to SQL using the sa account.
  2. Open SQL server management tools.
  3. Select the SQL service, and go to Security > Logins. Verify that BUILTIN \Administrators is a listed login.
  4. Right-click on BUILTIN \Administrators and go to Properties > Server Roles.
  5. Verify that BUILTIN \Administrators has the following roles:
    • public
    • sysadmin

If BUILTIN\Administrators is not listed as a SQL Server login, do the following:

  1. Select the SQL service, and go to Security > Logins.
  2. Right-click on Logins and go to New Login.
  3. Enter BUILTIN\Administrators in the Login name field.
  4. Click OK.
  5. Right-click on BUILTIN \Administrators and go to Properties > Server Roles.
  6. Verify that BUILTIN \Administrators has the following roles:
    • public
    • sysadmin
  • Make sure that the Analytics host, iMIS host, and iMIS application server all meet the Analytics system requirements.
  • Note the SQL connection information for the iMIS database on the iMIS host, and for the SQL environment on Analytics host, if you are installing a two-tier configuration.

    • Note: When installing Analytics, the value local cannot be used for the service name; the actual service name must be used, for example, sqlser123 instead of local. 
    The install process will complete if you use local; however, analytics jobs will not run without errors.

  • Make sure your iMIS product license keys include the key for Analytics.

Installing Analytics requires extra steps if you are upgrading iMIS and/or Analytics

Scenario

Installation path

Neither iMIS nor Analytics has been installed in your organization before, so you are installing both products at the same time.

Perform a standard installation of iMIS. After iMIS is installed, install analytics using the setupAnalytics.exe installer.

Analytics has never been installed before, but you currently have iMIS 15.1.3 or higher installed. You are therefore installing Analytics and upgrading iMIS.

Perform these tasks in the order listed:

  • Perform a standard upgrade of iMIS, choosing a Complete installation.

    •  

    Warning! Do not install Analytics now!

  • Verify that iMIS is working properly.
  • Install analytics using the setupAnalytics.exe installer.
  • Run the package (see Manually running the Update iAnalytics package) to populate the new analysis database installed by this latest release of Analytics.

You have both Analytics 1.0 and iMIS 15.1.3 or higher installed. You are therefore upgrading both products.

Warning!
You must do so in two separate passes.

Perform these tasks in the order listed:

  • Upgrade the SQL Server environment on your Analytics host as needed.
  • Ensure that the Analytics host meets all requirements.
  • Perform a standard upgrade of iMIS.

    • Warning!
    Do not install Analytics now!

  • Verify that iMIS is working properly.
  • Install Analytics using the setupAnalytics.exe installer.
  • Migrate your data warehouse (see Migrating the Analytics 1.0 data warehouse) to the newly installed one.
  • Run the package (see Manually running the Update iAnalytics package) to populate the new analysis database.
  • Verify that Analytics is working properly.
  • Delete the old data warehouse and analysis database from your SQL Server environment. Only the new iMIS_Analytics data warehouse and iAnalytics analysis database should remain.

If you are using a separate host machine for Analytics, you need to set all of the SQL Server 2012 services to log on with the Local System account.

  1. After you install SQL Server 2012/2014, open Start > Administrative Tools > Services.
  2. Set Log On > Log on As to use the Local System account on each of the SQL Server  services:
    • SQL Server
    • SQL Agent
    • Analysis Service
    • Integration Services
  3. Restart all of the services.
  1. On your application server host, run the product setupAnalytics.exe.
  2. A message indicating that a new instance of iMIS Analytics will be installed on your computer is displayed. Click Next.
  3. The Destination Folder page is displayed. Click Next to accept the default installation path (recommended).
    • Select Yes to accept the installation path given by the installer.
    • Select No to modify the installation path given by the installer.

    4. Note: If an existing directory that contains files for an iMIS install is selected, the installation will overwrite all files and the specified installation of iMIS will no longer function.

  4. The Database information page is displayed. Enter the database connection information:
    1. In the Source iMIS SQL Server instance, select a SQL Server instance.

      2. Note: Do not use (local) for the SQL Server instance.

    2. In Database name, enter a name for your iMIS database.
    3. For Source SQL Login, enter the sysadmin login for the SQL Server instance that is hosting the iMIS database.
    4. For Password, specify the password for the accounts you entered. If left blank, the install fails.
    5. In iMIS Analytics SQL Server instance, select a SQL Server instance. If the iMIS Analytics SQL Server instance is the same as the Source iMIS SQL Server instance, the SQL Login and Password will populate in the fields below.
    6. Specify the Analytics Database name.
    7. Specify the Analysis database name.
  5. Click Next. A Ready to Install window is displayed.
  6. (optional) Enable the option Display debug information to review the installation information.
  7. When you are ready to install your new iMIS Analytics instance, click Install. The installation process can take several minutes.
  8. Click Finish when the installation process is complete.

When upgrading from iMIS Analytics 1.0 to the current release of Analytics, as described in Installing Analytics, you must manually migrate your data warehouse tables from Analytics 1.0 into the new iMIS_Analytics data warehouse that is created when you install the current release of Analytics.

Warning!
Certain tables and views must not be imported from the older data warehouse. These objects are listed in the following procedure.

  • All Analytics system requirements for the Analytics host must be in place.
  • The following procedure must be performed on the Analytics host.
  1. Using Microsoft SQL Server Management Studio, connect to the Database Engine on the Analytics host.
  2. In the Object Explorer pane, expand the Databases folder.
  3. Right-click the iMIS_Analytics database and choose Tasks > Import Data. The welcome page of the SQL Server Import and Export Wizard appears.
  4. Click Next. The Choose a Data Source page appears.
  5. Using the Server name and Database fields, specify your Analytics 1.0 data warehouse, then click Next. The Choose a Destination page appears.
  6. Click Next without changing any values. The Specify Table Copy or Query page appears.
  7. Select Copy data from one or more tables or views, then click Next. The Select Source Tables and Views page appears.
  8. In the first column, select every table except for the following:
    • DWMColumn
    • DWMTable
    • DWParameters
  9. Ensure that you have not selected any views in the list, then click Next. The Save and Execute Package page appears.
  10. Ensure that the Execute immediately checkbox is selected and click Finish twice to begin importing the selected tables from the old data warehouse. The wizard displays status while it imports the tables.

Due to stronger security on network communications enabled by recent service packs on Microsoft server operating systems (and present by default in newer server operating systems), network Distributed Transaction Coordinator (MSDTC) access might be disabled on one or both of the SQL server hosts in a multi-server configuration of iMIS Analytics.

If you have installed Analytics in a multi-server configuration, you must ensure that both the iMIS host and the Analytics host are configured to allow network DTC access.

Ensure that both your Analytics host and your iMIS host meet all system requirements (see Analytics system requirements).

  1. On the Analytics host, choose Start > Control Panel > Administrative Tools > Component Services. The Component Services window appears.
  2. In the console tree, click Component Services, then expand the Computers sub-folder. The My Computer icon appears in the console tree.
  3. Right-click the My Computer icon and choose Properties.
  4. On the MSDTC tab, click Security Configuration. The Security Configuration window appears.
  5. In the Security Settings area, ensure that all of the following checkboxes are selected:
    • Network DTC Access
    • Allow Remote Clients
    • Allow Remote Administration
    • Allow Inbound
    • Allow Outbound
    • Enable XA Transactions
  6. In the Transaction Manager Communication section, ensure that the Mutual Authentication Required option is selected.
  7. Click OK. A confirmation message to stop and restart the MSDTC service appears.
  8. Click Yes, then click OK at all further messages and windows.
  9. Repeat this process on the iMIS host.

When you install iMIS Analytics, the setup program creates a scheduled Integration Services package named UpdateinstanceName_analysisDBName on the Analytics server host. The package extracts data from the iMIS database, updates the Analytics data warehouse, and updates the Analytics cubes.

The package is scheduled by default to run every night at 4:00 a.m. server time, but you can modify the schedule by using the procedure described in Changing the scheduled run time of the Update iAnalytics package.

You might occasionally need to manually execute this package during the business day, such as after first installing Analytics (to populate the data warehouse and cubes), or when configuring Analytics to track custom iMIS 10 demographics (see Configuring Analytics to track custom iMIS demographics).

Analytics must be properly installed and configured. See Installing Analytics for more information.

Note: Depending on the size of your iMIS database, this process can take a long time to complete.

  1. Launch SQL Server Management Studio.

    2. Note: For SQL Server 2012, you must run as administrator: right-click SQL Server Management Studio and select Run As Administrator.

  2. Connect to the Database Engine on the Analytics host.
  3. In the Object Explorer pane, browse to SQL Server Agent > Jobs. The Summary Page displays the scheduled jobs on the Analytics host.
  4. Right-click the UpdateinstanceName_analysisDBName job and choose Start Job at Step. The Start Jobs window appears and displays status while it runs the job. When the job has completed successfully, the Start Jobs window displays a success message.

If the facts in the iMIS cube have no data or if the data does not match the contents of the data warehouse after running the Update iAnalytics package (evidenced by the Briefing Book showing no data or incorrect or old data), you must run two stored procedures to fix the problem. This needs to be done only once.

  1. Using Microsoft SQL Server Management Studio, connect to the Database Engine on the Analytics host.
  2. Use the query window to execute the following two stored procedures, with the listed parameters:
    exec asi_dwSetLAll 1
exec asi_DWSInitDB
  3. Re-run the Update iAnalytics package.

The Integration Services package UpdateinstanceName_iAnalytics is scheduled by default to run every night at 4:00 a.m. server time, provided that the SQL Server Agent is started. This package extracts data from the iMIS database, updates the iMIS Analytics data warehouse, and updates the Analytics cubes. You can specify any schedule for this package.

Note: Depending on the size of your iMIS database, this process can take a long time to complete.

Analytics must be properly installed and configured. See Installing Analytics for more information.

  1. Using Microsoft SQL Server Management Studio, connect to the Database Engine on the Analytics host.
  2. In the Object Explorer pane, browse to SQL Server Agent > Jobs. The Summary Page displays the scheduled jobs on the Analytics host.
  3. Right-click the UpdateinstanceName_iAnalytics job and choose Properties. The Job Properties window appears.
  4. On the Schedules page, from the Schedule list, select UpdateinstanceName_iAnalytics.
  5. Click Edit. The Job Schedule Properties window appears, displaying the current schedule for the UpdateinstanceName_iAnalytics job.
  6. Modify the schedule and click OK several times to close all open dialogs.
  7. If the SQL Server Agent is not already started, right-click the SQL Server Agent object and choose Start.

To enable people who aren't SQL Server administrators to view the Analytics analysis database by using the Analytics Briefing Book, you must enable the Everyone role in SQL Server to have read access to the analysis database.

Analytics must be properly installed and configured. See Installing Analytics for more information.

  1. Using Microsoft SQL Server Management Studio, connect to Analysis Services on the Analytics host.
  2. In the Object Explorer pane, expand the tree to view the contents of the Databases > iAnalytics > Roles folder.
  3. Right-click the everyone role and choose Properties. The Edit Role window appears.
  4. On the Data Sources page, in the iAnalytics data source row, specify Read access and select the Read Definition checkbox.
  5. On the Cubes page, in the iMIS cube name row, specify Read access, Drillthrough local cube/drillthrough access, and select the Process checkbox.
  6. Click OK to save your changes.

You can track custom iMIS demographics (custom fields) as attributes in the iMIS Analytics customer dimensions. Only fields from single-instance custom tables can be tracked in Analytics. If there is demographic data in multi-instance custom tables that you want to track in Analytics, you must redefine the data in iMIS as a single-instance custom table. Consult your Authorized iMIS Solution Provider (AiSP) for assistance with this process.

  • Read Custom iMIS Demographics and Slowly Changing Dimensions in Basic concepts for your IT staff.
  • Perform this procedure on the iMIS application server where Analytics is installed.
  • If SQL Server Business Intelligence Development Studio (or any Visual Studio-based window) is currently open on the host, close the window before starting this procedure. If Visual Studio is left open during the early parts of this procedure, you might have difficulty viewing newly-added custom fields.

Warning!
The Analytics Configuration tool used in this procedure is meant primarily for advanced development and customization of Analytics. Using this tool for any purpose other than the specific goals of this procedure is unsupported at this time.

  1. On the iMIS application server where Analytics is installed, open your Analytics folder, such as C:\Program Files\ASI\iMIS\Analytics.
  2. Double-click AnalyticsConfiguration.exe. The Analytics Configuration window appears.
  3. In the i15.UseCustomerExtensions row, set the Value to 1.
  4. Click File > Save.
  5. Choose Tools > Mappings. The Analytics Mappings window appears.
  6. From the Table List, select the CustomerExt dimension table. The editor grid displays all of the fields from single-instance custom tables in the iMIS database that can be tracked in Analytics.
  7. Select the fields to track and specify their slowly changing dimension (SCD) type:
    • In the Active column, select the checkbox corresponding to each custom field that you want to track in Analytics.
    • In the SCD Type column, specify which slowly changing dimension type to use for each field that you marked as Active:
      • Enter 2 to specify Type 2 (preserves history). Use this slowly changing dimension (SCD) type if you want to preserve a history of older attribute values in the data warehouse (by adding new records) when the custom field is changed in iMIS.
      • Enter 1 to specify Type 1 (overwrites history). Use this SCD type if you want to always overwrite the current attribute value in the data warehouse when the custom field is changed in iMIS.
      • Enter 0 to never update records. Use this special value for attributes that you never want to update from its original value when first extracted into the data warehouse.
  8. Click Save to save the changes that you have made to the CustomerExt table definition.
  9. Close the Analytics Mappings window.
  10. Connect to the Analytics analysis database
    • Choose Start > All Programs > Microsoft SQL Server > SQL Server Business Intelligence Development Studio. The Visual Studio window appears.
    • Choose File > Open > Analysis Services Database. The Connect To Database window appears.
    • In the Server field, specify the SQL Server instance that contains your Analytics analysis database. This field is case-sensitive.
    • From the Database drop-down list, select your analysis database (named iAnalytics by default).
    • Click OK. The Solution Explorer pane displays the contents of your analysis database.
  11. Update the Analytics data warehouse structure to incorporate the new iMIS custom fields.
    • In the Solution Explorer, in the Data Source Views folder, right-click the iAnalytics data source view and choose Open. The iAnalytics data source view appears.
    • In the toolbar area for the data source view, click the Refresh Data Source View icon. The Refresh Data Source View window appears. All of the iMIS custom fields that you marked as Active in the Analytics Mappings window should be listed as Added for the DWCustomerExtDim table.
    • Click OK.
    • The DWCustomerExtDim table in the data source view is updated to include new columns for each of the iMIS custom fields.
    • Choose File > Save All to save the changes to the data source view and to update the Analytics data warehouse structure accordingly.
  12. Update the Analytics analysis database dimensions to incorporate the new columns that you added to the data warehouse.
    • In the Solution Explorer, in the Cubes folder, right-click the iMIS cube and choose Open. The Cube Structure tab for the iMIS cube appears.
    • In the toolbar area for the Cube Structure tab, click the Add Cube Dimension icon. The Add Cube Dimension window appears.
    • From the Select dimension list, select Customer Ext Dim and click OK. The Customer Ext Dim dimension is added to the Dimensions lists.
    • In the Hierarchies tab of the Dimensions list, right-click the Customer Ext Dim dimension and choose Edit Dimension. The Dimension Structure tab for the Customer Ext Dim dimension appears.
    • In the Data Source View, locate the DWCustomerExtDim data source and scroll the list of columns in the data source until the new iMIS custom fields are visible.
    • For each column in the DWCustomerExtDim data source that corresponds to the new iMIS custom fields that you want to track in Analytics, perform the following steps.
      1. Multi-select all of the columns (hold Ctrl while selecting each column).
      2. Right-click the selected columns and choose New Attribute from Column. The columns are added to the Attributes list for the Customer Ext Dim dimension.
    • Choose File > Save All to save the changes to the Customer Ext Dim dimension and the iMIS cube in the analysis database. The Microsoft Visual Studio window appears, prompting you to reprocess various objects in the analysis database.
    • Click Yes. The Process Object(s) window appears.
    • Click Run. The Process Progress window appears and displays status while the analysis database is reprocessed.
    • When the reprocessing has completed and the Status field displays Process succeeded, close all SQL Server  Business Intelligence Studio windows.
  13. Update the data warehouse and analysis database with the new custom fields from the iMIS database by manually running the Update iAnalytics package.
  14. Verify that the new custom fields are available in Analytics.
    • Open the iMIS Analytics.bbk file in ProClarity Professional. The Analytics data appears.
    • In the Briefing Book area, expand the Revenue folder and select the Revenue by Product Class view. The Revenue by Product Class view appears.
    • Choose View > Setup Panel. The Setup Panel appears.
    • Verify that the Customer Ext Dim dimension is listed in the Custom Hierarchies for the view.
    • Expand the Customer Ext Dim dimension and verify that the new custom fields from the iMIS database are listed as attributes in the dimension.
  15. Create a new view that uses one of the custom fields.
    • Ensure that the Revenue by Product Class view is still displayed, and the Customer Ext Dim dimension is still expanded in the Setup Panel.
    • Right-click an attribute in the Customer Ext Dim dimension that corresponds to one of the new custom fields and choose Move to Rows. The attribute appears at the top of the Rows list of the Setup Panel.
    • In the Rows list, select and drag the attribute from the top of the list to the bottom (immediately after the Product Class hierarchy).
    • Choose View > Apply Now. The table portion of the view changes to display new sub-rows within each of the product class rows, and the graph portion also changes accordingly to show more granular breakdowns of each product class based on the attribute that you added to the view. For more information, see Viewing Analytics cubes for ad-hoc analysis (see Viewing the iMIS cube for ad-hoc analysis).

Several dimensions in the cube created by iMIS Analytics rely on the Country portion of a contact record's address information. For this reason, it is important to assign a default country code to iMIS contact records that are missing country information.

When the Analytics data warehouse is populated, a special country code (USA by default) is inserted into data from iMIS that does not contain country information. You can change this default country code to a different value at any time.

Note: Changes you make to the default country code affect only the Analytics data warehouse and analysis database.  This code is not inserted into your iMIS database. Changes that you make to the default country code are not retroactive.The new default country code will be used only for new records that are brought into the data warehouse.

  • You must perform this procedure on the iMIS application server where Analytics is installed.
  • Analytics must be properly installed and configured. See Installing Analytics for more information.
  • If SQL Server Business Intelligence Development Studio (or any Visual Studio 2005-based window) is currently open on the host, close the Visual Studio window before starting this procedure.

Warning!
The Analytics Configuration tool used in this procedure is meant primarily for advanced development and customization of Analytics. Using this tool for any purpose other than the specific goals of this procedure is unsupported at this time.

  1. On the iMIS application server where Analytics is installed, open Windows Explorer and navigate to the C:\Program Files\ASI\iMIS\Analytics folder.
  2. Double-click AnalyticsConfiguration.exe. The Analytics Configuration dialog appears.
  3. In the i15.DefaultBlankCountry row, set the Value to a valid 2- or 3-digit country code.
  4. Click File > Save.
  5. Update the data warehouse and analysis database by manually running the Update iAnalytics Integration Services package. (For more information, see Manually running the Update iAnalytics package.)

iMIS Analytics is configured at installation time to use the fiscal year that is currently defined in the AR/Cash feature of iMIS. If you later change the defined fiscal year in iMIS, you must manually configure Analytics accordingly so that the fiscal year is the same in both products.

Note: Changes you make to the fiscal year in Analytics affect only the Analytics data warehouse and analysis database.

  • You must perform this procedure on the iMIS application server where Analytics is installed.
  • If SQL Server Business Intelligence Development Studio (or any Visual Studio 2005-based window) is currently open on the host, close the Visual Studio window before starting this procedure.

Warning!
The Analytics Configuration tool used in this procedure is meant primarily for advanced development and customization of Analytics. Using this tool for any purpose other than the specific goals of this procedure is unsupported at this time.

  1. On the iMIS application server where Analytics is installed, open Windows Explorer and navigate to the C:\Program Files\ASI\iMIS\Analytics folder.
  2. Double-click AnalyticsConfiguration.exe. The Analytics Configuration dialog appears.
  3. In the i15.ETL.FiscalYearBegins row, set the Value to a number from 1 to 12 that corresponds to the calendar month in which the fiscal year begins. For example, if the fiscal year begins in April, enter 4.
  4. Click File > Save.
  5. Update the data warehouse and analysis database by manually running the Update iAnalytics Integration Services package. (For more information, see Manually running the Update iAnalytics package.)

iMIS Analytics provides no security capabilities beyond that supported by Microsoft SQL Server. Use Microsoft SQL Server Management Studio to define security roles and permissions for the Analytics data warehouse and for the Analytics cubes.

  • Connect to the Database Engine on the Analytics host to define properties for the Analytics data warehouse.
  • Connect to Analysis Services on the Analytics host to define properties for the Analytics cubes.

Because iMIS Analytics comprises SQL Server and Analysis Services databases, Integration Services packages and jobs, the Windows uninstall process cannot completely remove all Analytics components. After removing Analytics by using the Windows Add or Remove Programs utility, you must also manually remove some Analytics components.

  1. On the Analytics host, choose Start > Control Panel > Add or Remove Programs. The Add or Remove Programs window appears.
  2. From the program list, select iMIS and click Change/Remove. The Welcome page of the iMIS - InstallShield Wizard appears.
  3. Select the Modify option and click Next. The Select Features page appears.
  4. Clear the iMIS Analytics checkbox located beneath the Optional Components node.
  5. Click Next. The wizard displays status information as it removes Analytics. When the uninstallation is complete, the Maintenance Complete page appears.
  6. Click Finish.
  7. Remove SQL Server Analysis Services components that are specific to Analytics.
    • Use SQL Server Management Studio to connect to Analysis Services on the Analytics host.
    • Remove the database named iAnalytics (the analysis database)
  8. Remove SQL Server Database Engine components that are specific to Analytics.
    • Use SQL Server Management Studio to connect to Database Engine on the Analytics host.
    • Remove the following components:
      • The database named iMIS_Analytics (the data warehouse)
      • The SQL Server Agent job named UpdateinstanceName_analysisDBName
  9. Remove SQL Server Integration Services components that are specific to Analytics.
    • Use SQL Server Management Studio to connect to Integration Services on the Analytics host.
    • Remove the stored package named UpdateinstanceName_analysisDBName (located in the MSDB folder)