SQL Schema & Stored Procedure
Documentation
Contents
Tables.......................................................................................................1
Stored Procedures....................................................................................3
Admin API Stored Procedures.................................................................11
Tables
1. BusinessUnitMapping Table
Description:
Master table holding metadata for each business unit, mapping
it to its corresponding export history table and blob storage
settings.
Columns:
BusinessUnitId: Unique ID (Primary Key)
BusinessUnitName: Name of the business unit
BusinessUnitDescription: Description/details
CreatedDate: Record creation timestamp (defaults to
GETDATE())
ModifiedDate: Timestamp of last update
IsActive: Boolean flag for active status (defaults to 1)
CreatedBy: Creator's identifier
ModifiedBy: Last modifier's identifier
ExportHistoryTableName: Name of the export history table
(e.g., BU001_ExportHistory)
BlobName: Name of the default blob file
BlobContainerName: Azure Blob container name
SQL Definition:
CREATE TABLE BusinessUnitMapping (
BusinessUnitId NVARCHAR(100) PRIMARY KEY,
BusinessUnitName NVARCHAR(255),
BusinessUnitDescription NVARCHAR(MAX),
CreatedDate DATETIME DEFAULT GETDATE(),
ModifiedDate DATETIME,
IsActive BIT DEFAULT 1,
CreatedBy NVARCHAR(255),
ModifiedBy NVARCHAR(255),
ExportHistoryTableName NVARCHAR(255),
BlobName NVARCHAR(255),
BlobContainerName NVARCHAR(255)
);
2. [BU001_ExportHistory] Table
Description:
Holds export execution history for Business Unit BU001. Each
business unit can have its own export history table.
Columns:
ExportId: Unique ID for the export job (Primary Key, auto-
generated)
ExportType: Type of export (e.g., Visual, Table, Report)
Status: Status of the export (e.g., Started, Completed,
Failed)
FileName: Exported file's name
ExportStartTime: Start time of the export (defaults to
GETDATE())
ExportEndTime: End time of the export
ErrorMessage: Error message if the export failed
UserId: Email of the user who initiated the export
FileSize: Size of the exported file (in bytes)
BlobUrl: URL to the exported blob file (with SAS token if
needed)
VisualId: ID of the visual exported
ReportId: ID of the report exported
Filters: Serialized filter information (e.g., JSON)
DAXQuery: The DAX query used for export
SQL Definition:
CREATE TABLE [dbo].[BU001_ExportHistory] (
ExportId UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
ExportType NVARCHAR(50),
Status NVARCHAR(50),
FileName NVARCHAR(255),
ExportStartTime DATETIME DEFAULT GETDATE(),
ExportEndTime DATETIME NULL,
ErrorMessage NVARCHAR(MAX) NULL,
UserId NVARCHAR(255),
FileSize INT NULL,
BlobUrl NVARCHAR(2000),
VisualId NVARCHAR(255),
ReportId NVARCHAR(255),
Filters NVARCHAR(MAX),
DAXQuery NVARCHAR(MAX) NULL
);
Stored Procedures
1. GetExportStatusById
Purpose:
Returns the status and file name of a specific export job using
dynamic SQL based on the business unit.
Parameters:
@ExportId: GUID of the export job
@BusinessUnitId: ID of the business unit
SQL Definition:
CREATE PROCEDURE GetExportStatusById
@ExportId UNIQUEIDENTIFIER,
@BusinessUnitId NVARCHAR(100)
AS
BEGIN
DECLARE @ExportHistoryTableName NVARCHAR(256);
DECLARE @SQL NVARCHAR(MAX);
SELECT @ExportHistoryTableName =
ExportHistoryTableName
FROM BusinessUnitMapping
WHERE BusinessUnitId = @BusinessUnitId;
IF @ExportHistoryTableName IS NULL
BEGIN
RAISERROR('Invalid BusinessId or no export table found.',
16, 1);
RETURN;
END
SET @SQL = 'SELECT Status, FileName FROM ' +
QUOTENAME(@ExportHistoryTableName) + ' WHERE ExportId =
@ExportIdParam';
EXEC sp_executesql @SQL,
N'@ExportIdParam UNIQUEIDENTIFIER',
@ExportIdParam = @ExportId;
END;
2. GetBusinessUnitContext
Purpose:
Fetches full metadata for a given business unit.
Parameter:
@BusinessUnitId: ID of the business unit
SQL Definition:
CREATE PROCEDURE GetBusinessUnitContext
@BusinessUnitId NVARCHAR(100)
AS
BEGIN
SELECT * FROM BusinessUnitMapping WHERE BusinessUnitId
= @BusinessUnitId;
END;
3. GetBusinessUnitExportHistory
Purpose:
Fetches all export history entries for a given business unit,
ordered by export start time (descending).
Parameter:
@BusinessUnitId: ID of the business unit
SQL Definition:
CREATE PROCEDURE GetBusinessUnitExportHistory
@BusinessUnitId NVARCHAR(100)
AS
BEGIN
DECLARE @ExportHistoryTableName NVARCHAR(255);
DECLARE @sql NVARCHAR(MAX);
SELECT @ExportHistoryTableName =
ExportHistoryTableName
FROM BusinessUnitMapping
WHERE BusinessUnitId = @BusinessUnitId;
IF @ExportHistoryTableName IS NULL
BEGIN
RAISERROR('ExportHistoryTableName not found for the
given BusinessUnitId.', 16, 1);
RETURN;
END
SET @sql = N'SELECT * FROM ' +
QUOTENAME(@ExportHistoryTableName) + ' ORDER BY
ExportStartTime DESC';
EXEC sp_executesql @sql;
END;
4. InsertExportHistory
Purpose:
Inserts a new export record into the export history table for the
specified business unit.
Input Parameters:
@BusinessUnitId, @ExportType, @Status, @FileName,
@UserId, @BlobUrl, @FileSize, @DAXQuery, @VisualId,
@ReportId, @Filters
Output Parameter:
@ExportId: Generated GUID for the inserted record
SQL Definition:
CREATE PROCEDURE InsertExportHistory
@BusinessUnitId NVARCHAR(100),
@ExportType NVARCHAR(50),
@Status NVARCHAR(50),
@FileName NVARCHAR(255),
@UserId NVARCHAR(255),
@BlobUrl NVARCHAR(2000),
@FileSize INT = NULL,
@DAXQuery NVARCHAR(MAX) = NULL,
@VisualId NVARCHAR(255) = NULL,
@ReportId NVARCHAR(255) = NULL,
@Filters NVARCHAR(MAX) = NULL,
@ExportId UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @tableName NVARCHAR(255);
SELECT @tableName = ExportHistoryTableName
FROM BusinessUnitMapping
WHERE BusinessUnitId = @BusinessUnitId;
IF @tableName IS NULL
BEGIN
RAISERROR('Invalid BusinessUnitId or no export history
table configured.', 16, 1);
RETURN 1;
END
SET @ExportId = NEWID();
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'INSERT INTO ' + QUOTENAME(@tableName) + '
(
ExportId, ExportType, Status, FileName,
ExportStartTime, UserId, FileSize,
BlobUrl, VisualId, ReportId, Filters, DAXQuery
)
VALUES (
@ExportId, @ExportType, @Status, @FileName,
GETDATE(), @UserId, @FileSize,
@BlobUrl, @VisualId, @ReportId, @Filters, @DAXQuery
)';
EXEC sp_executesql @sql,
N'@ExportId UNIQUEIDENTIFIER, @ExportType
NVARCHAR(50), @Status NVARCHAR(50),
@FileName NVARCHAR(255), @UserId NVARCHAR(255),
@FileSize INT,
@BlobUrl NVARCHAR(2000), @VisualId NVARCHAR(255),
@ReportId NVARCHAR(255),
@Filters NVARCHAR(MAX), @DAXQuery
NVARCHAR(MAX)',
@ExportId=@ExportId, @ExportType=@ExportType,
@Status=@Status, @FileName=@FileName,
@UserId=@UserId, @FileSize=@FileSize,
@BlobUrl=@BlobUrl, @VisualId=@VisualId,
@ReportId=@ReportId, @Filters=@Filters,
@DAXQuery=@DAXQuery;
SELECT @ExportId AS ExportId;
RETURN 0;
END;
5. UpdateExportHistory
Purpose:
Updates an existing export record (e.g., to mark it as complete
or failed).
Parameters:
@BusinessUnitId: ID of the business unit
@ExportId: ID of the export to update
@Status: New status value
@ErrorMessage: Optional error message
@ExportEndTime: Optional override for end time (defaults
to current time)
SQL Definition:
CREATE PROCEDURE UpdateExportHistory
@BusinessUnitId NVARCHAR(100),
@ExportId UNIQUEIDENTIFIER,
@Status NVARCHAR(50),
@ErrorMessage NVARCHAR(MAX) = NULL,
@ExportEndTime DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ExportHistoryTableName NVARCHAR(255);
DECLARE @sql NVARCHAR(MAX);
SELECT @ExportHistoryTableName =
ExportHistoryTableName
FROM BusinessUnitMapping
WHERE BusinessUnitId = @BusinessUnitId;
IF @ExportHistoryTableName IS NULL
BEGIN
RAISERROR('ExportHistoryTableName not found for the
given BusinessUnitId.', 16, 1);
RETURN;
END
IF @ExportEndTime IS NULL
SET @ExportEndTime = GETDATE();
SET @sql = N'UPDATE ' +
QUOTENAME(@ExportHistoryTableName) + '
SET Status = @Status,
ErrorMessage = @ErrorMessage,
ExportEndTime = @ExportEndTime
WHERE ExportId = @ExportId';
EXEC sp_executesql
@sql,
N'@ExportId UNIQUEIDENTIFIER, @Status NVARCHAR(50),
@ErrorMessage NVARCHAR(MAX), @ExportEndTime DATETIME',
@ExportId=@ExportId,
@Status=@Status,
@ErrorMessage=@ErrorMessage,
@ExportEndTime=@ExportEndTime;
END;
Admin API Stored Procedures
1. GetBusinessUnitList
Purpose:
Fetches all active business units (IsActive = 1).
SQL Definition:
CREATE PROCEDURE GetBusinessUnitList
AS
BEGIN
SELECT *
FROM BusinessUnitMapping
WHERE IsActive = 1;
END;
2. InsertBusinessUnit
Purpose:
Inserts a new business unit and links it to an export history
table and blob configuration.
Parameters:
@BusinessUnitId, @BusinessUnitName,
@BusinessUnitDescription, @BlobName,
@BlobContainerName, @ExportHistoryTableName,
@CreatedBy
SQL Definition:
CREATE PROCEDURE InsertBusinessUnit
@BusinessUnitId NVARCHAR(100),
@BusinessUnitName NVARCHAR(100),
@BusinessUnitDescription NVARCHAR(255),
@BlobName NVARCHAR(255),
@BlobContainerName NVARCHAR(255),
@ExportHistoryTableName NVARCHAR(100),
@CreatedBy NVARCHAR(255)
AS
BEGIN
INSERT INTO BusinessUnitMapping (
BusinessUnitId, BusinessUnitName,
BusinessUnitDescription,
BlobName, BlobContainerName, ExportHistoryTableName,
CreatedDate, CreatedBy
)
VALUES (
@BusinessUnitId, @BusinessUnitName,
@BusinessUnitDescription,
@BlobName, @BlobContainerName,
@ExportHistoryTableName, GETDATE(), @CreatedBy
);
END;
3. UpdateBusinessUnit
Purpose:
Updates configuration for an existing business unit.
Parameters:
@BusinessUnitId, @BusinessUnitName,
@BusinessUnitDescription, @ModifiedBy,
@ExportHistoryTableName, @BlobName,
@BlobContainerName, @IsActive
SQL Definition:
CREATE PROCEDURE UpdateBusinessUnit
@BusinessUnitId NVARCHAR(100),
@BusinessUnitName NVARCHAR(100),
@BusinessUnitDescription NVARCHAR(255),
@ModifiedBy NVARCHAR(100),
@ExportHistoryTableName NVARCHAR(100),
@BlobName NVARCHAR(255),
@BlobContainerName NVARCHAR(255),
@IsActive BIT
AS
BEGIN
SET NOCOUNT ON;
UPDATE BusinessUnitMapping
SET
BusinessUnitName = @BusinessUnitName,
BusinessUnitDescription = @BusinessUnitDescription,
ModifiedBy = @ModifiedBy,
ModifiedDate = GETDATE(),
ExportHistoryTableName = @ExportHistoryTableName,
BlobName = @BlobName,
BlobContainerName = @BlobContainerName,
IsActive = @IsActive
WHERE BusinessUnitId = @BusinessUnitId;
END;
4. DeleteBusinessUnit
Purpose:
Deletes a business unit entry from metadata. This does not
delete the underlying export history table.
Parameter:
@BusinessUnitId
SQL Definition:
CREATE PROCEDURE DeleteBusinessUnit
@BusinessUnitId NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM BusinessUnitMapping
WHERE BusinessUnitId = @BusinessUnitId;
END;
