SDU Tools: Script Analytics View

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. One tool that was added a while back is ScriptAnalyticsView.

When we build BI systems, we create a data warehouse with tables that are structured as cleansed versions of the incoming data, and with strong referential integritty and consistency.

When we expose those tables to external BI tools like Power BI, Analysis Services, etc. we limit those tools to a series of views that we create. Generally we put them in a schema called Analytics, but it could have any name.

To avoid the laborious work of creating the views, we have a stored procedure that reads the source structure, and scripts out the view for us. You’ll notice that it does things like separate out words to have spaces between them, deal with singular vs plural, etc.

This procedure is particularly useful if you are following the guidance that I had for my book on Implementing Power BI in the Enterprise .

Find out more

You can see the procedure in action in the main image above, and in the video below. Note: there is also a single database version of this procedure for Azure SQL Database.

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

2025-04-20