Microsoft Excel Pivot Tables, Exams of Designs and Groups

Download Microsoft Excel Pivot Tables and more Exams Designs and Groups in PDF only on Docsity!

Microsoft Excel

Pivot Tables

General instructions with

exercises on BI4Dynamics data

1 PREFACE

1.1 How to use this document

This document’s purpose is to empower BI4Dynamics users to fully benefit from BI4Dynamics (as a source of data) and Microsoft Excel (as a viewing data software). This document is presented in two parts:

Part 1: How to use prebuilt Excel reports made on top of BI4Dynamics

For easier start BI4Dynamics created set of predefined reports that can be connected to Analysis data base that is created with BI4Dynamics installation Wizard. Please note that you need successfully complete the installation to use prebuilt reports.

Part 2: Excel exercises for creating your own reports and getting most of BI4Dynamics

As every company has its own needs and challenges BI4Dynamics delivers all you can imagine content that can be used for analysis. With drag and drop Pivot table functionality you have unlimited potential of building reports.

1.2 BI4Dynamics

BI4Dynamics is complete Business Intelligence solution that is specially build for Microsoft Dynamics AX & NAV. BI4Dynamics covers all Microsoft Dynamics AX & NAV application areas and includes numerous built-in calculations for endless analysis possibilities. F.e. BI4Dynamics NAV Sales module alone offers 254 measures, 37 dimensions and 137 attributes.

It is open and completely customizable and it also serves as a framework on which you can extend the solution to fit your needs. Customization Wizard enables building new cubes, modifying built-in cubes and adjusting setup.

As BI4Dynamics main focus is transforming your data into knowledge by storing your data into Data Warehouse and creating advanced calculations to empower you with the data and unique version of the truth that is available to your entire organization. To learn how to use it in best possible way we created this guide about using Excel Pivot table.

2 INTRODUCTION

2.1 Understanding dimensions and cubes

In this chapter, the difference between cubes, dimensions, attributes and hierarchies are presented.

 Every cube is composed of different dimensions and different set of measures.  Dimension consists of single attributes that are grouped in predefined hierarchy.  Hierarchies have the possibility to drilldown by levels thus making it easier for the business to quickly analyze the granular data.  Many attributes are visible and many more are hidden. They can be made visible via the Bi4Dynamics customization wizard or by modification of properties in Microsoft Analysis Server (cube).

Example:

Picture: Item Category Group by measures: Stock Quantity, Stock Value, Stock Rotation Days.

Measures can be viewed over different dimensions and their hierarchies or attributes.

2.2 General features of BI4Dynamics

2.2.1 One Installation, Any Database, Any Company,

BI4Dynamics can join data across any supported version of Microsoft Dynamics NAV database and company within – in one single data warehouse.

2.2.2 Global dimensions

Each measure in a specific OLAP cube can be viewed over multiple companies and over eight global dimensions.

3 CONNECTING TO DATA

Data is the base of every analysis and we will use Excel to connect to the OLAP cubes, where the data is stored and prepared for the business user.

OLAP cubes reside on the SQL Analysis Services Server, so in order to get to the data, we first need to connect to the server.

3.1 Creating a new Connection

Procedure for connecting to OLAP cubes on Analysis Services is as follows:

Go to: Data >> Get External Data >> From Other Sources >> From Analysis Services

In Connect to Database Server window write your server name. Choose Windows or User authentication. Press Next.

In Database and Table window choose your database and select designated cube. Press Next.

In Save Data Connection and Finish window choose the file name for your data connection. We recommend you also fill in Description, Friendly Name and Search Keywords fields. Press Finish.

As an optional last step you can also select where and what you want to create based on this connection ( Pivot Table Report – pivot table, Pivot Chart and Pivot Table Report – pivot chart and pivot table, Only Create Connection – Create connection, Existing worksheet , New worksheet )

You are now left with a blank pivot table placeholder, pivot table field list and pivot table tools tabs. Also a new connection file has been created on your file system (by default C:\Users\YOUR USERNAME\Documents\My Data Sources).

3.3 Changing an existing report connection

Once you create a pivot table that pivot table will be associated with a connection. You can always change the connection the pivot table uses.

Go to: PivotTable Tools >> Change Data Source

Then in the next window click Choose Connection and select the connection you want to use.

3.4 Refreshing a connection

To refresh the data, you need to refresh the connection which effectively connects to the OLAP cube and returns the latest data available.

One connection can only be linked to a single OLAP cube. You can however have several connections in a workbook, though each pivot table can only be based on a single connection.

You can

 Refresh individual connections (Refresh) or  Refresh all connections at once (Refresh All).

Go to: Data >> Refresh All (or Refresh)

3.5 Connection properties

Each individual connection has several properties you can set. To view the list of all connections in a workbook go to Data >> Connections, which will bring up the Workbook Connections windows.

3.6 Changing language

Translations for all languages are available in every cube. Users can set-up local connection string (in Excel) with Locale ID and change the display language in BI client (Excel).

Language Locale identifier

Connection string

Croatian 1050 Locale identifier=

