OLAP Data Source

 The OLAP data source supports access to Microsoft SQL Server® Analysis Services (SSAS) or Cognos TM1® databases. The Attach to OLAP Data dialog allows for easy selection of data cubes, columns, rows and data dimensions, while also providing the ability to enter more complex queries using MDX.

This section includes:

§        “OLAP System Requirements and Setup” on page 595

§        “Attach to OLAP Data” on page 595

§        “Application Options - OLAP” on page 600

§        “RTView Deployment - OLAP” on page 603

§        “OLAP Demos” on page 604

§        “Quick Start: OLAP Data” on page 605

§        “OLAP Database Connection Setup” on page 610

§        “OLAP Data Source Command Line Options” on page 612

OLAP System Requirements and Setup

System Requirements

In addition to basic “System Requirements”, the OLAP data source is only available on the Windows platform and requires client installation of Microsoft SQL 2000 with Analysis Services or Applix TM1. See the README_sysreq.txt file in your installation’s home directory for the current version(s) supported.

In the Display Viewer Applet, attachments to OLAP data require the use of the Data Server.

If you will be connecting to an Applix TM1 database using the Applix Java API Connection Type, you must include TM1JavaApi.jar from your Applix installation in “RTV_USERPATH”. See “OLAP Tab” for more information.

Setup

In addition to general environment variables (see “Setup”), you must append the following to the PATH environment variable:

Attach to OLAP Data

Note: The OLAP data source may not be licensed in your RTView installation.

From the Object Properties window you can access the Attach to OLAP Data dialog, which is used to connect an object to your OLAP cube. Once an object has been attached to your database it can receive continuous updates.

When an object property is attached to data, the Property Name and Value in the Object Properties window will be displayed in green. This indicates that editing this value from the Object Properties window is no longer possible. To remove the data attachment and resume editing capabilities in the Object Properties window, right-click on the Property Name and select Detach from Data. You will recognize that an object property has been detached from the database when the Property Name and Value are no longer green.

To bring up the Attach to OLAP Data dialog, right-click on the Property Name from the Object Properties window and select Attach to Data>OLAP. The Attach to OLAP Data dialog provides drop down menus that will be used to create a query for the selected database. Alternatively, if the Connection Type is ODBO, select the Enter MDX Query check box in order to enter an advanced query, or if the Connection Type is Applix Java API, select the Use TM1 View check box to specify a TM1 view to display.

The Database Name drop down menu lists all available databases. The Database Name field automatically displays the name of the default database. If the item you require is not listed, see “Application Options - OLAP” for information on how to add additional databases.

Validation Colors

Fields in the dialog change colors according to the information entered. These colors indicate whether or not information is valid. Information entered into the dialog is validated against the selected database.

Note: Only databases and cubes are validated. Column(s), Rows, and Slicer are assumed to be valid if the cube is valid. Advanced MDX queries are not validated.

The following describes the significance of the Attach to OLAP Data validation colors:

If the validation response is Unknown, see “Application Options - OLAP” for information on how to add a database.

Substitutions

Substitutions allow you to build open-ended displays in which data attachments depend on values defined at the time the display is run. Generic names, such as $table1 and $table2, are used instead of specific values. Later when the display is running, these generic values are defined by the actual names, such as production_table and system_table. In this way, a single display can be reused to show data from a number of different databases. For more information on creating displays using substitution values, see “Substitutions”.

Selecting Column, Row, and Slicer Dimensions

From the Attach to OLAP Data dialog you can specify which column, row and slicer dimensions to display and in what order they will appear. In order to populate the listing of available dimensions, you must first select a valid database and cube.

To bring up the Select Dimensions dialog, click on the  button to the right of the Column(s), Rows, or Slicer fields. The dialog contains a list of Available Dimensions that you can add from the selected cube.

To add, select an item from the Available Dimensions list and click on the Add button. If the Connection Type is Applix Java API, you may also right-click on any dimension in the Available Dimensions list and select an alias to use for that dimension. This will cause the members of that dimension to display the text for the selected alias in the dimension tree and also in the row and column labels of the query result. If the item you require is not listed, type your selection into the Enter field. Click the Remove button to delete an item previously added to the Selected Dimensions list. You can control the order of columns or rows in a table by arranging the items in the Selected Dimensions list with the Move Up and Move Down buttons. After moving an item up or down in the list, the item's position may be automatically adjusted so that it is grouped with other items in the list that belong to the same dimension.

