get.myArchive

by

Purpose

The get.myArchive stored procedure is designed to retrieve archived data from a specified DataMart view, allowing for historical analysis and structured retrieval. The procedure integrates data retrieval logic, version control, and debugging capabilities to ensure flexible and efficient access to archived data.

Key Features

  • Parameterised Input: Supports multiple parameters for flexible data extraction, including:
    • @dataMart and @viewName to define the target dataset.
    • @myDate to specify the date for archival retrieval.
    • @version for accessing different stored versions of the data.
    • @whereClause to filter results dynamically.
    • @debug mode for enhanced logging and issue tracking.
    • @tableSchema and @viewSchema for specifying schema constraints.
  • Dynamic View Construction:
    • Constructs the appropriate view name based on the provided parameters.
    • Uses a hashed string (MD5) for unique identifiers where required.
  • Archive Management:
    • Identifies and retrieves archived views dynamically.
    • Calls put.myarchive to manage data persistence.
    • Verifies view existence before retrieval.
  • Error Handling & Debugging:
    • Implements structured error handling with SQL Server’s TRY...CATCH mechanism.
    • Debug mode enables SQL statements and process flow visibility.
  • Secure Execution:
    • Uses SUSER_SNAME() to track execution user.
    • Enforces NOCOUNT ON to reduce interference with SELECT statements.

Workflow

  1. Parameter Validation & Default Assignments:
    • Ensures parameters have appropriate values or assigns defaults.
    • Constructs @myDataView based on the provided @dataMart and @viewName.
  2. Data Archival Handling:
    • Calls put.myarchive to ensure the requested archive exists.
    • Constructs the target archive view name using the date (@archiveDate).
  3. View Existence Check:
    • Uses OBJECT_ID to validate the existence of the requested archive.
  4. Data Retrieval:
    • If the view exists, executes get.myview to fetch the archived dataset.
  5. Error Handling:
    • Captures and raises errors with relevant details, including stored procedure name and table reference.

Use Case

This procedure is ideal for historical data retrieval where users need to extract archived snapshots of DataMart views dynamically. It provides a structured approach to accessing versioned data and ensures robust error handling in case of missing or malformed requests.

Sample Execution of get.myarchive Stored Procedure

Before executing the stored procedure, ensure that your connection is established to the OnDemand data source:
🔹 Server: bmt-dwh-uks-syn-prd-ondemand.sql.azuresynapse.net
🔹 Database: dwh_silver

Example Execution: Retrieving an Archived Project Details View

EXEC [get].[myarchive] 
    @token = 'abc123',
    @dataMart = 'project',
    @viewName = 'details',
    @myDate = '2025-01-13',

Explanation of Parameters

ParameterValueDescription
@token'abc123'Report_ID authentication token (example value).
@dataMart'project'Specifies the DataMart to query.
@viewName'details'Specifies the view within the DataMart.
@myDate'2025-01-13'Retrieves data archived on this date.

Expected Outcome

  1. The procedure verifies the existence of an archived view:
    • svw.project_details_20250113
  2. If found, it calls get.myview to retrieve the data.
  3. Debug mode prints useful messages to track execution flow.
  4. If an error occurs (e.g., view not found), it raises an error with detailed information.

This execution retrieves project details as of 13th January 2025.

Leave a Comment