Czech 1029 Locale identifier= Danish 1030 Locale identifier= Dutch - Belgium 2067 Locale identifier= Dutch - Netherlands 1043 Locale identifier= English - United States 1033 Locale identifier= Estonian 1061 Locale identifier= Finnish 1035 Locale identifier=

French - France 1036 Locale identifier= German - Germany 1031 Locale identifier= German - Switzerland 2055 Locale identifier= Italian - Italy 1040 Locale identifier= Lithuanian 1063 Locale identifier= Norwegian (Bokmål) 1044 Locale identifier= Polish 1045 Locale identifier= Portuguese - Portugal 2070 Locale identifier=

Serbian (Cyrillic) 3098 Locale identifier= Serbian (Latin) 2074 Locale identifier= Slovak 1051 Locale identifier= Slovenian 1060 Locale identifier= Spanish - Spain (Traditional Sort) 1034 Locale identifier= Swedish 1053 Locale identifier=

Table of available languages and locale identifiers. This feature is available on STANDARD version of Microsoft SQL server.

3.7 Exercises

3.7.1 Exercise 1 – Creating a new connection

Step 1: Select to create a new Analysis Services connection

Step 2: Connect to the server BI4NAV

Step 3: Select the BI4NAV database

Step 4: Select sales cube

Step 5: Rename you connection

Step 6: Select to create a pivot table report on the existing worksheet

3.7.2 Exercise 2 – Use an existing connection

Step 1: Select to use existing connection

Step 2: Select the connection you created in the previous exercise

Step 3: Select to create a pivot table report on the existing worksheet

4.2 Pivot table field list

Pivot table field list contains dimension and measures.

By checking checkboxes we filter the data cube.

Checked attributes are automatically positioned in pivot table rows and columns. This can be done manually by dragging and dropping the attribute into Column Labels , Row Labels , and Values in Report Filters.

For each dimension we can examine its hierarchy by clicking on +.

Filters can be added to reports in Report Filter.

Top – MEASURES Bottom – DIMENSIONS

---

Sort is alphabetical

4.3 Pivot table preview

Pivot table preview is divided in data description (dimensions) part and data value part.

Data description part contains:

 Header filter (global data filter),  Row filter (filter applied on rows) and  Column filter (filter applied on columns).

4.4 Pivot Table tools tabs

PivotTable tools tabs are automatically shown when clicking on one or more pivot table cells.

1. Pivot Table Name – name of pivot table. 2. Options – changing pivot table options.
2. Active Field – show active cell name. _4. Field Settings – changing cell settings (sum, format cell…).
3. Expand Entire Field – entering deeper hierarchy level of selected cell
4. Collapse Entire Field – exiting deeper hierarchy level of selected cell._
5. Group Selection – active on column and row header. Grouping data.
6. Ungroup – active on column and row header. Ungrouping data.
7. Sort – sorting data (ascending, descending,).
8. Insert Slicer – inserting different slicers.
9. Refresh – refreshing data from the data source.
10. Change Data Source – choose a different data source.
11. Clear – clear pivot table.
12. Select – select parts of pivot table.
13. Move Pivot Table – moving pivot table (into new sheet).
14. Summarize Values By – group data (power pivot).
15. Show Values As – customize pivot table values, % of total, …
16. Fields, Items, & Sets – defining additional groups.
17. Pivot Chart – adding pivot chart.
18. Formulas – adding formulas.
19. OLAP tools – Working without a connection to OLAP cube and converting into formulas.
20. Field List – turn on/off data field list.
21. +/- Buttons – turn on/off entering into dimension hierarchies.
22. Field Headers – turn on/off pivot table header.

5 WORKING WITH PIVOT TABLES

5.1 Creating, modifying and deleting the pivot table layout

The first thing we need to do before designing our pivot table is to think about which dimensions and measures we are going to need.

Once we have identified them, we can select or just drag & drop them to the desired location in our pivot table. The fields will be shown automatically in the pivot table preview.

We can rearrange, add or remove the fields from our pivot table at any time by dragging and dropping or by using the arrow buttons and selecting the action from the contextual menu that comes up.

Click on the arrow next to Date YMD and select Move to Column Labels. Dimension Date YMD will be moved from rows to columns. Move to Row Labels moves dimension from columns to rows. (Row is active only if data in the column was previously selected) We can also use Drag and drop.

5.2 Nesting dimensions

When we set up multiple dimensions in columns/rows, we are talking about dimension nesting. This is very useful when we want to see related information from different dimensions grouped together.

In our example we want to see how well our salespeople did within the years. In the pivot table field list we select the Salesperson - Purchaser dimension and drag it below the Data YMD dimension. The order in which dimensions appear is important, because the order defines the grouping.

Adding dimension: Result:

5.3 Exercises

5.3.1 Exercise 1 – Creating a new pivot table

We will create a report showing Net Sales by Customer by country over years

Step 1: Drag Customer by country to row labels

Step 2: Drag Date YQMD to columns labels

Step 3: Select Net Sales and see it being added to the Values