Creating IPS and Data Services repositories

The IPS repository is a storage for environment and user configuration information and metadata collected by various services of IPS and Data Services. It has another name: the CMS database. This name should be quite familiar to those who have used SAP Business Intelligence software. Basically, IPS is a light version of SAP BI product package. You will always use only one IPS repository per Data Services installation and most likely will deal with it only once: when configuring the environment at the very beginning. Most of the time, Data Services will be communicating with IPS services and the CMS database in the background, without you even noticing.

The Data Services repository is a different story. It is much closer to an ETL developer as it is a database that stores your developed code. In a multiuser development environment, every ETL developer usually has its own repository. They can be of two types: central and local. They serve different purposes in the ETL lifecycle, and I will explain this in more detail in the upcoming chapters. Meanwhile, let's create our first local Data Services repository.

Getting ready…

Both repositories will be stored in the same SQL Server Express RDBMS ((local)\SQLEXPRESS) that we used to create our source OLTP database, ETL staging databases, and target data warehouse. So, at this point, you only need to have access to SQL Server Management Studio and your SQL Server Express services need to start.

How to do it…

This will consist of two major tasks:

1. Creating a database:
   1. Log in to SQL Server Management Studio and create two databases: IPS_CMS and DS_LOCAL_REPO.
   2. Right now, your database list should look like this:
   
2. Configuring the ODBC layer: Installation requires that you create the ODBC data source for the IPS_CMS database.
   1. Go to Control Panel | Administrative Tools | ODBC Data Sources (64-bit).
   2. Open the System DSN tab and click on the Add… button.
   3. Choose the name of the data source: SQL_IPS, the description SQL Server Express, and the SQL Server you want to connect to through this ODBC data source: (local)\SQLEXPRESS. Then, click on Next.
   4. Choose SQL Server authentication and select the checkbox Connect to SQL to obtain the default settings. Enter the login ID (sa user) and password. Click on Next.
   5. Select the checkbox and change the default database to IPS_CMS. Click on Next.
   6. Skip the next screen by clicking on Next.
   7. The final screen of the ODBC configuration should look like the following screenshot. Then, clicking on the Test Data Source button should give you the message, TESTS COMPLETED SUCCESSFULLY!
   

How it works…

These two empty databases will be used by Data Services tools during installation and post-installation configuration tasks. All structures inside them will be created and populated automatically.

Usually, they are not built for users to access them directly, but in the upcoming chapters, I will show you a few tricks on how to extract valuable information from them in order to troubleshoot potential problems, do a little bit of ETL metadata reporting, or use an extended search for ETL objects, which is not possible in the GUI of the Designer tool.

The ODBC layer configured for the IPS_CMS database allows you to access it from the IPS installation. When we install both IPS and Data Services, you will be able to connect to the databases directly from the Data Services applications, as it has native drivers for various types of databases and also allows you to connect through ODBC layers if you want.

See also

References to a future chapter containing techniques mentioned in the preceding paragraph.