Why use OLAP or Microsoft Analysis Services 2005

Business Scorecard Manager can use business data from any Open Database Connectivity (ODBC) or ADOMD.NET data source to create the KPIs used to build scorecards. Business Scorecard Manager can also combine data from multiple sources in a given scorecard. ODBC and ADOMD.NET are widely interoperable with a variety of documents and databases, enabling users to build scorecards with the existing sources of data used by their organization. 

OLAP is the best solution for your Scorecard and Reporting solution because it provides fast and interactive access to aggregated data and the ability to drill down to detail. OLAP lets users view and interrogate large volumes of data (often millions of rows) by pre-aggregating the information. It puts the data needed to make strategic decisions directly into the hands of the decision makers, not only through pre-defined queries and reports, but also because it gives end users the ability to perform their own ad hoc queries, minimizing users’ dependence on database developers.

OLAP leverages existing data from a relational schema or data warehouse (data source) by placing key performance indicators (measures) into context (dimensions). Once processed into a multidimensional database (cube), all of the measures are pre-aggregated, which makes data retrieval significantly faster. The processed cube can then be made available to business users who can browse the data using a variety of tools, making ad hoc analysis an interactive and analytical process rather than a development effort.

There are numerous reasons to move your transactional data into an OLAP database:

§         Friendliness. Business data is typically stored in relational databases optimized for data input or storage and not analysis of that data.  Names of columns etc. are typically not intuitive to end users, there are no clear relationships between fields, etc.  Analysis Services provides a user-friendly model where you can provide understandable business names, specify relationships between fields (Product Category – Product Subcategory – Product) so that it is possible for business users to design their own reports without help from IT.

§         Personalization.  Analysis Services offers tools for personalizing individual users’ reporting experience by only showing them the data that they care about and have permissions to see; in addition, Analysis Services can translate data into users’ preferred languages.

§         Analytical capabilities.  Key Performance Indicators, calculations, conditional formatting, and actions are just a few examples of business logic that you can define once in Analysis Services and then expose automatically in Excel PivotTables.  Part of the beauty of this is that all users see the same thing in their PivotTables because the formatting, for example, is calculated in one place – on the server.

§         Fast analysis.  Analysis Services aggregates data so that analytical queries that might take minutes when executed against a relational database are typically executed in less than a second with Analysis Services.

§         One version of the truth.  When analyzing data in Analysis Services, all the business logic is centrally managed in one analytical model so that every user will see the same numbers, calculated using the same business logic.  Any changes made to the model will immediately be available to all Excel PivotTable users when they update their report.  No more worrying that different users with different copies of the spreadsheet have different financial results.

§         One consolidated analytical model.  Analysis Services allows you to consolidate data from different business systems into a single analytical model.  For example, you might have some sales data in an Oracle database and some customer data in a SQL Server database but for analysis that you would like to see in the same report. With an Analysis Services model, you can do just that without needing to change the source system at all.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s