Slowly-changing Dimensions
A common example of using an application view is in resolving the effective date of a slowly changing dimension. Consider an order status table that maintains the history of the status for the order as it proceeds through the order processing workflow (e.g. received, processed, shipped, etc.). This would typically be stored in a order status history table containing the order number, status code and the starting and ending date of the status. To determine that status on any given date you need to include a condition in the WHERE clause that resolves the proper status for that point in time by specifying “WHERE date_column BETWEEN status_start_date AND status_end_date”. Rather than creating a filter in MicroStrategy and users having to remember to always include that filter when they use the order status attribute on a report, a view can be created that includes the date qualification. The view definition could be:
Note that the entire calendar table is selected. This is done to avoid having to perform an extra join
between the view and the calendar table since the view can satisfy reports at any level of the date
dimension.
Last Transaction Date Using Views
It is important to make sure that when designing views you consider how MicroStrategy will use the view and review the report SQL to make sure the view is used in the most efficient manner. It is especially important when using views that include a GROUP BY clause to make sure any qualifications are placed directly on the view and not against a table joined to the view if the column also exists in the view. For example, you want to create a report to display the email address and last order date for a list of targeted customers using the following tables:
A common example of using an application view is in resolving the effective date of a slowly changing dimension. Consider an order status table that maintains the history of the status for the order as it proceeds through the order processing workflow (e.g. received, processed, shipped, etc.). This would typically be stored in a order status history table containing the order number, status code and the starting and ending date of the status. To determine that status on any given date you need to include a condition in the WHERE clause that resolves the proper status for that point in time by specifying “WHERE date_column BETWEEN status_start_date AND status_end_date”. Rather than creating a filter in MicroStrategy and users having to remember to always include that filter when they use the order status attribute on a report, a view can be created that includes the date qualification. The view definition could be:
CREATE VIEW ORDER_STATUS_VIEW AS
SELECT C.*, ORDERID, ORDER_STATUS_ID
FROM CALENDAR C
JOIN ORDER_STATUS O ON C.calendar_date between O.status_start_date and o.status_end_date;
JOIN ORDER_STATUS O ON C.calendar_date between O.status_start_date and o.status_end_date;
Last Transaction Date Using Views
It is important to make sure that when designing views you consider how MicroStrategy will use the view and review the report SQL to make sure the view is used in the most efficient manner. It is especially important when using views that include a GROUP BY clause to make sure any qualifications are placed directly on the view and not against a table joined to the view if the column also exists in the view. For example, you want to create a report to display the email address and last order date for a list of targeted customers using the following tables:
CREATE TABLE TRANSACTIONS
(transactionid INTEGER,
custid INTEGER,
orderdate DATE)
primary index (custid);
CREATE TABLE EMAIL_ADDRESS
(custid INTEGER,
email_Address varchar(300))
unique primary index (custid);
A view is created to derive the last order date for each customer as follows:
CREATE VIEW LAST_TRANSACTION_DATE AS
SELECT custid, max(orderdate) lasttransdt
FROM TRANSACTIONS
GROUP BY 1;
You then create a report to display the last order date and email address for a targeted list of customers.
If the report SQL is generated as follows, where the filter condition is placed against the joined table
rather than the view. The entire result set of the view will be materialized in spool before the customer
id filter conditions are applied.
select m.custid, d.lasttransdt, m.email_Address
select m.custid, d.lasttransdt, m.email_Address
from LAST_TRANSACTION_DATE d
join email_Address m on d.custid = m.custid
where m.custid IN (1,2,3,4,5,6,7,8);
join email_Address m on d.custid = m.custid
where m.custid IN (1,2,3,4,5,6,7,8);
If the report SQL is generated with the filter condition applied directly against the view, the customer id
filter conditions will be applied directly to the TRANSACTIONS table early in the plan to limit the result set
of the view to only the customer ids that were requested.
The "WHERE Clause Driving Table" VLDB setting can be used to change where filter conditions are applied to make sure you get the proper SQL in the situation above.
Attribute From Selection Option for Intermediate Pass
Changing the “Attribute Form Selection Option for Intermediate Pass” VLDB setting to “Select ID and other forms if they are on template and already available in existing join tree” can sometimes eliminate some joins and increase performance. This setting is documented in the MicroStrategy System Administration Guide.
The "WHERE Clause Driving Table" VLDB setting can be used to change where filter conditions are applied to make sure you get the proper SQL in the situation above.
Attribute From Selection Option for Intermediate Pass
Changing the “Attribute Form Selection Option for Intermediate Pass” VLDB setting to “Select ID and other forms if they are on template and already available in existing join tree” can sometimes eliminate some joins and increase performance. This setting is documented in the MicroStrategy System Administration Guide.