The following describes the Attach to OLAP Data dialog commands:

Sample Databases

You must set up two sample databases that are used in demo displays located in demos\dstutorial, One database is for use with Microsoft SQL Server Analysis Services and the other is for use with Applix TM1. See “OLAP Database Connection Setup” for more information.

Application Options - OLAP

To access the Application Options dialog, select Tools>Options in the Display Builder. Options specified in the OLAP tab can be saved in an initialization file (OPTIONS.ini). On startup, the initialization file is read by the Display Builder, Display Viewer, Display Server, Data Server and Historian to set initial values. If no   directory has been specified for your initialization files and OPTIONS.ini is not found in the directory where you started the application, then RTView will search under lib in your installation directory. See “RTV_JAVAOPTS” for more information.

Note: Options specified using command line arguments will override values set in initialization files. See “OLAP Data Source Command Line Options” and “Command Line” for more information.

OLAP Tab

This tab allows you to add or remove your OLAP databases and set the default database. See “OLAP Database Connection Setup” for information on how to set up your database.

When you add a database to the list it will be highlighted in yellow indicating that it is not connected. To attempt to connect to a database, click OK, Apply, or Save. If the background remains yellow, then RTView was unable make a connection to your database. Check your database connection and see “OLAP Database Connection Setup” for information on how to set up your driver correctly.

Note: Databases that have been setup to Use Client Credentials will not connect unless you are logged in and you have objects in your display that are using that connection. See “OLAP Tab” for more information.

Note: Regardless of which tab you are currently working from in the Application Options dialog, RTView will attempt to connect to all unconnected databases each time you click OK, Apply, or Save.

