This document provides a technical overview of Business Analytics granule in Microsoft Business Solutions-Navision. It describes the technical architecture of Business Analytics, identifying key components and their purpose in the overall solutions. It describes the steps that are needed to implement and use Business Analytics. It provides an overview of how to setup and implement base and advanced granules.

Business Analytics for Navision

Business Analytics in Navision 4.00 is a general purpose analytics and reporting solution that has been developed to enable easy access to relevant business information. It uses a multi-dimensional data store that allows users to get quick and updated information at the summarized level with the capability to do analysis by drilling down into the details from the summarized data. It provides user specific reporting and analysis capabilities to all employees within the business so that they can make informed business decisions without losing time waiting for business information.

Different business decisions often demand different types of information from various competencies within the business. Business Analytics allows users to access dynamic information from multiple business areas simultaneously, with the capability to combine information selectively. Users can access their customized information and analyze them through the interface of their choice, Microsoft Excel, over the Web, or through a Microsoft Windows based Business Analytics client.

In order to realize the full potential of Business Analytics, it is important that you understand what its key components are, how they work to enable their functionality, and how you can set up and configure this application. The purpose of this document is to provide an understanding of how Business Analytics works, what the possible hardware configurations are for deployment, and how the application can be configured for specific usage. This document provides an architectural overview of Business Analytics, deployment scenarios, and some tips and tricks to benefit deployment and usage.

This White Paper is divided into two sections. The first section describes the architecture of Business Analytics, including conceptual overview of OLAP systems. The second section describes usage and deployment scenarios.

Architecture Overview

Business Analytics comes in two granules: Business Analytics–Base Functionality and Business Analytics Advanced. Business Analytics–Base Functionalityis a prerequisite for Business Analytics Advanced. The advanced solution consists of additional components that work with the base solution components to enable advanced analysis and reporting functionality. This section provides the details of these components and their purpose in the overall solution. Business Analytics consists of the following main components:

Business Analytics–Base Functionality

Business Analytics Data Mart
Data Transformation Services package(s) for Data Transfer
Business Analytics Configurator

Business Analytics Advanced

Business Analytics Server
Business Analytics Client
Business Analytics Web Server
Business Analytics Enterprise Manager

The following diagram shows the component layout for Business Analytics.

Business Analytics Data Mart

Business Analytics uses a multi-dimensional data store for data analysis and reporting purposes. This data store consists of two components, a set of OLAP cubes and a relational data mart which works as staging environment. OLAP cubes facilitate rapid, sophisticated analysis on large and complex data sets. Saving analysis data in OLAP cubes enables:

Ease of selecting, navigating, and exploring the data. This includes slicing and dicing data based on different dimensions, drill down and drill up of data from different summary levels. For example, a regional sales manager can look at summarized sales data for a region, and then selectively drill down into detailed sales by customer, by product, or by time.
Pre-calculation of frequently queried data that enables very fast response time to ad hoc queries.

Business Analytics uses Microsoft SQL Server Analysis Services to instantiate and host the cubes. OLAP cubes mainly contain two entities: dimensions and measures. Dimensions are organized hierarchies of categories that describe similar sets of members upon which the user wants to base an analysis. In the above example, dimensions namely are market, product and time. A measure is a set of values that are analyzed, that is, measures are the numeric data of primary interest to end users browsing a cube. The measures one selects depend on the types of information end users request. Some common measures are sales, cost, expenditures, and production count.

For more information on OLAP, refer to Data Warehousing and Online Analytical Processing.

A typical deployment of Business Analytics can contain several data cubes, one for each subject area. For example, there can be one cube for sales, another for finance etc. Even though subject matter data is partitioned into individual cubes, users can do analysis of data across two or more cubes using virtual cubes. A virtual cube is used to encapsulate a subset of the measures, dimensions, and levels contained in one or more cubes. A virtual cube, like a view in a relational database, is a logical construct that itself contains no data. Just as a view is a join of multiple relations, a virtual cube is a join of multiple cubes.

