DWH-storage space for automated monitoring of BI systems

A complete BI system has a variety of log and monitoring mechanisms, one of which is monitoring the growth trend of BI system storage, the so-called storage in BI can be divided into Data Warehouse storage and OLAP database storage .

Data Warehouse

At least until now, all data warehouses have been built on a relational database, except that the database is designed to conform to the dimensional data model. So we can use a series of features provided by SQL Server to collect the storage size information of the table granularity in the Data Warehouse, to do this I believe there are many ways, one of which is to use a system view:

• Sys.tables

Returns all tables in the current database

• Sys.partitions

Returns all the partitions in the current database that have the Rows property indicating how many rows of data are in the partition. We know that a table has at least one partition, so we can find the table and its partitions through the top two views, so we know how many rows a table has (and of course it doesn't have to be so troublesome to just find the number of rows in the table).

• Sys.allocation_units

Returning all allocation units, the allocation unit has a lot of information about the data page, one of which is total_pages, which indicates how many data pages there are in total for the allocation unit. A partition has one or more allocation units, so we can find out how many data pages are in total for each table in the previous three views, and each data page is 8KB, so we find out how much space each table occupies.

The following is a statement and the return result shows the effect (because each table has only one partition, so there is no group), we can use this statement in the ETL as long as it is encapsulated in the stored procedure to obtain the DWH storage information automatically:

OLAP database

We know that SSAS has provided some system functions since 2008 to see how many OLAP databases are similar to one server, how many cubes a single OLAP database has, and what measure groups, measures, dimensions, and so on, a cube has. Unfortunately, however, there is no corresponding storage information, so it is best to use the AMO programming interface to obtain storage information for the OLAP database.

Add the script Component to the Data Flow task in the SSIS package, and then use the following code:

The last information obtained is as follows:

DWH-storage space for automated monitoring of BI systems