Dataviews for Analytics
Dataviews can act as a data source to Analytical Reports (Templates). Analytical reports can be based on a Microsoft Analysis Services (SSAS) multi-dimensional database, or a flat data source SQL statement.
This topic contains the following sections:
Flat-Data SQL Select Statements
If an analytical report should be based on a regular SQL statement, create a "Select statement" dataview of type. The statement is normally a regular "select" statement returning a data set back to the Bizview application server. But the SQL-statement can also be the execution of a stored procedure in SQL Server that returns a data set.
A few examples of using stored procedures to load data to a dataview, and then later use this data in an analytical report are the procedures:
fact.p_fin_findata_extract – To get both actuals, planning, and group consolidation transactions from the datastore.
dat.p_extract_plandata – To get "live" registered input data from the dat.data_cells table inside the Bizview application database.
These two procedures, in combination with selection panes sending the valid parameters down to the database (#pane), make it possible to easily build analytical reports based on complex Bizview business logic, such as group consolidation and currency conversion.
Microsoft Analysis Services Databases (SSAS) Cubes
If you want to build analytical reports based on SSAS Cubes, you should first create a XMLA data source under the Data Sources system admin menu.
Then, create a new "Cube" type dataview with the XMLA data source you just created as the data source. You must also select which SSAS database and cube associated with the data source you want to use as the base for the dataview.
When you have a valid connection to the cube, the dimensions in the cube display:
Dataview Fields
A data view returns data, normally represented as a data set with a set of fields. To provide designers and end-users with a common set of definitions of the data returned, it’s possible (but not mandatory) to add more meta-data regarding the data in the data view. This meta-data is configured under ‘Data View Fields’.
Meta-data and Dimensions
At the top right of the screen, select what data view you want to configure. The data view must be published to a client to be visible in the drop-down. Bizview will then execute the data view and retrieve which fields the data view will return. These fields are listed in the section to the far left of the screen.
You can also select which fields from the data view should be returned as fields from the data view; the underlying query can contain more fields then the data view will return. Unselect the fields you do not want the data view to return.
In general, a field can be one of two things: a measure (number), or an attribute/dimension. Attributes can be grouped into what’s called dimensions.
Select a field and configure meta-data regarding the field. In the Property area in the middle of the screen, you can:
Define if the field is a dimension/attribute or a measure.
Give the field a Display text (what the end-user will see when viewing data from the data view).
If the field is an attribute, you can also group this attribute into a dimension (a natural collection of attributes that are related). For example, product, product group, product size, and product color might all be grouped into a ‘Product’ dimension.
If the field is a measure, you can define the display format. For example, {0:0,0}, and the aggregate function (e.g. sum, avg, count, etc).
Click Save to save the settings.
If you change the SQL-statement returning the data to the data view, and there are fields defined in the ‘Data View Fields’ editor that is no longer returned by the SQL-statement, you will see a small indicator to the left of the name of the field. If you want to remove these fields from the meta-data definition, select the Clean up button at the top-left of the screen.
Hierarchies
A hierarchy is a representation of the fields that make it easy and intuitive for the end-user to navigate down through the hierarchy structure. An example can be a structure of ‘Product Groups -> Products’, or ‘Year -> Quarter ->Month’.
The data view only returns flat data, but an analytical report can internally restructure the flat data into hierarchies. In order to determine how the different fields are related to each other, the analytical report must get meta-data about these relations. One, or many, hierarchies can be defined in the section to the far-right of the screen.
The following example is based on a scenario with a data view returning three separate fields: Year, Quarter, and month (and probably many more dimensions and measures).
Click the Add Hierarchy button at the top of the Hierarchies section.
First, give the hierarchy a name by renaming ‘New Hierarchy’ to something else. This text will be visible to the end-user. A good example is to describe the hierarchy levels in the name, such as ‘Year -> Quarter -> Month’.
Then, drag-and-drop the fields you have in the field list at the far left into the hierarchy (white box). Start with the top-level field (Year), then the next level (Quarter), etc.
You should also assign the hierarchy to a dimension. In the hierarchy definition, you can select a dimension to assign the hierarchy to.
You can preview the data view structure as the end-user will see the structure in an analytical report by clicking the Preview tab at the far-right of the screen.
Template Design->Analytical Reports
It's easy for a designer to create an analytical report if a data view exists and, if needed, the structure of the data is described using Data View Fields. There is actually no design required; just some configuration and assigning the report to the appropriate clients and users.
Complete the following:
Click Create new template.
In the small menu that appears, select ANALYTIC.
Give the analytical report an ID and a description.
In the Dataview drop-down, select which data view you want as the base for the analytical report.
If the Analytical report is based on a flat-data SQL-statement, you can also assign a selection pane to the analytical report. Parameters defined in the selection pane are sent to the data view to filter data.
