Analysis Services Setup

Microsoft SQL Server Analysis Services delivers online analytical processing and data mining functionality for business intelligence applications such as Umbria.

A cube is then augmented with calculations, key performance indicators (KPIs), actions, partitions, perspectives, and translations. A cube is essentially synonymous with a Unified Dimensional Model and is a set of related measures and dimensions that are used to analyze data.

In Analysis Services, a KPI is a collection of calculations that are associated with a measure group in a cube and are used to evaluate business success. KPIs also have additional metadata that provide information about how client applications display the results of a KPI's calculation.

Prosperoware uses Analysis Services in Umbria (v. 2.1 and up) to show comparisons between individual Partners' or Associates’ KPI statistics and the rest of the firm's, as well as comparisons with last year's performance.

Tip: See the Analysis Services Architecture page for more information about the technology and setup behind the Microsoft SQL Server Analysis Services.

Setup

To set up Analysis Services during initial installation:

1. First, install SQL Server Analysis Services, if you have not done so already.
2. Go through the analytics database installation available in the Umbria installation process wizard. The wizard is accessible by adding /Install/Wizard to the end of the Umbria root path in your browser. You do not have to go through all the steps, only the one called Analysis Services Setup.

   Tip: The Analysis Services Setup page is not mandatory, so during installation it is possible to skip this screen.

   Warning: For the analytics database installation, you MUST go through the installation wizard.

3. On the Analysis Services Setup page, enter the information from the following table.

   Field	Description
   Data Source Server Name	Enter the name of the server that holds the Umbria database that you entered on the Database Setup screen when installing Umbria.
   Data Source Database Name	Enter the name of the database containing Umbria that you entered in the Database Setup screen during installation.
   Authentication	Choose the type of authentication to be used to connect to the data source database. Options include Integrated (Windows authentication using the Windows credentials) and Sql Server (SQL authentication using specific database-only credentials).
   Analysis Server Name	Enter the name of the server that has Analysis Services installed. Usually this is the same as the data source.
   Analysis Database Name	Enter the name of the database to be used for Umbria analytics processing that is created when the wizard finishes.

4. After you are done, click Next. Information is written into the Caliban.config file and is used later for querying the analytics cube. Then you are taken to the Upgrade page with the information about the latest cube script version.

   Warning: After setup, you need to manually go to the /Install/Upgrade page to force Umbria to apply the update to the web server.

5. Type Analysis in the Modules search field and click the magnifying glass icon. The folder tree is filtered to show only those modules matching the search criteria.
6. Click the Analysis Settings module under the Manage folder. The Analysis Settings tab appears.
7. On the Analysis Settings tab, click the Refresh Cube Data button. A confirmation message appears.
8. On the confirmation message, click Yes. The confirmation message closes.

   Tip: See the Command Log and check to see if the TaskDescription of Refreshing cube’s data has a CommandStatus of Success to know that the manual refresh process has completed.

To set up Analysis Services after initial installation:

In the Admin Panel, you can change the information about the relational database for the installation of the cube. These steps assume you have already installed the rest of Umbria and are just adding Analysis Services.

1. Type Analysis in the Modules search field and click the magnifying glass icon. The folder tree is filtered to show only those modules matching the search criteria.
2. Click the Analysis Settings module under the Manage folder. The Analysis Settings tab appears.
3. On the Analysis Settings tab, enter the information from the following table.

   Field	Description
   Authentication	Choose the type of authentication to be used to connect to the data source database. Options include Integrated (Windows authentication using the Windows credentials) and Sql Server (SQL authentication using specific database-only credentials).
   Data Source Database Username	Enter the username of the database containing Umbria that you entered in the Database Setup screen during installation. (SQL authentication only.)
   Data Source Database Password	Enter the password for the username of the database containing Umbria that you entered in the Database Setup screen during installation. (SQL authentication only.)
   Analysis Server Name	Enter the name of the server that has the Analysis Services installed. Usually this is the same as the data source.
   Analysis Database Name	Enter the name of the database to be used for Umbria analytics processing that is created when the wizard finishes.