Data is populated in OLAP cubes from a relational database, often referred to as relational BA data mart. This relational database is hosted using Microsoft SQL Server and it stores cleansed and consolidated data from the Navision database. Data in the relational data mart is stored in a de-normalized multidimensional schema, which is also commonly known as star or snowflake schema.

Package for Data Transfer Service

Business Analytics Data Mart gets populated from the source Navision database using DTS (Data Transformation Services) packages. Data Transformation Services is part of Microsoft® SQL Server™ 2000 which is a set of graphical tools and programmable objects that enable extracting, transforming, and consolidating data from disparate sources into single or multiple destinations. Business Analytics creates a set of DTS packages to load data to the relational Data Mart and also into the OLAP Cubes. BA schedules execution of these DTS packages with the help of Microsoft SQL Server 2000 Agent jobs.

Business Analytics Configurator

While Business Analytics comes with a predefined set of cubes, businesses deploying Business Analytics can configure their own cubes in order to bring in data from customized tables in Navision or bring additional data fields that are not a part of the pre-defined cubes from the Navision database. Business Analytics allows implementers to customize cubes according to individual business needs through a configurator. Business Analytics configurator consists of two components:

Configuration Form
Configuration Engine

The configuration form is accessible only from within Navision. It is a design tool that is primarily meant for implementers and power users. It allows users to define their own cubes by selecting tables and fields from Navision database. A configuration is created by starting with a unique configuration name. Each configuration can contain definitions of multiple cubes. To define a cube, users need to specify a cube name and then pick the tables/fields that represent the measures and dimensions of the cube. For each table, the configurator shows the possible fields that can be selected as measures and dimensions.

The configuration form allows users to import and export a configuration from/to a predefined XML file. The configurator also comes with a predefined cube definition file that serves as an example for creation of cubes. Customers can use this to understand how the configuration form works.

If there is more than one configuration, users can select a configuration to be active. The configuration engine reads a configuration definition and creates the schema for data mart and the DTS packages for loading data into the data mart. The configuration engine needs to be invoked specifically by the users through the configuration form.

Business Analytics Client

Business Analytics client is a component of the advanced granule that can be used for advanced analysis and reporting purposes. It provides several objects for viewing the data, like charts, maps, gages, and tables. A collection of objects can be stored in a view for later retrieval. Each user can create multiple views to facilitate different usage. Even though views are user specific, they are stored in the Business Analytics Server, thus allowing users to be machine independent.

Business Analytics Client provides three key user functions:

Data Analysis
Data Mining
Report Creation, Viewing, and Scheduling

The Business Analytics client requires a Business Analytics Server for access to the cubes created during Business Analytics configuration.

Business Analytics Server

Business Analytics Server is a component of the advanced solution only. It is a Windows service that provides user interface services for Business Analytics client. It is a multi-threaded service, capable of supporting multiple clients simultaneously.

Business Analytics Server has been developed to render a high performance user experience. It takes analysis requests from the client and converts them to low-level database queries that are sent to the Business Analytics data mart. It uses a patented query optimization technique, which ensures that only the least time and resource consuming queries are sent to the database. In addition, it caches previous analysis results. If a request is repeated by the client, it returns the cached answer thus avoiding unnecessary queries.

Business Analytics server also works as language and security layer in the advanced granule. User privileges are defined within and authenticated by Server.

Business Analytics Web Server

Business Analytics Web Server is a Business Analytics Server add-on offering thin client /zero-footprint access to the Business Analytics solution. Business Analytics Web Server is based on .NET technology and is enabled through Internet Information Server. End users can create new views and reports or access views and reports defined using the Business Analytics client using Internet Explorer 5.5 or higher without installing any additional software on the client machine.

Business Analytics Enterprise Manager

Business Analytics Enterprise Manager is the administrative interface of Business Analytics Server. In the Enterprise Manager, administrators configure:

User rights and data access rights – either inherited from Analysis Services (Kerberos required) or managed by Business Analytics Server.
Report scheduling
Import of Graphical elements for use in Business Analytics client
Dimension and measure (business term) translations

Using Business Analytics

