get.myhistory

Stored Procedure Summary

Purpose:

This stored procedure, [get].[myhistory], is designed to retrieve historical views from a data mart for use in PowerBI analysis. It is flexible and allows users to specify various parameters to customise the view that is returned.

Key Features:

Parameters:
- @token (varchar(32)): It is required to determine which view to access.
- @dataMart (varchar(32)): Specifies the data mart to query. Default is ‘project’.
- @viewName (varchar(32)): Name of the view to retrieve. Default is ‘details’.
- @viewSchema (varchar(16), optional): The schema of the view. If not provided, the procedure will attempt to determine it based on the provided table name.
- @whereClause (varchar(4000), optional): SQL WHERE clause for filtering the result set.
- @rowNum (varchar(16), optional): Maximum number of rows to return. Default is ‘30000000’.
- @orderBy (varchar(32), optional): Specifies the ORDER BY clause.
Functionality:
- Token Validation: Checks if the provided token is valid. If not, returns an informative message.
- Dynamic View Creation: Constructs the view name and schema based on parameters.
- SQL Query Generation: Builds a dynamic SQL query based on the provided parameters, including WHERE clauses and ORDER BY conditions.
- Debugging: Optionally prints the constructed SQL query for debugging purposes.
- Error Handling: Includes error handling to capture and report any issues that occur during execution.
Execution:
- The stored procedure constructs and executes a SQL query to return a view of the data based on the provided parameters.
- If the view does not exist, it attempts to use a fallback view or construct a default view.
- Returns results in a table format for further analysis.

Typical Use

Example Usage:

EXEC get.mhistory 
    @token = 'your_token',
    @dataMart = 'employee',
    @viewName = 'headcount',
    @whereClause = 'some_condition',
    @rowNum = '1000',
    @orderBy = 'some_column'

PowerBI:

= Sql.Database(History_Host, History_Endpoint, [Query="EXEC get.myHistory @dataMart = 'employee', @viewName = 'headcount', @token = '"& DataMart_Token &"'"])

Parameters:
History_Host: bmt-dwh-uks-syn-prd-ondemand.sql.azuresynapse.net
History_Endpoint: BMTDWHGLDB
DataMart_Token: <my_token>