How to Add Custom Views to the Comsense Data Warehouse

The Comsense DataWarehouse is refreshed on a schedule, usually over night. The process removes the existing data warehouse database and recreates a new one with fresh data from your production system. As a result, it is important to address any customization in a particular way in order to ensure they are recreated with each refresh of the data warehouse database.

To add customization to the data warehouse take the following steps:

  1. Create the definition for your new view using SQL Server Management Studio or other query generation tool.
  2. We recommend including the USE statement and GRANT statements as seen in the example below to set the target database and set the appropriate security on your new view.

     

    USE [#ReportingDatabaseName#]
    GO

    CREATE VIEW [dw].[MyNewView]
    AS
    SELECT
    [Code],
    [Name]
    FROM
    [dw].[Company]
    GO

    GRANT SELECT ON [dw].[MyNewView] TO [DWRole]
    GO

     

  3. Save your query in a text file with an extension of '.sql'. We recommend to name the file using the name of the view. For the example above we'll name the file MyNewView.sql.
  4. A copy of this .sql file needs to be placed with the DataWarehouse application. Typically this application is installed on the same server as your Microsoft SQL Server. You can determine which server this is by viewing the Help > About window in Comsense Advantage. Your server is the portion of the Datasource before the slash. On the database server, the data warehouse application is typically located on the C: or D: drive in the \ProgramData\Comsense\DataWarehouse\ folder.
  5. In this application folder, you will find a subfolder called Scripts, and inside Scripts you will find Custom. Place your view file in this Custom folder as seen below.



  6. With this file in place we need to tell the refresh utility that we want it to run the file with each refresh. To do this we add a reference to the .sql file in the CustomScriptFiles.sql file.
  7. The CustomScriptFiles.sql file is a text file that contains a list of script files we want the refresh utility to run, with one file name per line excluding the file extension. See the example below.



    Note that the script file you want to run must be located in the same folder as the CustomScriptFiles.sql file, which again is the \DataWarehouse\Scripts\Custom\ folder.
  8. The next time the data warehouse refresh runs, it will run your custom script files. If there are issues with the script you will find the output from the process in the \DataWarehouse\Log folder where there is a new log folder created each time the refresh runs.
  9. If you need to run the scheduled refresh more frequently than the default schedule in order to test your customizations, please submit a support request as such and include the domain and username of the account to grant this permission and we will grant the permission for you. 
Have more questions? Submit a request

Comments