This section describes the steps that you need to take in order to start using Business Analytics. It, however, does not provide the details of installation steps. In order to learn how to install Business Analytics, please refer to Business Analytics Installation Guide.

Setup

The setup steps mentioned below are the common steps that you would need to perform for both the Base Granule and Advanced Granule.

Gather Requirements

The first step in the setup and implementation of Business Analytics is to understand the business requirements from prospective users. Very often, the requirements can be understood by looking into the details of business questions that users would want to answer. For example, if the users want to analyze sales, the questions may include:

What is the sales trend by region by time?
What are five most profitable products in each product category?
How does this year’s sales compare with last two years by quarter?

Identification of analysis pattern and key business questions can help determine the structure of OLAP cubes that would ultimately source the analysis and reports.

Identify Dimensions, Measures, and Functions

Once the business requirements are collected, next step is to identify entities namely dimensions and measures that can answer these questions. This requires mapping the questions to Navision tables and identifying where the data will come from, what relationships and functions would need to be applied to the source data etc.

Identify Source Tables and Create Configuration

After identifying dimensions and measures, the next step is to map them to the corresponding source that would contain this data. Use Business Analytics configuration form to identify and select Navision source tables and fields that contain the data for dimensions and measures. Configuration form saves metadata information about the selection made in XML schema in the configuration file. This XML files is later used to create a business schema.

Execute Configurator

After defining a configuration, you can run the configuration engine to create business schemas including relational data mart, OLAP cubes and DTS package that contains the logic to move data from Navision tables to the relational data mart, and from there to the OLAP cubes. Running the configuration engine also executes the DTS packages to populate relational data mart and OLAP cubes.

One can also specify the schedule for data population in the data mart and OLAP cubes from here. This creates Microsoft SQL Server Agent job to automatically run the DTS packages periodically.

Using the Base Functionality

Viewing Data in the Base Functionality

After running the configuration, the OLAP cubes are ready for analysis and reporting. The Base Functionality Granule does not come with a client tool. For the users of base granule, the best option to view and analyze data is to use client tools like Excel Pivot Tables, Office Excel Add-in for SQL Server Analysis Services or one of the third party tools. All these tools provide a way to connect to the OLAP cubes just created. What you would need to know is the name of the server OLAP Services is installed on, name of the OLAP database the cube resides in and names of OLAP cubes and then you should be able to build reports with desired dimensions and measures. For more information on how to use the specific client tool, refer to its usage manual.

Setting Up Security an User Privileges in Base Functionality Granule

It is a common business need to restrict access to data based on the user. Base Functionality Granule does not provide any special feature to enable this. However, you can use the security features of Analysis Services for this purpose. Analysis Services provides data level security with the granularity of a cell (referred to as cell level security) and also based on specific dimension or dimension member. For more information of Analysis Service security model, refer toSecurity and Authentication.

Hardware Configuration

While it is difficult to propose a generic hardware configuration for all users, as each deployments needs could be different, it is recommended that you keep the data mart and Navision database on two separate machines. Depending upon performance needs, further optimization can be achieved by separating relational data mart from the server that hosts OLAP. This may however cause more network traffic during cube processing.

Using the Business Analytics Advanced Granule

The advanced granule is installed separately from Navision using its own setup program. It can be used in a two tier client server mode simultaneously with a three tier web client mode. If the users need to access data and do analysis through a Web browser, then Business Analytics Web Server would also need to be installed using Internet Explorer 5.5 or later. Alternatively, users can install Business Analytics clients on their desktop.

There are a few differences in the use of advanced granule from the base granule. The advanced granule provides built-in support for security and user privileges. You do not need to explicitly use the security features of Analysis Services for this purpose. You can use Business Analytics Server to configure security and authentication.

For further information on using the Business Analytics Advanced granule, see Business Analytics online help or product documentation.

Hardware Configuration

If you are installation advanced granule, the hardware configuration for Business Analytics Data Mart can remain the same as that in the base granule. Depending upon the performance needs, you may want to separate Business Analytics Server from the machines hosting the data mart.