How to use Power Pivot for Excel on your Exact Globe database.

As described in my previous blog, easier to build reports on a Exact Globe database with your preferred reporting tools by making use of the Globe reporting views, I described the introduction of the Globe reporting views. In this blog I will describe how you can use these reporting views with Power Pivot for Excel (one of my favorite 10 features of SQL 2008 R2).  PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within Excel. It’s the user-friendly way to perform data analysis using PivotTable and, PivotChart views, and slicers.

To make it a little bit easier for you I have created an Power Pivot for Excel sheet with some pre configured charts and tables on it. Of course in combinatie with the slicers. Last but not least, some relations between the different views. These relations are very important to analyze and combine different types of data.

This Power Pivot for Excel sheet make use of the Globe Reporting Views (GRV_*). As of Exact Globe product update 402 the first version of the reporting views are shipped. These views will only be installed as of SQL 2005.

In this Excel sheet you will find tabs for:

1. Revenue Overview (Slicers on: Year, Period,Country, Statecode, Sectorcode, Employee)
2. Revenue Costcenters and Projects (Slicers on: Year, Period,Project, Costcenter)
3. Cost Costcenters and Projects (Slicers on: Year, Period,Project, Costcenter)
4. Sales Account Analyze (Slicers on: Year, Period)
5. Sales Employee Analyze (Slicers on: Year, Period)
6. Sales Item Analyze (Slicers on: Year, Period)
7. Sales Assortment Analyze (Slicers on: Year, Period)
8. Top Suppliers (Slicers on: Year, Period)
9. Margin Items (Slicers on: Year, Period, Assortment)

Before starting you need to have:

• MS Office Excel 2010, preferable 64 bits version.
• Globe database installed on SQL 2005 or higher.
• Power Pivot for Excel.
• Globe Reporting Views installed on your Globe database. In case you can't wait for Globe release 402. Download the script to install the views.
• Read access to the Globe Reporting Views
• Download and Open the GRV_PowerPivotGlobe excel sheet.

Now it is time to load your Globe data in your sheet. Next steps are described in my blogpost: How to change the database for your Power Pivot sheet.

1. Change the database connection to your Globe database
2. Update (Refresh) the the Power Pivot Window.
3. Update (Refresh) the Excel sheet.
4. Now you are ready to analyze your Globe administration in Power Pivot for Excel.
5. Adjust the Charts, Pivots, tables etc to your own preferences.

Enjoy the power of Power Pivot for Excel on your Globe databases.

White paper Analysis Services 2008 R2 Performance Guide

This month Thomas Kejser and Denny Lee published the white paper: Analysis Services 2008 R2 Performance Guide. This white paper describes how business intelligence developers can apply query and processing performance-tuning techniques to their Microsoft SQL Server 2008 R2 Analysis Services OLAP solutions.

This guide contains information about building and tuning Analysis Services in SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2 cubes for the best possible performance. It is primarily aimed at business intelligence (BI) developers who are building a new cube from scratch or optimizing an existing cube for better performance.
The goal of this guide is to provide you with the necessary background to understand design tradeoffs and with techniques and design patterns that will help you achieve the best possible performance of even large cubes.
Cube performance can be divided into two types of workload: query performance and processing performance. Because these workloads are very different, this paper is organized into four main sections.
Design Patterns for Scalable Cubes – No amount of query tuning and optimization can beat the benefits of a well-designed data model. This section contains guidance to help you get the design right the first time. In general, good cube design follows Kimball modeling techniques, and if you avoid some typical design mistakes, you are in very good shape.
Tuning Query Performance - Query performance directly impacts the quality of the end-user experience. As such, it is the primary benchmark used to evaluate the success of an online analytical processing (OLAP) implementation. Analysis Services provides a variety of mechanisms to accelerate query performance, including aggregations, caching, and indexed data retrieval. This section also provides guidance on writing efficient Multidimensional Expressions (MDX) calculation scripts.
Tuning Processing Performance - Processing is the operation that refreshes data in an Analysis Services database. The faster the processing performance, the sooner users can access refreshed data. Analysis Services provides a variety of mechanisms that you can use to influence processing performance, including parallelized processing designs, relational tuning, and an economical processing strategy (for example, incremental versus full refresh versus proactive caching).
Special Considerations – Some features of Analysis Services such as distinct count measures and many-to-many dimensions require more careful attention to the cube design than others. At the end of the paper you will find a section that describes the special techniques you should apply when using these features.

Easier to build reports on a Globe database with your preferred reporting tools by making use of the Globe Reporting Views.

A lot of data is stored by business solutions in one or more databases. Every business solution will ship a lot of overviews to support your business process. However, a lot of users requires some custom made reports, which are not part of the standard solution. As of today a lot of reporting tools are available to build reports on your database. For instance:

• SQL Server Reporting Services (SSRS)
• Crystal Reports (CRW)
• Excel
• Power Pivot for Excel

Every reporting tool has it's own advantages and disadvantages. It's up to the report builder to use his favorite reporting tool.

Building reports can be divided into next steps:

1. Define the goal of your report.
2. Retreive the data from the data source.
3. Make calculations with the data you have retrieved.
4. Format the report according your UX design rules.

The report builder expert will know all in's and out's of the reporting tool. However this expert does not always understand the business logic of the solution. To get a better understanding you need to read and understand the database documentation of the solution. I will explain with one example: How can I get all outstanding invoices of customer X. To answer this question you need to know in which tables this information is stored but also how it is stored. For Exact Globe next tables are used: GBKMUT, BankTransactions, CICMPY. The outstanding items query will contain 140 lines of code. So being a report builder expert you can still not build the requested reports if you do not understand the business logic.

To solve the challenge of the business logic we have developed database views which contains the business logic. The view will present the data in an understandable format which can be used in the report.

The advantages of using views:

1. Business logic is in the database, not in the report. If business logic changes, the view will be updated with the new logic. No changes are needed in the report.
2. Data is presented in an understandable way. For instance 'Supplier code' instead of a databasse column name: 'cicmpy.crdcode'
   
   
3. Data sets in the reports are easier to read and maintain. 
4. Performance can  be better guaranteed.

As of Exact Globe product update 402 the first version of the reporting views are shipped. The Globe Reporting Views start with 'GRV_' in their name.  In my next blog post I will describe how you can use these views with Power Pivot for Excel.