(For more details on Kyubit Business Intelligence read full User Manual)
3. Grid
Analysis features. 13
3.1. Start Analysis. 13
3.2. Grid Analysis concepts. 13
3.2.1. Cube
structure (Tree) 14
3.2.2. Measures. 15
3.2.3. Categories
and series. 15
3.3. Expand member, single dimension hierarchy. 15
3.4. Expand member, different dimension hierarchy. 16
3.5. Expand all/Collapse all members. 17
3.6. Drill down member. 17
3.7. User analysis settings. 18
3.7.1. Grid
analysis Aggregates. 18
3.7.2. Show/hide
empty rows. 18
3.7.3. Edit
grid column width. 19
3.8. Filtering grid analysis data. 19
3.8.1. Search
Members. 20
3.8.2. Isolating. 20
3.8.3. Dimension
level value filtering. 21
3.9. Sorting all members on grid axis. 22
3.10. Sorting members on a particular dimension level 23
3.11. Pivot 24
3.12. Grid values in percentage. 24
3.13. Create and manage ad-hoc User Named sets. 25
3.13.1. Create
Named Set by picking members. 25
3.13.2. Create
Named Set by expression. 25
3.13.3. Edit
existing User Named Sets. 26
3.14. Create and manage ad-hoc User Calculated Measures. 27
3.14.1. Edit
existing User Calculated Measures. 28
3.15. Save analysis state. 29
3.16. Share analysis. 29
3.17. Analysis history, move back and forward. 29
To start analysis, please click on the
“Start Analysis” on the Kyubit Business Intelligence page. Note, that if you have only one
cube reference, Grid Analysis screen will immediately become visible and ready
for analysis. If there are more cube references, user will be asked for which
OLAP cube to start the analysis. If desired OLAP cube is not on the cube
reference list, select “New Cube Reference” and create new cube reference.
Most common way of OLAP data analysis in
Kyubit Business Intelligence application is “Grid Analysis”. Drag-and-drop measures, hierarchies/levels
from OLAP cube tree structure to grid areas on the right side to analyze OLAP
data. Following areas exist for grid analysis:
o
Cube structure (Tree)
o
Categories area (Axis)
o
Series area (Axis)
o
Measures area
o
Filters area
o
Values area
Every OLAP cube consists of measures,
dimensions and hierarchies. Cube structure is defined in OLAP database and
within Kyubit Business Intelligence is shown using tree view. Analysis is performed by
dragging desired elements (measures, dimension hierarchies) to appropriate
areas on the right.
Measures represent what kind of values we
want to analyze (measure). For example, it could be Sales amount, product
ordered, working hours etc. To start analysis with particular measure, it
should be dragged from the OLAP tree structure on the left to measures area on
the right. Note, if measure is not explicitly selected, analysis will be
performed with default cube measure. It is possible to select (drag) multiple
measures for the same analysis, so the date among the measures could be
compared.
To remove measure from analysis, drag
measure from Measures area to cube tree or select measure (one click) and press
Delete key.
To see some measure over some entity
structure (dimension), drag OLAP dimension hierarchies from the OLAP tree
structure, visualized with the tree view on the left side of the screen, to the
Categories and/or Series area. For example, Countries dimension hierarchy could
be placed on Category axis and Product dimension hierarchy on the Series axis.
Corresponding values from OLAP cube will appear on the values area in the
center of the screen.
To remove dimension hierarchy from
analysis, drag dimension hierarchy from Categories or Series area to cube tree
or select dimension hierarchy (one click) and press Delete key.
If we drag dimension hierarchy that consist
of several levels (For example, Geography [Country - State - City - Customer]
or Calendar [Year - Semester - Quarter - Month - Week - Day]), with each member
on the Categories or Series area will be displayed collapse/expand option,
which would expand values to show values for the next hierarchy level members.
For example, with a few clicks, we would see analysis for all countries, drill
down USA to states, drill down California to cities and finally find values for
San Francisco. At the same analysis, it is possible to drill down Categories
and Series dimension hierarchies.
To expand values and show members of other
dimension hierarchy, drag all dimension hierarchies to the same axis
(Categories or Series) and click/expand desired members. It is possible to drag
multiple dimension hierarchies to the same axis and on that way expand values
from one hierarchy to another. For example, we could first drag Countries
hierarchy and then Product hierarchy. On that way first we would see values for
all countries, and then by expanding USA, we would see Product categories sold
in USA.
Expanding members with single or different
dimension hierarchies could be combined and performed on both axes separately
within same analysis, giving powerful and fast option to analyze data from OLAP
cube.
To expand all sibling members of a
particular hierarchy, right click any of sibling members and select “Expand
all”. On the same way select “Collapse all members” to collapse all sibling
members. Exapand all/Collapse all members is available only on categories axis.
On a category axis it is possible to drill
down certain member if it is a root category level member. Right click member
and select “Drill down Bikes”. “Bikes” member will be automatically added to
filters and level bellow will show members related to selected “Bikes” member.
Every user could define his own preference
while analyzing OLAP data with Kyubit Business Intelligence application. Every time user
starts new analysis, user settings will be applied. Select “User Settings”
available in the top-right corner of the screen.
User can chose which aggregate function to
apply and show while in grid analysis view. To select aggregate function,
right-click grid analysis area and select “Set Aggregates...”
This setting shows or hides rows from OLAP
database that not include any data.
In some situations while in grid analysis,
it will be convenient to customize grid column width for best grid visual
perception. User can select five different column with parameters, from “Small”
to “XXL”. Column width will be saved together with analysis.
Drag-and-drop dimension hierarchy to
filters area and narrow analysis to a particular point of interest. Multiple
filters could be selected to existing analysis. By dragging dimension
hierarchies to Filters area, we prepare grid to accept filter parameters.
Finally, to set filters, browse through dimension hierarchy or search for dimension
hierarchy members to isolate analysis data.
Dimension hierarchies could be dragged to
filters area from the cube tree or, if exists, from the dimension hierarchies
that are already placed to categories or series area.
For example, we could add Calendar
dimension hierarchy to filters area and select CY2003 which would narrow
analysis and show values only for Calendar Year of 2003 for the given dimension
hierarchies on the Categories and Series area.
To remove filter from analysis, drag filter
from Filters area to cube tree or select filter (one click) and press Delete
key.
To find particular member to add to
analysis filters, add dimension hierarchy to filters and select “Search” tab.
Combine hierarchy members browsing and member search to find exact members for
analysis filter. Type in part of the member name, select appropriate selector
(contains, starts with, ends with, etc.) and select of dimension hierarchy
level and narrow scope that will be searched to find members.
When narrowing our analysis to particular
point of interest, we could drag dimension hierarchies to Filters area, but
more practical and faster way to narrow analysis and reduce number of values
shown in analysis is Isolating.
While analyzing data, it is possible to
select certain members on Categories or Series axis (Column or Row). Member and
all of its children will be selected and highlighted in yellow color. In that
moment, it is possible to click on the Isolate option in the analysis toolbar
and instantly selected member and all of its children will be isolated for
further analysis. This means, members not isolated on that axis will be removed
from further analysis. It is possible to select multiple members on Categories
or Series axis (Columns or Rows) at the same time by holding left shift
button.
After isolating, Filters area will be
refreshed with actual filter members as a result of isolating. Isolating is
just another way of setting up analysis filters and can be used together in the
same analysis.
For every added dimension level on grid
analysis, it is possible to define level filtering that would narrow number of
level members to show in grid. Click level arrow and select “Level value
filtering” from level context menu. Level value filters could be applied using
any cube defined measure, using desired query selector (Top, Top Percent,
Bottom, Bottom Percent, Is Higher, Is Between, etc.) and specified filter
value. Levels with defined “Level value filtering” will show different blue
arrow on grid axis.
Grid analysis data could be sorted on axis
level and on particular dimension level added to grid analysis. To sort all
members on an axis, right click on grid analysis and select “Set Row Sorting”
or “Set Column Sorting”. Choose to sort by one of added measures or
alphabetically. If you wish to sort data using measure values, optionally it is
possible to select member on opposite axis upon which values sorting will be
perform. If opposite member is not selected, sorting will be done by measure
total on opposite axis.
(sorting
with selected member on opposite axis)
Grid analysis has options to set sorting
for each added dimension level individually. For example, one level could be
sorted for one measure values, other for different measure values and third
could be sorted alphabetically. Click the arrow beside added dimension level,
level context menu will appear and select “Level sorting”.
On a picture bellow, we see, “Country”
level members are sorted by “Accessories” ascending, but members of
“State-Provice” are sorted by “Bikes” in descending order. It is possible to
create sorting for every added dimension level on both axis.
Anytime while analyzing data in
Kyubit Business Intelligence, user has possibility to switch dimension hierarchies on the
Categories and Series axis. For many reasons, switching axes could be very
practical while analyzing and visualizing data, especially when working with
Chart Analysis and switching back and forth with Grid Analysis.
To select Pivot action, right click on the
empty Grid area. After menu pops up, click on the Pivot action.
While analyzing in Grid view, it is
possible to see values expressed in percentage for each member on the
Categories or Series axis. Percentage could be shown for each row total, sum of
all members on Series axis, or for column total, sum of all members on
Categories axis.
To select percentage view, right click on
the Grid empty area. After menu shows up, click on the “View Rows Percentage”
or “View Columns Percentage” action.
While analyzing data with grid analysis,
any user could use OLAP database defined Named set or create ad-hoc “User Named
Set” that will be stored in Kyubit Business Intelligence application and could be reused in
different analyses and shared with other users using Kyubit Business Intelligence software.
To create ad-hoc “User Named Set” drag dimension hierarchy to any of grid axes,
click dimension hierarchy arrow and from hierarchy context menu select “Create
Named Set”. After saving User Named Set, Cube metadata tree will be refreshed
to include new User Named set.
To create named set with static members of
certain dimension hierarchy, select “Select named set members”, click “Pick
members” and browse through dimension hierarchy or use search members to find members
for named set.
To create dynamic set of named set members,
select “Set named set expression” and enter MDX expression that will be used to
dynamically return members evaluated by expression. Select “Test expression” to
validate MDX expression input. After saving named set, every time named set is
used, expression will be executed in the analysis scope to return members and
use within analysis.
While in grid analysis, authorized users
could edit existing User Named Sets by right mouse click on User Named set in
Cube metadata structure tree or in Main Menu -> Shared Items -> User
Named Sets.
While analyzing data with grid analysis,
any user could use OLAP database defined calculated measures or create ad-hoc
“User Calculated Measures” that will be stored in Kyubit Business Intelligence application
and could be reused in different analyses and shared with other users using
Kyubit Business Intelligence software. To create ad-hoc “User Calculated Measure”,
right-click grid analysis area and select “Create User Calculated Measure“.
Enter MDX expression that will be used for
User Calculated Measure. Select measures and click “Add Measure” to add measure
unique name to expression. After you form complete expression, validate
expression by selecting “Test MDX expression”. Enter appropriate MDX value for
“Format values”, for example “Standard”, “Currency”, “Percent” or any other
valid MDX format values expressions. (Check official MDX documentation for
“Format values” parameter)
After you create User Calculated Measure,
Cube metadata tree will be refreshed to show new User Calculated Measure which
then could be used in analysis.
To edit existing User Calculated Measure,
right-click User Calculate Measure in Cube metadata tree while in grid analysis
and select “Edit User Calculated Measure” or go to Main Menu -> Share Items
-> User Calculated Measures.
In Grid or Chart view, after every analysis
action, it is possible to save current state of analysis and run it again from
the Kyubit Business Intelligence maps. By default, when saved, analysis will be located in
“My analysis” folder, visible only to user who created analysis. Existing
analysis could be saved under different name, using “Save As” option. To run
(analyze) again same analysis, open Folders, then My Analysis map and click on desired
analysis.
To make analysis visible to other people,
open existing analysis and select “Share” option available in the top right
corner. Select one of the available folders and click “Share” button. From now
on current analysis will be located in designated folder and will not be
visible in “My analysis”. Shared folder does not mean that this analysis could
be visible to everyone, but only to users with permission for a particular folder.
Furthermore, users with given permissions on folder will see that analysis with
given name exists, but when they try to execute one, same users additionally
must have permissions in OLAP database itself to successfully run analysis.
Typically, folders would be created to
separate analysis for different business units, sectors or teams, to separate
points of interest and appropriately assign permissions.
Option to share analysis is available in
Grid and Charts view, if analysis is previously saved.
After every analysis action in Grid or
Charts view, analysis state is saved in memory and could be used to “Back” or
“Forward” functionalities, which would reproduce analysis state from memory.
Additionally, user can select “History”
option and see all available analysis states (up to 10), that exists in memory
and could be run again, either by “Back” and “Forward” option or by explicitly
clicking on green arrow beside each analysis state in history panel. History
panel additionally shows basic elements of certain analysis state: measures,
filers, category and series dimension hierarchies and time of execution.
Analysis history is reset with each new
analysis (Clear action).
