TANDBERG ANALYTICS EXTENSION - Service manual

TANDBERG Analytics
Extension
Administrator guide
D14668.01
September 2010

Introduction
TANDBERG Analytics Extension Admin guide 2
Contents
Contents............................................................................................................................... 2
Document Revision History................................................................................................ 4
Introduction......................................................................................................................... 5
New feature overview.............................................................................................................................. 5
Technical overview ............................................................................................................. 6
Analytics Extension components and roles...................................................................... 7
Data warehouse server ........................................................................................................................... 8
Data warehouse database ............................................................................................................... 8
Data warehouse CUBE.................................................................................................................... 8
Data warehouse updates ................................................................................................................. 9
Service account permissions and uses................................................................................................... 9
Data warehouse service user........................................................................................................... 9
DWH TMS service user.................................................................................................................... 9
Adding and managing users .............................................................................................10
Analytics users ...................................................................................................................................... 10
Administrator roles................................................................................................................................. 11
Analytics extension administrators................................................................................................. 11
Analysis services server administrators......................................................................................... 11
Web interface......................................................................................................................12
Download excel sample files.......................................................................................................... 12
Log ETL jobs .................................................................................................................................. 12
Reconfiguring Analytics Extension ..................................................................................14
Troubleshooting.................................................................................................................15
Web site issues ..................................................................................................................................... 15
ETL job failures .............................................................................................................................. 15
Client Connectivity Issues ..................................................................................................................... 16
Microsoft Excel and Windows Authentication ................................................................................ 16
The LocaleIdentifier property.......................................................................................................... 17
Logs ....................................................................................................................................20
Application Logs............................................................................................................................. 20
Database Logs ............................................................................................................................... 20
Setting up HTTP access to the CUBE...............................................................................21
Installing IIS.................................................................................................................................... 21
Copying the pump binaries............................................................................................................. 21
Creating an IIS application pool ..................................................................................................... 21
Setting up handler mappings.......................................................................................................... 22
Name extension ............................................................................................................................. 23

Introduction
TANDBERG Analytics Extension Admin guide 3
Choosing an authentication mode.................................................................................................. 24
Setting msmdpump.dll as the default document............................................................................ 24
Setting the target Analysis Services server.................................................................................... 25
Creating a domain service account and giving it read access to the cube.................................... 25
Verifying that the connection works ............................................................................................... 25
References and related documents..................................................................................27
Licenses..............................................................................................................................28
EULA...................................................................................................................................32

Introduction
TANDBERG Analytics Extension Admin guide 4
Document Revision History
Rev 02
August 2010: Release of version 1.1
Rev 01
May 2010: Release of the Analysis Extension 1.0

Introduction
TANDBERG Analytics Extension Admin guide 5
Introduction
This document describes the TANDBERG Analytics Extension.
The TANDBERG Analytics Extension is an online analytical processing (OLAP) system for the
TANDBERG Management Suite (TMS) and provides advanced reporting functionality on your video
network. It integrates with Business Intelligence (BI) applications, custom built applications, and other
applications capable connecting to an OLAP cube such as Microsoft Excel.
The TANDBERG Analytics Extension API is described in a separate document, TANDBERG Analytics
Extension API Documentation.
This document, along with the installation guide, release notes, and other documentation can be found
at the Support > Documentation section of http://www.tandberg.com.
New feature overview
Analytics Extension version 1.1 is a maintenance release, with additional Analytics Extension cube
features, installer enhancements, and improved logging.
The main improvement to the cube is a new fact table; Fact MCU Utilization. This supersedes the old
Fact MCU Load, which is no longer available. As a result, existing reports that use Fact MCU Load
must be updated to use the new measures. Fact MCU Utilization basically the end user access to the
same data as before, but with improved data quality. In addition, the new fact table contains
aggregated data about MCU usage across the entire installation, and can filter results on arbitrary
MCUs.
Two bracket dimensions have been introduced: Actual Duration Brackets and Actual System Count
Brackets. Both apply to Fact Conference. Actual Duration Brackets shows the mode average (most
common conference durations). Actual System Count breaks down conferences by the number of
participants.
A new Persistent System dimension has been introduced. This dimension is a variant of the System
dimension. Persistent System will always associate historical call data with the current system
information. The Persistent System dimension has the exact same attributes and hierarchies as the
System dimension. This means systems can be renamed without losing associated historical data.
The installer now creates logs automatically, and will expose the version number in the log files. Also,
the installer will catch some common TMS and DWH SQL server configuration mistakes and warn the
user accordingly.
The Microsoft Excel example reports have been updated and use more intuitive descriptions. The ETL
job has been made more robust, and handles TMS database data mismatches that could make the
1.0 ETL job fail.

