OLAP administration: setup of Analysis Services and its components

OLAP Installation with Analysis Services

OLAP in PANTHEON is compatible with Analysis server version 2012 and above, up to 2019. Lower versions of Analysis Server are not supported so you should upgrade to the newest version. If you are using older version of PANTHEON (prior to 10.0.30.0) or older version of Analysis Server, please continue here. Higher versions were not yet tested so some additional instalations might be required.

Referential integrity is very important in ZEUS: If a record does not exist (e.g. an item does not exist in the register), ZEUS will not produce the same results as by using simple report.

Microsoft Analysis Services Server requires sufficient free disk space (approx. ten times the size of the PANTHEON database) to process cubes. Example:The size of myDB.mdf is 1 GB, therefore Analysis Services requires approximately 10 GB of free space.

Analysis server instalation

If you are installing analysis server for PANTHEON OLAP, you have to install it in multidimensional mode. Cube processing will not work on other types of server.

Check also for compatibility of server collation.

For proper functioning of Microsoft AS, it is necessary to select collation, that in not Case Sensitive.

OLAP employs Windows Authentication for connectivity. Because of this is important that SQL Server is using the Windows Authentication, too (SQL Server and Windows).

Once analysis server is installed, make sure that it is running. You can check this in SQL configuration manager or in Windows services.

Analysis server startup

Once analysis server is installed, make sure that it is running. You can check this in SQL configuration manager or in Windows services.

User authorization for Analysis server and cube processing

OLAP cubes contain a lot of information about individual company (goods movements, human resources data, financial data…). OLAP cubes can also be accessed from other tools (e.g. MS Excel) so it is therefore important to protect these data. As Analysis server uses Windows authentication you have to grant access to individual Windows user. There are two options to do so. Follow the link to Set security roles for Analysis server to check how to define user authorizations.

OLAP cubes cannot be used in PANTHEON until you define user authorizations.

Once user authorization is defined you also have to set users authorization to process cubes. During cube processing, user need access to database to rewrite data from SQL to OLAP database.

PANTHEON uses two login types. Login type can be selected in Administration panel in ZEUS|Analyses, reports|General. To preform cube processing, user must be granted a database role db_datareader, db_datawriter and db_owner. If you select Windows Authentication, you have to set roles for user NT AUTHORITY\SYSTEM. If you select SQL Authentication then set roles on user that is used to connect to SQL database through PANTHEON (from which databases are processed).

If you are using automated cube processing make sure that the user, designated for processing, has correct database role.

 Besides OLAP  authorizations, you can additionally limit access to OLAP cubes through PANTHEON authorizations. Follow the link to learn mora about PANTHEON authorizations.

Workstation settings

After the installation and user authorizations you have to prepare each worksations to be able to access OLAP cubes. First, installation of OLE OB Provider is required prior using OLAP cubes as well as dll files. For more information, follow the link Set up clients workstation for OLAP to check how to prepare clients workstation.

Creating and processing cubes

Once all of the previous steps are preformed, you can create OLAP cubes. Cubes are created automatically when you start processing in PANTHEON.

1. Open menu ZEUS|Analyses, reports|Cube Processing in Administration panel
2. Insert Analysis server name (1) and name of OLAP database (2) on Analysis server.
3. Click connect and, if access is granted for user, PANTHEON will connect to Analysis server.
4. Panel on the right side (3) will show you list of OLAP databases that you can access.
5. Click button Process.
   1. If you are doing this fo the first time, OLAP database will be created under a name that you inserted above.
   2. If you are reporcessing the database, data will be updated in the database named above.
6. Once processing is concluded, report is shown (4) and, if there are no errors, databases are processed.
7. You can check if data are up-to-date, by selecting cube in desired database. Last process date will be shown on the left (5).

Processing of OLAP cubes can be automated with command line parameter /ZE = FULL. Follow he link Set automatic cube processing to see how to prepare automated process for cube processing.

Do not use Data Transformation Services or similar tools to process cubes. Do it only with PANTHEON. Cube processing in PANTHEON also updates PANTHEON's database as required for ZEUS. Cube update and reconfiguration is not incorporated into PANTHEON upgrades because creating and processing cubes with a large quantity of data would take a very long time. It is recommended to schedule cube creation and processing for a time when the computer is not in use, e.g. overnight.

Custom OLAP cubes in PANTHEON

PANTHEON enables you to create custom OLAP cubes to prepare custom analyses. Custom cube has to be created on Analysis server and must have a different name that Datalab default cubes. Follow this link to check Reccomentations for creating user cubes.

After ZEUS installation is complete, regions can be optionally entered into the Postal Areas register.