CREATING AND USING OLAP CUBES in EXCEL

1
CREATING AND USING OLAP CUBES in EXCEL
2
Building and Using an OLAP Cube
Creating an OLAP Cube through a Database Query in
Excel
Select data, get external data, and then new
database query from the pull downs
3
Next select a Data Source (an Access Database or
an ODBC Link to ORACLE)
4
Next Select the Tables and Columns to be Stored
in the Cube
You Can now filter (where conditions)
and/or Sort the data to be stored in the cube
5
The Final Dialog for Creating the Query Allows
you to

• Place query results in an Excel worksheet
• View or edit the query in Microsoft Query
• Create an OLAP Cube storage structure for the
  query (our Choice).

6
Choosing to Create an OLAP Cube Causes the OLAP
Cube Wizard to be Displayed with Dialog Boxes to
Guide its Creation
7
The First OLAP Wizard Dialog Box Lets You Select
the Summarization to be Performed on the Data
Fields of the Cube
You may select SUM, Max, Min, or Count,
8
The Next Dialog box allows you to define
dimensions
Items are drug from the source fields list to the
dimensions area. By dragging an item on-top of an
existing dimension, you are indicating that it
is a more detailed item in the same dimension.
After the State dimension was created, the city
and Zip_code were drug to its dimension and thus
appear as sub categories within that dimension.
Similarly, route was drug to the Orig dimension
to become a subcategory there. Time values, can
be automatically sub grouped by Year, Quarter,
Month, Week and Day. Since our data is monthly,
the Week and Day options are turned off.
9
Saving the OLAP CUBE File

• The OLAP Cube can be saved in a special Microsoft
  cube file or can be rebuilt from the
• Source data when needed.
• We choose to save the cube file since it can be
  reused without new data throughout the month.
• You will also be prompted to save the Query file
  that references the cube.

10
Next you are Prompted to Use the OLAP Cube as
the Source for a Pivot Table
11
Items are Drug and Dropped as in Any Pivot Table
Note that some of the fields Are actually
dimensions Containing a hierarchy of Categories
State State, City, Zip_Code Orig Orig,
Route, Flight_no Month Year, Quarter, Month
Pivot Table is a toolbar that can be hidden or
exposed From View Toolbars.
Pivot table Field List can be hidden or displayed
by Right clicking in the area of the pivot table
and Toggling the Show/Hide Field List option
12
Fields are Drug and Dropped Into the Table Layout
The layout as shown has had dimensions for State
and Year drug to appropriate spots and Is
awaiting the placement of one of the Summary
Fields from the field list in the data items area
(cell A3) of the layout.
13
Once the Layout is full the Pivot Table Appears
as Below
By clicking on Combo Box down arrow, you can get
a display of the hierarchy of that dimension And
can change the scope and detail of the Table.
14
Table with Expanded Detail
Clicking the Chart Icon will Display a Chart of
the Data and operates just as it does
for Standard pivot tables and charts.