Technical overview
TANDBERG Analytics Extension Admin guide 6
Technical overview
The sections provide technical explanations of how the Analytics Extension components are created
and used in ongoing operations, to assist administrators understand system dependencies.
TANDBERG Analytics Extension brings business intelligence, customized reporting, and high
performance data mining to TMS. Analysis and data from your visual communications network can be
accessed via industry standard tools.
33.3
333
%
33.3
333
%
33.3
333
%
TANDBERG
VCS
TANDBERG
Analytics
Extension
Business
Intelligence User
End-
Users
Visual Communications
Network
Administrators
TANDBERG
TMS
TANDBERG Analytics Platform
Analytics Extension makes the best use of Microsoft‟s powerful SQL Server Analysis Services to
provide business knowledge and customized reporting on your TANDBERG TMS Server, and
integration with Business Intelligence applications. Analytics Extension enables the use of
standardized, OLAP compatible clients to access a known and supported list of information about the
usage of your visual communications network without disrupting ongoing operations.
The Analytics Extension component is installed on an existing TANDBERG Management Suite Server
and creates and maintains new information stores. The new information stores enhance the type of
data available for TMS customers, and provides a standardized, supported way of accessing reporting
data made available through the Analytics Extension.

Analytics Extension components and roles
TANDBERG Analytics Extension Admin guide 7
Analytics Extension components and roles
To plan a deployment of the Analytics Extension, it is important to understand the components and
roles used in the solution so you can deploy the solution in the best possible way for your
environment. The following table and illustration explain the various elements involved with the
TANDBERG Analytics Extension and how they relate to each other.
TMS Web Server –The Windows installation that hosts the TMS web application.
The TMS Web Server also requires a TMS SQL Server, which may or may not be
the same Windows Server.
TMS SQL Server –The Microsoft SQL Server hosting the TMS Database. In smaller
installations, this server is typically hosted on the same physical server that the TMS
Web Server is hosted on. In advanced installations, these tasks are usually different
physical Windows Servers.
tmsng
TMS Database –The main database used by TMS itself. This database is hosted by
the TMS SQL server role and the default name of the database is tmsng.
TMS SQL Login –The SQL Login used by the TMS Web Server to access the
tmsng database. By default, this is the sa account of the TMS SQL server, but can
be customized during TMS installation.
Data Warehouse Server –The Microsoft SQL Server that will be hosting the
Analytics Databases and SQL Server Analysis Services. This server can be the
same SQL server as the TMS SQL Server, but is recommended to be a separate
SQL server. This Server installation must be operational before attempting to install
the Analytics Extension.
tmsng_dwh
Data Warehouse Database –A new database created by the Analytics Extension
which serves as the long-term data repository for the Analytics Extension. This
database is hosted by the Data Warehouse Server
CUBE
Data Warehouse CUBE –A new specialized multi-dimensional database created by
the Analytics Extension which contains the pre-computed data created by the
Analytics Extension. This database is hosted by the SQL Server Analysis Services
Instance running on the Data Warehouse Server.
DWH Service User –A Windows domain user account used by the Analytics
Extension to log into the data warehouse Server for ongoing operations. This
account will be given the necessary permissions by the Analytics Extension installer.
DWH TMS Service User –A SQL Login for the TMS SQL server used by the
Analytics Extension to read data from the TMS Database. This account is a SQL
Login, not a Windows Account and must have at least the db_datareader role.
See Appendix 2: Creating User Accounts in Windows Server and Microsoft SQL
Server in the Analytics Extension installation guide for information on how to set up
such an account.

