April 23, 2015 at 7:08 am
I am currently working on Credit risk data warehouse project. The current data architecture is based on DataVault. Users are able to view consolidated data per reporting period via a web portal that runs queries directly from the vault. There are no datamarts or olap cubes which could be used for reporting purposes at this stage.
The current architect initially implemented a business view layer that consists of sq views based on the vault tables which are used to present data to the users. However the performance of getting the data out was not good enough for reporting requirements. It was also noted that a lot of the views are pulling pretty much the same data from the underlying vault tables. Consequently it was decided to create an indexed view which joins data from all the commonly used tables. The user views can then utilize this view in their definitions.
This has improved the performance greatly but has led to deadlock issues whilst some of the underlying tables are being updated separate by datavault loading processes. I have been able to resolve some of the deadlock issues by making adjustments to the control flows of the some of the SSIS packages. However this is not a feasible option as this would require refactoring of a lot of packages. Given the project t timeframe this will not be accepted by the higher management.
I just wanted to know if anyone had any ideas on an alternative to the indexed view or any similar experiences with DataVault DataWarehouses?
May 1, 2015 at 7:30 am
In my opinion, you have already mentioned the solution...a data mart. What you're doing right now with the views (or indexed views), is taking a shortcut to reporting. It won't scale and will result in more deadlock situations as more views are created.
There is no replacement for a dimension model when it comes to reporting. The amount of time spent on creating the views and resolving issues around those, could be better spent creating a data mart and ETL process to keep that up to date.
May 1, 2015 at 3:46 pm
I would agree with Martin, a data mart that pulls data from your vault for reporting is a natural evolution when direct reads from the vault model are no longer performing well enough.
In Data Vault oriented projects I've participated in this was a planned step with the vault serving as a warehouse from which the mart could be extracted for OLAP processing.
May 4, 2015 at 12:36 am
I have brought up the idea of a datamart on several occasions but there seems to be a reluctance particularly from the chief architect to deviate from the DataVault model. We have a lot of PIT tables which are are supposed to be used for performance and thus for reporting purposes. However we are not meeting the necessary performance requirements.
There is still a lot of functionality to add to the data warehouse so I am hoping that some different architectural decisions can be made. One thing that is certain to everybody is that the Indexed view is clearly a temporary thing.
Thanks for the information. Will let you know how things go.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply