Introduction
The steps provided here are an indication of the normal process required to install and configure WhereScape RED.
In some environments, additional processes will be required.
- Install the WhereScape RED software onto your PC. Refer to Installing RED for SQL Server for details.
- Add your license key. Refer to Adding your License for details.
- Check your Windows version. Refer to Checking your Windows Version for details.
- Check your ODBC Drivers. Refer to Checking your ODBC Drivers for details.
- Designate a database that is to be used as the data warehouse database. Refer to Creating a SQL Server Database for details.
- Add the definition of an ODBC data source to connect to the data warehouse database. Refer to Configuring a SQL Server Metadata Connection (DSN) for details.
- Create the WhereScape metadata repository. Refer to Creating a Metadata Repository for SQL Server for details.
- Install a Windows Scheduler. Refer to Installing a Windows Scheduler for SQL Server for details.
- If required, load the WhereScape tutorial tables into a separate database. It is highly recommended that the tutorials be undertaken. Refer to Creating Tutorial Data for details.
Note
A Quick Start option is available for SQL Server. This option performs all the required tasks (except the entry of a license key) when provided with a valid database server
Problem Solving
If any problems happen in your install that cannot be resolved, proceed as follows when running WhereScape's Setup Administrator utility:
- Create a log file by selecting Tools > Start Logging from the menu bar. Enter a file name.
- Step through at least:
- Computer Setup > Check Windows version.
- Computer Setup > Check ODBC drivers.
- Computer Setup > Validate/Add ODBC Source.
- Metadata Repository > Validate meta repository.
- Select Tools > Stop Logging to close off the log.
- Edit the log and remove any passwords that appear.
- Send your log file to support@wherescape.com.
Installing RED for SQL Server
Installation Prerequisites
WhereScape RED requires certain client and server components.
Client Prerequisites
- Windows 10 and Windows 11.
- 4GB RAM minimum
- 2GB available disk space.
- ODBC Version 3.0 (normally shipped as part of the Operating System).
- For building a data warehouse, the appropriate Microsoft ODBC driver for SQL Server is required.
Server Prerequisites
- RED requieres SQL Server versions beginning from 2012 to SQL Server 2022.
- To use the WhereScape RED Scheduler, a UNIX, Linux or Windows based server is also required (this may be the database server). WhereScape RED only supports sh and ksh under UNIX and bash under Linux.
Installation of WhereScape RED
To install WhereScape RED, refer to Installing WhereScape RED for details.
SQL Server Database and Login Setup
SQL Server Database
Create a SQL Server database to be used as the RED metadata repository and data warehouse.
To use object placement across multiple schemas in RED, create the required schemas in the SQL database.
Note
The database name used for the RED repository must follow SQL Server rules for "Regular Identifiers". For example, the database name should not contain any spaces. Please refer to SQL Server documentation for more details on rules for regular identifiers.
SQL Server Login Setup
Create an SQL Server login that will be used to set up the RED metadata repository and for access by the RED scheduler service. The minimum SQL Server permissions are as follows:
Server Level Permissions
- bulkadmin - this role is required if you want to load files or use Native ODBC loads.
Permissions to kill a process when killing jobs in the scheduler
- VIEW SERVER STATE
- ALTER ANY CONNECTION
SQL Server 2012 roles
For SQL Server 2012 and later only, users can create server roles and grant them to the RED scheduler and RED users.
Database Permissions
Database roles (on the database with the RED metadata in it)
- db_datareader
- db_datawriter
- db_ddladmin
Database grants (on the database with the RED metadata in it)
- SELECT
- INSERT
- UPDATE
- DELETE
- EXECUTE
Note
When creating a new repository or validating the metadata in an existing repository, the SQL Server login used must have SYSADMIN or the ALTER TRACE permission. This is because almost all the WhereScape procedures have a catch all with log file option exception handler. This ensures that if the scheduler cannot log errors to the database, it can log errors to SQL Server logs and Application logs.
The SQL Server login for RED must have a default schema of dbo.Configuring a SQL Server Metadata Connection (DSN)
To begin adding an ODBC Source from WhereScape's Setup Administrator, refer to Creating an ODBC Connection for details.
1. In ODBC Data Source Administrator, click the System DSN tab to view the system wide ODBC entries.
- Click the Add... to add a new entry.
2. Select an SQL Server driver from the driver list.
3. Enter a Name for the ODBC Source and select or enter the Server.
- Click Next.
4. Another pop-up appears with authentication details.
- Change as required or leave as shown if you are unsure.
- Click Next.
5. The next pop-up provides the opportunity to select a database. The following example shows the selection of the Northwind database.
- Select a database from the drop-down-list and click Next.
6. The last pop-up enables setting of other features if desired.
- Ensure the regional settings check-box is not used. If this is used, then date and number formats will be in regional standards rather than native form.
- Click Finish.
7. Test the data source using the 'Test Data Source...' button, otherwise just click OK.
Tip
If multiple PCs will access the data warehouse, they must all use the same data source name as entered here.
Creating a Metadata Repository for SQL Server
1. In WhereScape's Setup Administrator, click the Metadata Repository tab and then click Create Metadata Repository.
2. This option creates the WhereScape meta tables, sequences and procedures in the specified schema. It checks to ensure that the schema, database or user does not contain an existing metadata repository before starting the metadata install.
- Click Create Metadata Repository.
- Enter the ODBC Connect, User Name and Password and then click OK.
3. When creating an SQL Server metadata repository, an additional screen is displayed requesting Filegroup information.
- Select the required Filegroups and then click OK.
- If Filegroups are not being used, leave the Filegroups fields blank and then click OK.
Notes
- The metadata repository and data warehouse tables must both be created under the dbo schema when using SQL Server as the data warehouse server.
- Ensure that the user creating the meta repository has the required privileges to create objects in dbo.
SQL Server Quick Start
The quick start component of WhereScape's Setup Administrator attempts to do a rapid install of WhereScape RED onto a SQL Server database server.
It creates two databases on this server and then installs the RED software and the tutorial; finally invoking WhereScape RED and the on-line tutorial help.
1. On the Quick Start tab, click Auto install Metadata to start. link
2. The following prompt is displayed.
- Click Yes to proceed with the creation of the databases.
3. You are first asked for the SQL Server instance in which to load the metadata repository and tutorial. The following window is presented.
RED will attempt to locate all SQL Server servers available and list them in the drop-down. This process can fail for a number of reasons in which case the Instance name will need to be ascertained and entered.
Leave the Username and Password blank, if using a trusted connection and no password is required. Click OK when complete.
Once the install has completed, RED will allow you to logon to the newly created data warehouse.
SQL Server Quick Application
The quick application option for SQL Server attempts to create a Data Warehouse database, populate that database with the WhereScape metadata, load an application, install a scheduler and optionally run a batch job.
It provides a means of quickly installing a RED application and getting the contents of the tables loaded and populated.
1. On the Quick Start tab, click Auto Install Metadata and Application to start. link
2. The following window is displayed.
- Click Yes to proceed.
3. The following window is displayed.
- Once all the values have been entered, click OK to proceed with the creation of the databases and the loading of the application.
The quick application can also be initiated from the command line or from a Windows batch file.
Windows Command Line Invocation
The syntax for calling from the command line is as follows:
adm.exe -QA [-SWITCH "switch value"]
The switches that can be applied supply information to the Setup Administrator utility. When all switches are provided, the application install occurs automatically. Where some switches are left out a partially completed, a window appears. The missing information can be provided and the install then proceeds.
Note
Where a parameter for a switch contains spaces, the parameter must be enclosed in double quotes.
The switches are:
| Switch | Parameter to be supplied |
|---|---|
-QA | Indicates that this is a "Quick Application". No parameter is required. |
-SS | The SQL Server instance name where the Data Warehouse database will reside. |
-SL | The administrator user name for the SQL Server instance defined with the -SS switch. If this switch is not provided a Trusted connection is assumed. |
-SP | The administrator password for the SQL Server instance defined with the -SS switch. |
-SN | The Data Warehouse database name. This database will be created in the instance defined by -SS. An ODBC Source DSN of the same name will also be created on the PC. |
-a | The ODBC DSN architecture. |
-AN | The RED application name. This is the name of the 'id' file and is typically of the structure app_id_XXXX_vvvvvv.wst where XXXX is the application identifier and vvvvvv is the application version. For example, the tutorial application would be app_id_SQLT_tutorial_6.wst |
-AD | The RED application directory. This is the directory in which the file defined by the -AN switch resides. |
-LI | The SQL Server instance that contains the source database. |
-LD | The ODBC source DSN name as defined in the Connection object in the data warehouse application. This ODBC source will be created. |
-LN | The database name on the SQL Server instance as defined by the -LI switch. This is the database that contains the source data. |
-LT | This switch does not have a parameter. If set, it flags the ODBC source DSN as defined in the -LD switch as a trusted connection. |
-JN | The name of a job in the data warehouse that is to be run once all other tasks are completed. |
-CC | The name of the cube connection if appropriate. If set, then all cubes will be updated to have this as the Analysis Services connection. |
-DC | The name of the Data Warehouse connection if appropriate. If set, then all cubes will be updated to have this as their Source connection. |
-CD | The name of the cube database in Analysis Services if appropriate. If set, then all cubes will be updated to have this database as their Analysis Services database. |
-ML | The scheduler login name. |
-MP | The scheduler password. |
For example, a batch file to call an application load of the tutorial may look as follows:
@echo off c:\ cd \Program Files\WhereScape\ adm -QA -SS "WSL-TEST" -SN new_dw -LT -AN app_id_SQLT_tutorial_3.wst -AD "c:\Program Files\WhereScape\" -LI WSL-TEST -LD tutorial -LN tutorial -JN "Daily Update" -CC Cubes -CD My_database -DC DataWarehouse exit