Analytics Extension components and roles
TANDBERG Analytics Extension Admin guide 8
tmsng_dwh
CUBE
tmsng
TMS Web Server
TMS SQL Server
Data Warehouse
Server
TMS SQL
Login
Analytics User
VCS
OLE DB
Feedback
Figure 1 Illustration of Roles and Components
Data warehouse server
Analytics Extension comprises two major elements - the data warehouse database, and the data
warehouse CUBE.
Data warehouse database
The data warehouse database is used for analysis and data mining, and contains information from the
TMS database combined with data computations and analysis.
Using credentials specified by the installing user, the data warehouse database is created in a SQL
server instance of the data warehouse server. The default database name is tmsng_dwh. The DWH
(data warehouse) service user is defined as the owner of the database, and this service account is
used by the Analytics Extension to log into the database for ongoing operations. Ordinary users of the
Analytics Extension API do not connect to this database.
The data warehouse database accesses the TMS database through a linked server, created on the
data warehouse server during installation. The linked server is created automatically using the TMS
database location and DWH TMS service user credentials supplied during installation. The DWH TMS
service user is an SQL login for the TMS SQL server. This account only needs db_datareader role to
access the TMS database, and can reuse any existing login with sufficient SQL access permissions.
Data warehouse CUBE
The data warehouse CUBE is a specialized type of database used in analysis and data mining. Its
main advantage is its ability to hold pre-computed aggregates of data across many different
dimensions, allowing fast manipulation of queries. The data warehouse CUBE holds different data
defined as „facts‟, each of which has different defined „dimensions‟, which can be used to manipulate
the information stored in the different fact tables. Analytics Extension provides sets of facts and
dimensions that can be used by programmers and analytics users.
The data warehouse CUBE is created during installation in the Analysis Services instance on the data
warehouse server. The default name for the database is tmsng_dwhAsDb (its name format must
always be <database>AsDb). The DWH service user is configured as the owner of the CUBE and the
DWH service user credentials are used by Analytics Extension to connect to the cube for ongoing
operations. Ordinary users of the Analytics Extension API connect to this CUBE using an OLAP client
and their Windows Domain accounts (with reader role permissions).
Add http pump ref
The data warehouse CUBE connects to the data warehouse database using a data source defined
configured to use the DWH service user credentials.

Analytics Extension components and roles
TANDBERG Analytics Extension Admin guide 9
Data warehouse updates
Data mining and long term analysis of information is done using historical data. The focus is on
historical data, instead of real-time data. Because of this, the data warehouse contents are not
recorded in real-time as in the traditional reporting information viewed via TMS. Instead the DWH data
is refreshed daily by the Windows Service installed on the TMS web server by the Analytics Extension.
The Windows Service initiates two updates, the ETL Task and the CUBE refresh. The ETL (Extract,
Transform, Load) task extracts from the TMS database all new (recorded since the last time the ETL
task run) data updates values and computations stored in the data warehouse database. Because
only new information is processed, the ETL task is extremely efficient. This task is initiated by the
Analytics Extension‟s Windows Service connecting to the data warehouse server at a scheduled time
using the supplied DWH service user credentials. The ETL job executes on the data warehouse server
and uses the defined linked server to read information from the TMS database. There is no significant
extra load placed the TMS web server. An administrator may also initiate the ETL job to run
immediately via the Analytics Extension web interface in TMS.
The CUBE refresh is performed daily after the data warehouse database has been updated by the
ETL task. To initiate this update, the Analytics Extension Windows Service on the TMS Web Server
connects to the Analytics Service on the data warehouse server using the DWH service user
credentials and tells the CUBE to refresh. The CUBE uses the connection properties defined in the
CUBE‟s Data Source definition to connect to the data warehouse database.
Service account permissions and uses
Data warehouse service user
The data warehouse service user account is used by Analytics Extension to log into the data
warehouse database and data warehouse CUBE. This user account must be a valid Windows Domain
account. It does not need any pre-existing permissions. It is also used by the data warehouse CUBE
to connect to the data warehouse database in the Database Engine instance.
If the account password changes the installation must be reconfigured. See the Analytics Extension
Reconfiguration section for more information.
DWH TMS service user
The TMS service user account is used by the data warehouse server to pull data from the TMS
database. This account must have an SQL Login capable of connecting to the TMS database tmsng.
It only needs SQL login access to the TMS and the db_datareader role on the TMS database.
If the account password changes, the installation must be reconfigured. See the Analytics Extension
Reconfiguration section for more information.

