Understanding the Record Grain of Business Views
When creating a Business View in Incorta, it is first important to think through the grain of the data you want your view to offer. Should the records be offered to the analyzers at the transaction-level or the product-level? The metrics that these views offer may only make sense at certain record grains too. Incorta allows any field from the physical table level to be brought into a business view. The resulting grain of the business view records is dictated by the child-most field contained in the view. What's more, if your analysts don't use every field offered by the view and a "base table" wasn't defined, the grain of the view can be dynamic as Incorta will set the grain based on the child-most field in use. This article will explore these concepts more to ensure they are well understood before you begin designing your business views.
We recommend that you be familiar with your data sources and these Incorta concepts before exploring this topic further:
These concepts applies to all 4.x releases and greater.
The best way to understand the manner in which Incorta sets the grain of business views is to first explore your data using a simple Listing Table insight. Using a Listing Table, these concepts can be conveyed by example.
Consider the listing table below with three fields from the "salesorderheader" table. Note that this is the only table in play at the moment and, as a result, all 2,381,622 records are visibile.
However, as soon as a field from a child table is introduced to this Listing Table, the record grain changes. Note the two new fields, "Salesorderdetailid" and "Orderqty", now added from the "salesorderdetail" table below. The grain of the data returned is now at the child-most detail level resulting in 9,067,608 "salesorderdetail" records.
Business Views behave the same way. The grain of the view is dictated by the child-most table involved. To illustrate this further, let's create a business view using the same five fields seen above. To accomplish this, simply click the down arrow next to the [Save] button and save this listing table as a business view.
When using this business view to build out insights on a dashboard, it is worth noting that the grain of view still depends on the fields employed in the insight. The business view will return records at the grain of the child-most fields involved. Thus, not until a field from the "salesorderdetail" table is used will the view return that grain. As illustrated below, even when using this new "SalesDetail_BV" view, the grain remains at the "salesorderheader" level until a field is added in the business view from the "salesorderdetail" table.
As a best practice, the business view should be defined with a base table to ensure that the grain can never be dynamic, as illustrated above. Let's go back to the business view and set the base table to be at the "salesorderdetail" level.
Now that the base table for the view has been explicitly defined, the view will always return records at the "salesorderdetail" grain regardless of the fields chosen. Note the 9,067,608 records returned below even without fields from the "salesorderdetail" selected.
Rather than confusing your analysts and consumers of the business view, as illustrated above, it is always best to create your views from a Listing Table first and then to explicitly define a Base Table in order to completely demystify the potential for your grain to change depending on the fields used.
Also, avoid building insights on dashboards using two different business views with different base table definitions. This can lead to unanticipated query plans that can lead to unexpected queries.