In the last post, I wrote about metadata driven run management. A system that caches the output from views in tables(if defined) and finally creates synonyms to either a view or cached table. I build this for a client that I am working for at the moment.
In the views, there are a lot of dependencies with each other. In the old way, the views where cached by using stored procedures that loops through all of the views. The load order was alphabetically, so when there was a dependency in views, they used numbers in the names to make sure things are cached in the right order. In my system, the views are being cached in parallel and the load order is automatically defined by looking at the dependencies as mentioned in the previous post.
Since the last post, people have been asking how I have done this. So therefore I have written this short blog to explain this to you. It’s really simple to determine what the dependencies are by using dynamic management views. Below is the query that I use to determine the dependencies.
SELECT
v.name AS Entity
,referenced_entity_name AS ReferencedEntity
,referenced_schema_name AS ReferencedSchemaName
,s.name As SchemaName
FROM sys.views v
INNER JOIN sys.schemas s on v.schema_id = s.schema_id
INNER JOIN sys.sql_expression_dependencies d
ON d.referencing_id = v.object_id
WHERE 1 = 1
Which gives me these results(Pokemon example from the last post)
I then use these results to fill the relationship table between the objects in my system in Parent-Child. The ID’s in the example below, are from the base table where I store all of the views with metadata.
When the relations between the objects are stored. I can write queries to use in different procedures and functions to determine the load order. For instance, I began by creating a function which places everything in a hierarchie as shown below.
I used this, to loop through the different levels by updating the sorting/load number in the metadata settings. And there you have it, the load order is defined. For my client we have seen, by doing it this way, that some views are build witch 10 underlying views. Which is a lot of course. So it gives you a good idea off the areas that can be improved.
That is it! If you have any questions/comments/remarks, please let me know by contacting me. Until next time!