Adding and managing users
TANDBERG Analytics Extension Admin guide 10
Adding and managing users
There are two classes of users for the Analytics Extension:
administrators who have access to diagnostic information and settings
analytics users who are consumers of the Analytics Extension data output
SQL Server Analysis Services (SSAS) uses a strict security model and only Windows authentication is
supported. Any user or client who wishes to be an administrator or analytics user must have a valid
Windows Domain account the SSAS instance trusts. Some software clients allow a user to specify
which account they will use to authenticate to SSAS; others only support Integrated Authentication
where the credentials of your current user identity are used.
Analytics users
Users who wish to read data from the Analytics Extension must have access to the data warehouse
CUBE hosted by the SQL Server Analysis Service (SSAS). An account with SSAS access does not
necessarily have the right to read the data warehouse CUBE.
SSAS offers administrators a wide variety of permissions possibilities for users, but to ease
configuration the data warehouse CUBE has a custom reader role defined during installation. Users
who are members of this role will have access to read, but not modify any information in the data
warehouse CUBE. Access to the information made available via the Analytics Extension should be
granted by adding users to this reader role.
To add a new user to the reader role:
1. Open Management Studio, and connect to the relevant SSAS instance.
2. Locate Databases > tmsng_dwhAsDb > Roles.
3. Right-click Reader and select Properties.
4. Go to the Membership subpage. Click Add… to enter users or groups (Figure 1). To grant read
access to domain members, use the Everyone group. Use the Authenticated Users group to
exclude guest accounts and anonymous users.
Figure 1: Managing user rights for accessing the cube

Adding and managing users
TANDBERG Analytics Extension Admin guide 11
To remove a user from the reader role:
1. Open Management Studio, and connect to the relevant SSAS instance.
2. Locate Databases > tmsng_dwhAsDb > Roles.
3. Right-click Reader and select Properties.
4. Go to the Membership subpage. Select the user to remove and click Remove.
Administrator roles
Analytics extension administrators
To view the Analytics Extension web interface, you must be a member of a user group in TMS that
has the Configuration > Read permission. To run the ETL job, the user must be a member of a user
group in TMS that has the Configuration > Update permission.
Group membership and group permissions are managed in the User Administration pages of TMS,
located at Administrative Tools > User Administration.
Analysis services server administrators
Access to the data warehouse CUBE and data exposed by the Analytics Extension is controlled via
the SSAS administrator role. Administrative privileges for the Analysis Services instance are controlled
by the server role in the Analysis Services server instance.
By default, local administrators of the Windows Server are members of the server role and have full
access to all features and data in the server instance. Other users can be added to the server role to
grant them administrator rights. To add another user to the server role:
1. In SQL Server Management Studio, connect to the instance of Analysis Services.
2. Right-click the instance name in Object Explorer and then click Properties.
3. In the Select a Page pane, click Security.
4. Click Add to add one or more Windows users or groups to the server role.

Web interface
TANDBERG Analytics Extension Admin guide 12
Web interface
The Analytics Extension web interface is accessed via your existing TMS installation. This section
provides further interface information.
Note: If the server name or address used in the URL to access TMS and the address in the Analytics
Extension URL configured in Administrative Tools are different, you may be prompted with a
username/password dialog when accessing the Analytics Extension.
Figure 2: The Analytics Extension web interface
Download excel sample files
Sample Excel workbooks are included with Analytics Extension. See the TANDBERG Analytics
Extension installation guide for more information on installing these example spreadsheets.
Log ETL jobs
The ETL job extracts information from the source TMS database and updates the data warehouse
server databases. This panel shows the status and log details for past runs of the ETL job and can be
used to verify the job is running, or help diagnose why the data warehouse databases are not
updating.

Web interface
TANDBERG Analytics Extension Admin guide 13
Job Status –Shows a checkmark if the job was complete, a red x if it failed, or a gear icon if it is
currently in progress.
Start Time –The start time of the job, in TMS Server time
Batch Duration –How long the job has been running for or how long the job took to complete
Batch Errors –If a job fails, diagnostic information is listed here. Click on the entry to expand the
box and see more information.
Run ETL Job Now –Clicking this will manually initiate an immediate ETL job. Note this task may
take a significant amount of time to complete.
Note: This window does not automatically refresh. Click the Refresh button to refresh the list with the
latest information.