4. Click the Initialize Analysis Database button to manually create the database and cubes. A confirmation message appears.
5. On the confirmation message, click Yes. The confirmation message closes.
6. Click the Refresh Cube Data button. A confirmation message appears.
7. On the confirmation message, click Yes. The confirmation message closes.
8. Click the Save button. A confirmation message appears.
9. On the confirmation message, click Yes. The confirmation message closes. A warning message appears.
10. On the warning message, click Yes to restart your session. The warning message closes and the Admin Panel reloads.

Warning: After setup, you need to manually go to the /Install/Upgrade page to force Umbria to apply the update to the web server.

Edit Analysis Services Settings

In the Admin Panel, you can change the information about the relational database for the installation of the cube. These steps assume you have already installed Analysis Services and are just editing settings.

1. Type Analysis in the Modules search field and click the magnifying glass icon. The folder tree is filtered to show only those modules matching the search criteria.
2. Click the Analysis Settings module under the Manage folder. The Analysis Settings tab appears.
3. On the Analysis Settings tab, edit the information from the following table as needed.

   Field	Description
   Authentication	Choose the type of authentication to be used to connect to the data source database. Options include Integrated (Windows authentication using the Windows credentials) and Sql Server (SQL authentication using specific database-only credentials).
   Data Source Database Username	Enter the username of the database containing Umbria that you entered in the Database Setup screen during installation. (SQL authentication only.)
   Data Source Database Password	Enter the password for the username of the database containing Umbria that you entered in the Database Setup screen during installation. (SQL authentication only.)
   Analysis Server Name	Enter the name of the server that has the Analysis Services installed. Usually this is the same as the data source.
   Analysis Database Name	Enter the name of the database to be used for Umbria analytics processing that is created when the wizard finishes.

4. Click the Save button when finished. A confirmation message appears.
5. On the confirmation message, click Yes. The confirmation message closes. A warning message appears.
6. On the warning message, click Yes to restart your session. The warning message closes and the Admin Panel reloads.
7. Click the Refresh Cube Data button. A confirmation message appears.
8. On the confirmation message, click Yes. The confirmation message closes.
9. Click the Save button. A confirmation message appears.
10. On the confirmation message, click Yes. The confirmation message closes. A warning message appears.
11. On the warning message, click Yes to restart your session. The warning message closes and the Admin Panel reloads.

Warning: After setup, you need to manually go to the /Install/Upgrade page to force Umbria to apply the update to the web server.

Cube Data

To manually refresh cube data:

Follow the steps below to manually refresh cube data through the Admin Panel.

1. Type Analysis in the Modules search field and click the magnifying glass icon. The folder tree is filtered to show only those modules matching the search criteria.
2. Click the Analysis Settings module under the Manage folder. The Analysis Settings tab appears.
3. At the top of the Analysis Settings tab, click the Refresh Cube Data button. A confirmation box appears.
4. Click the Yes button on the confirmation box. The box closes and an alert message briefly appears, alerting you that the update process may take some time to complete.

   Tip: See the Command Log and check to see if the TaskDescription of Refreshing cube’s data has a CommandStatus of Success to know that the manual refresh process has completed.

To schedule cube data refresh:

Follow the steps below to set up a schedule for cube data to automatically refresh.

1. Type Analysis in the Modules search field and click the magnifying glass icon. The folder tree is filtered to show only those modules matching the search criteria.
2. Click the Analysis Settings module under the Manage folder. The Analysis Settings tab appears.
3. From the Sync schedule mode drop-down, select either Daily or Hourly.
   1. If you select Daily, you must select a Time.
   2. If you select Hourly, you must enter a number of hours to determine how often the data should sync. The default is every two hours.
4. Click the Save button when finished. A confirmation message appears.
5. On the confirmation message, click Yes. The confirmation message closes. A warning message appears.
6. On the warning message, click Yes to restart your session. The warning message closes and the Admin Panel reloads.

Tip: See the Command Log and check to see if the TaskDescription of Refreshing cube’s data has a CommandStatus of Success to know that the scheduled refresh process has completed. The User will be listed as SYSTEM for scheduled refreshes.