Field Name	Description
Default Database	Name of database used as the default for data attachments. Select from drop down menu to change default setting.
Add Database	Click to open the Add Database dialog. To edit, select a database from the Databases list and double-click. Databases that are updating objects in a current display cannot be renamed.
	Database Name - The name to use when referencing the OLAP database connection in your data attachments. This does not need to match the actual name of the database.
	Connection Type - Select ODBO to make an ODBO connection to this database. This will enable writing MDX queries against your SQL Server Analysis Services or Applix TM1 database in the Attach to OLAP Data dialog. Select Applix Java API to use the Applix Java API to connect to this database. This will allow you to use aliases in your queries and to attach to views in your Applix TM1 database, but will not allow you to write MDX queries in the Attach to OLAP Data dialog. This option is not supported for SQL Server Analysis Services databases. Switching between these options may invalidate existing data attachments to this database. In this case, you will need to recreate your data attachments.
	User Name - The user name to pass into this database when making a connection. This parameter is optional.
	Password - The password to pass into this database when making a connection. This parameter is optional. If you need to provide an encrypted password (rather than expose server password names in a clear text file, use the encode_string command line option with the following syntax: encode_string type mypassword where type is the key for the data source and mypassword is your plain text password. Note: The type argument is only required when you encrypt a string for a data source. For example, enter the following in an initialized command window (see “Initializing a Command Prompt or Terminal Window”): encode_string olap mypassword and you will receive an encrypted password: encrypted value: 013430135501346013310134901353013450134801334 Copy the encrypted value, paste it into the password field and click Save to save this value to the initialization (*.ini) file. Or, if necessary, manually edit the (*.ini) file to include the encrypted value. Note: If you need to manually edit a configuration (*.ini) file, contact SL Technical Support at support@sl.com for information about supported syntax.
	Use Client Credentials - If selected, the user name and password from the RTView login will be used instead of the User Name and Password entered in the Add Database dialog. Connections to this database will only be made when you are running with login enabled and a display is opened that accesses this database. See “Login” for more information. As a result, this connection will not be made when you click OK or Apply in the Application Options dialog and will remain yellow. If you will be using the Data Server or the Display Server with a database connection that has this option enabled, you must enable Use Client Credentials for Database Login in these applications.
	TM1 Admin Host - The name of the host machine for the TM1 Admin Server. Note: Only available if the Connection Type is Applix Java API.
	TM1 Server Name - The name of the TM1 server to connect to. Note: Only available if the Connection Type is Applix Java API.
	Connection String - The OLE DB connection string to use to connect to this database. The syntax for this string varies by provider. Consult your OLAP database documentation to determine the OLE DB connection string for your OLAP provider. Example Microsoft SQL Server Analysis Services Connection String:     Provider=MSOLAP;Location=MyServer;Database=SampleMD Example Applix TM1 Connection String:     Provider=TM1OLAP;Data Source=planning sample Depending on your SQL Server installation, you may need to specify MSOLAP.2 for the Provider in your connection string for Microsoft SQL Server Analysis Services. Note: Only available if Connection Type is ODBO.
Remove Database	Select a database from the list and click Remove Database to delete. Databases that are updating objects in a current display cannot be removed.
Suppress Query Errors	Select to suppress errors generated by queries. Connection errors will still be shown.

Sample Databases

You must set up two sample databases that are used in demo displays located in demos\dstutorial. One database is for use with Microsoft SQL Server Analysis Services and the other is for use with Applix TM1. See “OLAP Database Connection Setup” for more informatoin.

RTView Deployment - OLAP

This page contains details about the deployment process that are specific to your data source. Please go to the Deployment section of this documentation for instructions on how to implement your RTView deployment option. Return to this page whenever you are instructed to refer to deployment information that is specific to your data source.

System Requirements and Setup

The OLAP data source has additional “System Requirements” and “Setup”.

Data Source Configuration File

RTView saves general application settings as well as data source configuration options in initialization files that are read at startup. If no directory has been specified for your initialization files and files are not found in the directory where you started the application, then RTView will search under lib in your installation directory. See “Application Options”, “Application Options - OLAP”, and “RTV_JAVAOPTS” for more information.

Include the following initialization file when you deploy RTView with this data source:

Note: Options specified using command line and applet parameters override values set in initialization files.

Rich Client Browser Deployment Setup for Direct Data Connection

This deployment is not supported by your data source. Use the Data Server to supply data to displays with OLAP data attachments. See “Rich Client Browser with Served Data - Manual Set up” for more information.

OLAP Demos

Except where noted, all demos can be run in three ways, as an application, or via rich or thin client in a browser.

Before You Begin

Start the Demo Server 

Rich Client and Thin Client Demos only. 

There are rich and thin client demos already installed on the “RTView Demo Server”.

In an initialized command window (see “Initializing a Command Prompt or Terminal Window”):

Type run_startup_demoserver 

Data Source Demo

The Data Source Demo is designed to illustrate each data source.

1.     Setup Sample OLAP Database. See “OLAP Database Connection Setup” for more information.

2.     Run Demos - Application, Rich Client Browser, or Thin Client Browser

Application Demo 

1. In an initialized command window (see “Initializing a Command Prompt or Terminal Window”), go to the demos/dstutorial directory.

2. To view the demo, type:

run_viewer 

3. To edit the demo, type:

run_builder 

Rich Client Browser Demo

“Start the Demo Server” if it is not running.

1. In an initialized command window (see “Initializing a Command Prompt or Terminal Window”), go to the demos/dstutorial directory.

2. Start the Data Server by typing:

run_dataserver -socket 

3. In the Data Server dialog, select:

Start Serving Data

4. Open a browser and navigate to http://localhost:8068/dstutorial_applet.

Thin Client Browser Demo 

“Start the Demo Server” if it is not running.

1. In an initialized command window (see “Initializing a Command Prompt or Terminal Window”), go to the demos/dstutorial directory.

2. Start the Display Server by typing:

run_displayserver

3. Open a browser and navigate to http://localhost:8068/dstutorial.

Quick Start: OLAP Data

Note: The OLAP data source may not be licensed in your RTView installation.

Import an OLAP Database

If you have not already done so you must follow the instructions in this section to import the sample OLAP database. Two sample databases are used in this example, one for use with Microsoft SQL Server Analysis Services and one for use with Applix TM1. The following steps will need to be run on the system running the OLAP server. Follow the instructions below for the OLAP database you are using.

Microsoft SQL Server Analysis Services

Import the sample database into Microsoft SQL Server Analysis Services:

1.     Open Analysis Manager.

2.     Select your OLAP server.

3.     Right-click on your OLAP server and select Restore.

4.     In the Open Archive File dialog, go to demos\dstutorial located in your RTView installation directory and select SampleMD.cab.

5.     Click Open.

6.     In the Restore Database dialog click Restore.

Applix TM1

Import the sample database into Applix TM1:

1.     Extract the contents of SampleMD_TM1.zip into the server data directory of your TM1 installation.

2.     Restart your TM1 Admin Server.

Add an OLAP Database

Microsoft SQL Server Analysis Services

Add the sample database to the RTView Display Builder:

1.     Start the Display Builder. See “Running the Display Builder” for more information.

2.     Login to the Display Builder. By default, the Display Builder does not require a login. Login can be enabled at setup to support “Role-based Security”. See “Login” for more information. The default user name and password are:

User Name: admin

Password: admin

Note: It is possible that your system administrator may have configured another user name and password. In this case, you may also need to select a role. See “Role-based Security” for more information.

3.     Select Tools>Options to bring up the Application Options dialog.

4.     Select the OLAP tab and click Add Database.

5.     In the Add Database dialog, enter the following:

Name - SampleMD_SQLSAS

User Name - <blank>

Password - <blank>

Connection String - Provider=MSOLAP;Location=localhost;Database=SampleMD 

Note: If you are not running the OLAP server locally, change Location to the name of the machine running the OLAP server. Depending on your Microsoft SQL Server Analysis Services installation, you may need to use MSOLAP.2 for the Provider.

6.     Click OK to add the sample database and close the Add Database dialog.

7.     Click Apply to attempt to connect to the sample database. If the connection has been successful, SampleMD will appear in the Databases field with a white background. If the background is yellow, the connection has failed.

8.     Click Save to apply and close the Application Options dialog.

Applix TM1

Add the sample database to the RTView Display Builder:

1.     Start the Display Builder. See “Running the Display Builder” for more information.

2.     Login to the Display Builder. By default, the Display Builder does not require a login. Login can be enabled at setup to support “Role-based Security”. See “Login” for more information. The default user name and password are:

User Name: admin

Password: admin 

Note: It is possible that your system administrator may have configured another user name and password. In this case, you may also need to select a role. See “Role-based Security” for more information.

3.     Select Tools>Options to bring up the Application Options dialog.

4.     Select the OLAP tab and click Add Database.

5.     In the Add Database dialog, enter the following:

Name - SampleMD_TM1

User Name - User name for your OLAP server

Password - Password for your OLAP server

If you need to provide an encrypted password (rather than expose server password names in a clear text file, use the encode_string command line option with the following syntax:

encode_string type mypassword

where type is the key for the data source and mypassword is your plain text password.

Note: The type argument is only required when you encrypt a string for a data source.

For example, enter the following in an initialized command window (see “Initializing a Command Prompt or Terminal Window”):

encode_string olap mypassword

and you will receive an encrypted password:

encrypted value: 013430135501346013310134901353013450134801334

Copy the encrypted value, paste it into the password field and click Save to save this value to the initialization (*.ini) file. Or, if necessary, manually edit the (*.ini) file to include the encrypted value. Note: If you need to manually edit a configuration (*.ini) file, contact SL Technical Support at support@sl.com for information about supported syntax.

Connection String - Provider=TM1OLAP;Location=localhost;Data Source=sdata

Note: If you are not running the OLAP server locally, change Location to the name of the machine running the OLAP server. The Data Source should be the name of your OLAP server.

6.     Click OK to add the sample database and close the Add Database dialog.

7.     Click Apply to attempt to connect to the sample database. If the connection has been successful, SampleMD_TM1 will appear in the Databases field with a white background. If the background is yellow, the connection has failed.

8.     Click Save to apply and close the Application Options dialog.

Attach to OLAP Data

1.     In the Display Builder, click on the Tables  icon in the toolbar, and click in the Working Area to add the table.

2.     In the Object Properties dialog, edit the following:

autoResizeFlag - Click to select the check box.

label - Change the name of the label to Product Sales. Press <Enter>.

rowLabelMode - 1. Press <Enter>.

valueTable - Right-click in the Property Name field and select Attach to Data>OLAP.

Microsoft SQL Server Analysis Services

3.     In the “Attach to OLAP Data” dialog:

Database Name - SampleMD_SQLSAS should already be selected.

Cube Name - Select Sales from the drop down menu.

Columns - Click on the  button to the right of the Column(s) and select Time-->Calendar-->(All)-->AllTime-->*. Click Add and OK.

Rows - Click on the  button to the right of the Row and select Product-->(All)-->All Product-->Bread-->*. Click Add and OK.

Slicer - Click on the  button to the right of the Slicer and select Measures-->Measures Levels-->Sales Dollars. Click Add and OK.

Update Mode - Run Query Every Update Period should already be selected.

Applix TM1

In the “Attach to OLAP Data” dialog:

Database Name - SampleMD_TM1 should already be selected.

Cube Name - Select Sales from the drop down menu.

Columns - Click on the  button to the right of the Column(s) and select Time-->Calendar-->level000-->All Time-->*. Click Add and OK.

Rows - Click on the  button to the right of the Row and select Product-->level000-->All Product-->Bread-->*. Click Add and OK.

Slicer - Click on the  button to the right of the Slicer and select Measures-->level000-->Sales Dollars. Click Add and OK.

Update Mode - Run Query Every Update Period should already be selected.

4.     Click OK to apply these values and close the Attach to OLAP Data dialog. The table will populate with values from the Sales cube in the sample database.

5.     In the Object Properties window, double-click on columnFormat in the Property Name field to bring up the “Column Format Properties” dialog. In the Column Format Properties dialog, edit the following properties:

Column Name: Select 2003

Column Format: Select $#,##0.00;($#,##0.00)

Click the Add button to insert column format selections into the Column Formats field.

Column Name: Select 2004

Column Format: Select $#,##0.00;($#,##0.00)

Click the Add button to insert column format selections into the Column Formats field.

Column Name: Select 2005

Column Format: Select $#,##0.00;($#,##0.00)

Click the Add button to insert column format selections into the Column Formats field.

Click OK to close the Column Format Properties dialog.

Set a Drill Down Target on a Table Object

Information passed into a drill down display varies according to the attached data source.

1.     In the Object Properties window, edit the following properties:

drillDownTarget - Double-click in the Property Name field to bring up the Drill Down Properties dialog.

2.     In the “Drill Down Properties” dialog:

Apply Drill Down To - Select Named Window from the drop down menu.

Window Name - Enter OLAP.

Drill Down Display Name - From the drop down menu:

 Microsoft SQL Server Analysis Services -- Select dstutorial\sample_detail.rtv.

Applix TM1 -- Select dstutorial\sample_detail_tm1.rtv.

Drill Down Substitutions -

String - Enter $year.

Value - Enter $colName.

Click Add.

String - Enter $detail.

Value - Enter $1rowName.

Click Add.

3.     Click OK to attach the drill down target and close the dialog.

4.     Double-click on any row in the table to drill down to a detailed display for that product. Substitutions, which are automatically set on drill down displays activated from a table, allow a single display to be reused to show data from a number of different sources.

5.     Double-click on another row in the table and the same display will be reused to show details for the second product.

6.     Close the drill down window.

7.     In the Display Builder select File>Save.

Go to the “Quick Start Tutorial”

OLAP Database Connection Setup

RTView communicates with your OLAP database using either an ODBO (OLE DB) connection or an Applix Java API connection. The ODBO connection will enable writing MDX queries against your Microsoft SQL Server Analysis Services or Applix TM1 database in the Attach to OLAP Data dialog. The Applix Java API connection will allow you to display views from your Applix TM1 database in addition to allowing you to use aliases in your queries, but will not allow you to write MDX queries.

You will need to add your database and specify a connection type in the Display Builder in the Application Options dialog on the OLAP tab before RTView will attempt to connect to your database. If RTView is unable to connect to your database, either the connection string is incorrect, the OLAP server is not currently running, or you do not have permission to access the database. See “Application Options - OLAP” for more information.

Note: Databases that have been setup to Use Client Credentials will not connect unless you are logged in and you have objects in your display that are using that connection. See “OLAP Tab” for more information.

If you are connecting to your database using ODBO, you will be required to specify a connection string. The connection string is vendor specific. RTView supports connecting to Microsoft SQL Server 2000 Analysis Services and Applix TM1 8.4.3+.

 Example Microsoft SQL Server Analysis Services Connection String:

    Provider=MSOLAP;Location=MyServer;Database=MyDB

Example Applix TM1 Connection String:

    Provider=TM1OLAP;Data Source=planning sample

Note: Depending on your SQL Server installation, you may need to specify MSOLAP.2 for the Provider in your connection string for Microsoft SQL Server Analysis Services.

Sample Databases

Two sample databases are used in our sample displays located in demos\dstutorial, one for use with Microsoft SQL Server Analysis Services and one for use with Applix TM1. The following steps will need to be run on the system running the OLAP server.

Microsoft SQL Server Analysis Services

Import the sample database into Microsoft SQL Server Analysis Services: 

1.     Open Analysis Manager.

2.     Select your OLAP server.

3.     Right-click on your OLAP server and select Restore.

4.     In the Open Archive File dialog, go to demos\dstutorial located in your RTView installation directory and select SampleMD.cab.

5.     Click Open.

6.     In the Restore Database dialog click Restore.

Add the sample database to the RTView Display Builder: 

7.     Start the Display Builder. See “Running the Display Builder” for more information.

By default, the Display Builder requires a login to support role based security. See “Role-based Security” for more information. The default user name and password are:

User Name: admin

Password: admin

If your system administrator has configured a user name and password for you, use these instead. In this case, you may also need to select a role.

8.     Select Tools>Options to bring up the Application Options dialog.

9.     Select the OLAP tab and click Add Database.

10.  In the Add Database dialog, enter the following:

Name - SampleMD_SQLSAS

Connection Type - ODBO

User Name - <blank>

Password - <blank>

Connection String - Provider=MSOLAP;Location=localhost;Database=SampleMD

Note: If you are not running the OLAP server locally, change Location to the name of the machine running the OLAP server. Depending on your Microsoft SQL Server Analysis Services installation, you may need to use MSOLAP.2 for the Provider.

11.  Click OK to add the sample database and close the Add Database dialog.

12.  Click Apply to attempt to connect to the sample database. If the connection has been successful, SampleMD will appear in the Databases field with a white background. If the background is yellow, the connection has failed.

13.  Click Save to apply and close the Application Options dialog.

Applix TM1

Import the sample database into Applix TM1: 

1.     Extract the contents of SampleMD_TM1.zip into the server data directory of your TM1 installation.

2.     Restart your TM1 Admin Server.

Add the sample database to the RTView Display Builder:

3.     Start the Display Builder. See “Running the Display Builder” for more information.

By default, the Display Builder requires a login to support role based security. See “Role-based Security” for more information. The default user name and password are:

User Name: admin

Password: admin

If your system administrator has configured a user name and password for you, use these instead. In this case, you may also need to select a role.

4.     Select Tools>Options to bring up the Application Options dialog.

5.     Select the OLAP tab and click Add Database.

6.     In the Add Database dialog, enter the following:

Name - SampleMD_TM1

Connection Type - Applix Java API

User Name - the user name for your OLAP server

Password - the password for your OLAP server

TM1 Admin Host - the name of the host system for your TM1 Admin server

TM1 Server Name - the name of your TM1 server

7.     Click OK to add the sample database and close the Add Database dialog.

8.     Click Apply to attempt to connect to the sample database. If the connection has been successful, SampleMD_TM1 will appear in the Databases field with a white background. If the background is yellow, the connection has failed.

9.     Click Save to apply and close the Application Options dialog.

OLAP Data Source Command Line Options

In addition to General Options, the following command line arguments are enabled with the OLAP data source when you run RTView applications from a Windows Command Prompt or UNIX terminal window. See “Command Line Options: Display Builder and Display Viewer” for more information.

Note: If a command line argument contains a space or a semicolon, then the entire argument must be enclosed in quotes (e.g.: "-sub:$data:my Data").