Reconfiguring Analytics Extension
TANDBERG Analytics Extension Admin guide 14
Reconfiguring Analytics Extension
Analytics extension provides wizard to help you through reconfiguring your setup. Common causes for
reconfiguration are to update a server address, an expired password, other account information, or
your server configuration or your network changes.
The wizard allows you to update:
destination database server name or IP address
user name and password for the data warehouse service account
source TMS database server name or IP address
While the reconfiguration wizard allows you to update the server addresses used by the installation, it
cannot move any databases or create new accounts. The wizard can only be used to update your
installation with external changes. If you need to change which servers host the TMS or data
warehouse databases, those changes must be made by the SQL Administrator manually and then use
the reconfigure wizard to update your software.
Note: TANDBERG recommends that the reconfiguration wizard not be used to replace the source
database with an entirely different TMS database. The reconfiguration wizard is useful in cases where
the underlying data is either the same, or a logical continuation of the current database (such as the
current database with more data).
Reconfiguring Analytics Extension will temporarily halt other web services on the same IIS, as the
World Wide Web Publishing Service will be stopped and restarted by the wizard. The expected
downtime is a few seconds.
The reconfiguration wizard will summarize your proposed changes and wait for confirmation before
making any changes.
1. Open the Start menu and select TANDBERG > Reconfigure TANDBERG Analytics Extension.
2. Follow the onscreen prompts.

Troubleshooting
TANDBERG Analytics Extension Admin guide 15
Troubleshooting
Web site issues
User does not have sufficient permissions in TMS to view this module
A bug in SQL server can produce this error even when permissions are set correctly. A failed login
attempt results in the following message in the web application log file log-AdminWeb.txt:
System.Data.SqlClient.SqlException: Login failed for user '<domain/account>'. Reason: Server is in
script upgrade mode. Only administrator can connect at this time.
This can occur after a fresh installation of Microsoft SQL Server or after installing a SQL server service
pack. To resolve this issue reboot the server.
An error has occurred!
This generic error message asks you to look in the logs for further information.
The most common cause is SQL connectivity problems. Look for an SQL Network Interfaces:
Error Locating Server/Instance Specified message in log-AdminWeb.txt.
Refer to the Analytics Extension installation guide for information on troubleshooting connection
errors.
Figure 3: The generic “An error has occurred” message
ETL job failures
Cannot insert the value NULL into column…
For some video networks with high MCU activity, the ETL job may fail with an error message:
Cannot insert the value NULL into column „PeakAudioCallBitrate‟
Cannot insert the value NULL into column „PeakVideoCallBitrate‟
Cannot insert the value NULL into column „PeakActualVideoCalls‟
This bug only occurs in Analytics extension version 1.0, and is fixed for version 1.1. A patch for
version 1.0 exists. Customers encountering this problem can contact TANDBERG Support to obtain
the patch.

Troubleshooting
TANDBERG Analytics Extension Admin guide 16
Figure 4: The “Cannot insert the value NULL into column…” bug as seen in the “Log ETL jobs”
panel.
Multiple “Error: Internal error: The operation terminated unsuccessfully.”
Getting multiple Error: Internal error: The operation terminated unsuccessfully.
messages in the same ETL job indicates that there have been major changes in the TMS database
since you last ran the job (Figure 5), and that the Analytics Extension is unable to extract data
because of schema changes or changes in constraints.
These errors can occur if you have used the reconfiguration tool to replace the source database with
an entirely different TMS database. As described in Analytics Extension Reconfiguration section, the
wizard tool should not be used for such changes. To fix the problem, use the reconfiguration tool again
to return the source database to the original TMS database.
Figure 5: Multiple “Error: Internal error: The operation terminated unsuccessfully.” messages
from the same failed ETL job.
Client Connectivity Issues
Microsoft Excel and Windows Authentication
Initialization of the data source failed
When connecting to a data warehouse Cube in environments where Integrated Authentication is not
available or fails, Excel will run into authentication problems. The user will not be warned about this
when creating the connection to Analysis Services and the connection will apparently be created
properly. However, later when the user tries to use the connection in a Pivot Table or Pivot Chart, the
user will get an Initialization of the data source failed error.
A workaround to the problem is appending the connection string manually:
1. In Excel, go to Data > Existing Connection.
2. Select the relevant connection, and click Open.
3. In the Import Data window that follows, click Properties…
4. Go to the “Definition” tab (Figure 6).
5. Append the text string in the “connection string” field with “;password=<your password>”. Note
the semicolon in front.

