The Case for Database Views
A large portion of MicroStrategy implementations point directly to the database tables. I eschew this practice on an absolute basis. Every reporting table should have a corresponding view even if it is a ‘select * from table’. Some DBA’s contend that this adds unnecessary overhead, yet I would contend that the places that a view would differ from the underlying table would contradict the DBA’s other arguments for putting some things in tables.
The first thing that a view does is provide flexibility and makes it easier for reporting especially when it comes to MicroStrategy and reporting in general.
Using views accomplishes the following:
Allows for immediate fixes while ETL and data modeling catch up.
Solves for modeling shortcomings.
Reduces the need for re-work should additional performance tuning be required.
Reduces the need for ‘ApplySimple’ functions making the MicroStrategy application platform independent.
In certain databases, it allows for the use of materialized views.
Reduces the need for database storage for some fields or records.
It is understood that the following scenarios and solutions may not be extremely efficient from a database perspective, they may degrade or eliminate certain indexing strategies, and add overhead for fields that are not of issue. There is a balance between being agile for the reporting users to be able to get data and how fast it needs to be returned. Many of these may be an interim solution until the modeling and ETL catch up, understanding that changes to those may require more extensive testing.
Good database design for consistency and performance would dictate that non-calculated fields should be designated as non-nullable. In the real-world, this is less of the true practice. In dimensional data modeling, if there is a corresponding lookup table or key field in a temporary table with a null value, unless the data is explicit outer joined, then those records will drop. Explicitly setting the outer-join on certain fields can be an expensive proposition. Within a view it is easy to add on a null check (ISNULL, NVL) function and use a default value of say (0,-1) for values, (’-1’,’NA’,’N/A’) for character fields, or (‘1900-01-01’,’9999-12-31’) for date fields. As long as those corresponding values exist in the lookup or join table an inner-join will suffice and may help performance.
Logical Views / Complex Metrics / Attribute ID Logic
Many developers use Logical Views, pass-through functions, or extreme logic in metrics, or logic within an Attribute ID. In most cases, all of this should be done within the view rather than within MicroStrategy. The primary reason for this is that, if there are performance issues and the logic needs to be pre-calculated in the ETL, it is much easier to change it from the view to the ETL, than changing it in MicroStrategy. If it is in MicroStrategy it means adding the new column(s), remapping object(s) associated with it, and having to go through the migration and testing process. As long as the ETL replicates what is in the view logic then the process becomes easier to test. As a developer it may be easier to do everything in MicroStrategy since a view may take longer, but the overall project risk increases greatly should performance issues arise.
Some databases allow for Materialized Views that act as a pseudo-table. Should a view become a performance bottleneck, it may be converted to a Materialized View before it needs to be converted into batch processing.
Certain tricks such as adding a count column with a value of ‘1’ for each row can improve performance by using a sum function rather than a count function. Some DBA’s object to putting a column of ‘1’ into a table due to space constraints, but the performance can come close by implementing the column in a view. Additionally, there may be a table that is generally queried in association with another table, but only contains a subset of records. For example, there may be an ORDER_NOTES table, wherein not every Order record will have a corresponding note, but most likely the Order Note will want to be displayed for all Orders regardless of if there is an Order Note record. A dummy record could be inserted into the ORDER_NOTES table, or the ORDER_NOTES view could be changed to the following:
Select a.ORDER_ID, ISNULL(b.NOTE_ID,-1), b.NOTE_DESC from ORDER a LEFT OUTER JOIN ORDER_NOTE b ON a.ORDER_ID = b.ORDER_ID
There are a variety of other instances where using views comes in very handy, and these illustrate just a few. While they don’t replace solid modeling, they allow for a faster response and provide for reduce risk in a MicroStrategy deployment.
- Ryan Forsythe, Course Creator, MaSTR Architect, Chief Analytics Officer, AllScient