get.myview

by

Stored Procedure Summary

Purpose:

The get.myview stored procedure is designed to generate and return a dynamic view of data based on user-defined parameters. It supports complex queries for PowerBI analysis by allowing users to specify various options such as data mart, view name, filtering conditions, and more.

Key Features:

  1. Parameters:
    • @token (varchar(32)): Authentication token for access control.
    • @dataMart (varchar(32)): Specifies the data mart to use (optional).
    • @viewName (varchar(32)): Defines the view to be generated (default is ‘details’).
    • @version (varchar(16)): Version of the view to use (optional).
    • @debug (INT): Debug flag to print SQL statements (default is 1).
    • @viewSchema (VARCHAR(16)): Schema for the view (optional).
    • @whereClause (varchar(4000)): WHERE clause for filtering data (optional).
    • @rowNum (varchar(16)): Number of rows to return (default is ‘30000000’).
    • @orderBy (varchar(32)): ORDER BY clause for sorting results (optional).
  2. 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. Handles both base tables and modular data blocks (Databricks) for efficient view generation.
    • Schema and Version Retrieval: Determines the schema and version of the view if not provided. Defaults to a common schema or the latest version.
    • 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.
  3. 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.

Example Usage:

EXEC get.myview 
    @token = 'your_token',
    @dataMart = 'project',
    @viewName = 'details',
    @version = '202409',
    @whereClause = 'some_condition',
    @rowNum = '1000',
    @orderBy = 'some_column'

PowerBI

= Sql.Database(DataMart_Host, DataMart_Endpoint, [Query="EXEC get.myView @dataMart = 'project', @viewName = 'details', @token = '"& DataMart_Token &"', @version = 'beta'"])

Parameters:
DataMart_Host: bmt-dwh-uks-srv-prd.database.windows.net
DataMart_Endpoint: bmt_dwh_db_gl
DataMart_Token: <my_token>

Leave a Comment