Troubleshooting
TANDBERG Analytics Extension Admin guide 17
Example: If the user account is named “peter.jones”, the connection string might look like this:
Provider=MSOLAP.3;Persist Security Info=True;User
ID=REPORTING\peter.jones;Initial Catalog=tmsng_dwhAsDb;Data
Source=analytics.reporting.tms.lab;MDX Compatibility=1;Safety
Options=2;MDX Missing Member Mode=Error
If the password of the account is “ENGLAND66”, the modified string should look like this:
Provider=MSOLAP.3;Persist Security Info=True;User
ID=REPORTING\peter.jones;Initial Catalog=tmsng_dwhAsDb;Data
Source=analytics.reporting.tms.lab;MDX Compatibility=1;Safety
Options=2;MDX Missing Member Mode=Error;password=ENGLAND66
Checking the “Save password” box will make the changes to the connection string persistent.
However, note that the connection string is stored unencrypted in an XML file on the local disk.
Figure 6: Manually appending the connection string
The LocaleIdentifier property
XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be
assigned a new value
After creating a connection, the error message XML for Analysis parser: The
LocaleIdentifier property is not overwritable and cannot be assigned a new
value may appear when you try to utilize the connection in a pivot table or chart. This issue only

Troubleshooting
TANDBERG Analytics Extension Admin guide 18
applies to certain combinations of old versions of Excel and SSAS. The following Microsoft Connect
page describes a workaround Error when creating an Excel Pivot Table from a SSAS project.
1. Right click the connection in the Select Data Source window and select Open with > Notepad
(Figure 7) to edit the connection file as described in the Microsoft Connect page,.
2. Locate the <odc:ConnectionString> element, and append “;Locale Identifier=1033”
(note the semicolon) to the end of the element‟s content (Figure 8). Click Save.
3. The connection is now updated and ready for use.
Figure 7: Opening the connection for editing

Troubleshooting
TANDBERG Analytics Extension Admin guide 19
Figure 8: Appending the connection string

Logs
TANDBERG Analytics Extension Admin guide 20
Logs
If you experience difficulties with the TANDBERG Analytics Extension, technical support may ask you
to supply log files along with a description of your issue. The follow sections outline the types of logs
available. For information on installer logs see TANDBERG Analytics Extension Installation Guide
Application Logs
Problems with the Analytics Extension web interface or the Windows Service that initiates the update
jobs are logged in files located on the web server where the Analytics Extension was installed. Logged
events from the Analytics Extension web interface and the Analytics Extension Service are stored in
the following locations:
<Installation Dir>\TANDBERG\Analytics Extension\ReportingService\Logs
<Installation Dir>\TANDBERG\Analytics Extension \AdminWeb\App_Data\Logs
These files are plain text but are low level and intended for debugging purposes by TANDBERG
personal.
Database Logs
The data warehouse itself also keeps logs useful for troubleshooting; these entries are stored in the
database itself in the table‟s orc.ExecutionEventLog. orc.ErrorEventLog. dwh.AppliedPatch, and
dwh.InstallerEventLog. These logs may be browsed using any SQL tool. To save all the entries in
these logs to a file:
1. Open Management Studio and connect to the database engine instance
2. Expand Databases, find your data warehouse database (tmsng_dwh by default)
3. Right-click on the database and select “New Query”.
4. Set the Results to save to a file. Right click in the Query Menu, and select Results to > Results
to File.
5. In the query window, type in the follow four commands
SELECT * FROM orc.ExecutionEventLog;
SELECT * FROM orc.ErrorEventLog;
SELECT * FROM dwh.AppliedPatch;
SELECT * FROM dwh.InstallerEventLog;
6. Click the Execute button to run the query. You will be prompted for a file name to save the results
to. This will be a CSV file of the output you can share with technical support if requested.