When would I use Inline Transformation Services (ITS) on a view?
ITS for views is a feature in Xpert BI where the contents of a view is materialised to a stored table, and Xpert BI maintains lineage:
The ITS will be updated every time the inline view is processed. Include the table in a process group to enable frequent updates.
I can think of three use cases where this is very useful:
- Performance. Transformation with Xpert Bi is based on a "view-on-view" strategy. When complexity and the number of layers increase, query performance can be suffering. Very often, query performance can be improved by materialising some of the views. Optimising large process groups with many objecs may seem as an overwhelmig task, but I suggest you apply a simple strategy: Work your way either downwards from the top (where performance is bad) or upwards from the bottom (where performance is presumably good) until you find the view(s) that take longer to run. Then, try and error by inlining one or more of the underlying views, so the query would select from an indexed table rather than a view.
- Access control. The stack databases ODS and ETL often have a mix of unrestricted and restricted data, and should generally not be accessible by end users. A common practice is to materialise data into dedicated databases/datamarts, and then give end users read access to that database only. Create a view in the datamart, inline it using Xpert BI ITS, and your ODS restricted data remains secure while your users can read the data they are allowed.
- Dimension table when source is lacking a dedicated dimension table. This is a special use case only relevant for fact-dimension modelling: Consider e.g. a bank account table with a column 'AccountType' with a few distinct values such as Savings Account, Loan Account, etc. But there is no AcccountType table in ODS. In this case, you could create an AccountType dimension:
CREATE VIEW BI_ETL.dbo.AccountType_Dim AS
GROUP BY AccountType
Then, materialise this as an ITS table in Xpert BI with a surrogate key:
Does anyone have other uses of ITS for views?
Please sign in to leave a comment.