Month: August 2024

Bulk Credentials

by

use masterCREATE LOGIN <user> use DatabaseCREATE USER <user> The EXECUTE permission was denied on the object ‘myhistory’, database ‘BMTDWHGLDB’, schema ‘get’. ALTER ROLE [exec_get] ADD MEMBER <user>;ALTER ROLE [read_hvw] ADD MEMBER <user>; DataSource.Error: Microsoft SQL: Error!! Error Number: 15151, Stored Procedure get_myHistory, Table Name: [hvw].[employee_details], Returned the following error : Cannot find the CREDENTIAL ‘https://bmtdwhuksstrprd.dfs.core.windows.net/gold/snapshot/employee/details/*.parquet’, … Read more

get.myhistory

by

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: Typical Use Example Usage: PowerBI: Parameters:History_Host: bmt-dwh-uks-syn-prd-ondemand.sql.azuresynapse.netHistory_Endpoint: BMTDWHGLDBDataMart_Token: <my_token>

Supplier.PurchaseOrderline

by

Purpose The Supplier Purchase Order Line Fact TableDesigned to display detailed quantitative data related to purchase orders from suppliers. This table integrates with various dimension tables to provide comprehensive insights into purchase order lines, including project associations, supplier details, and order statuses. The primary purpose is to support procurement tracking, cost analysis, and supplier management … Read more

Release 31

by

Alterations Project.PurchaseOrderLine (&202407)ADD DEMAND CODE Project.SupplierInvoice (&202408)REMOVE WHERE filter src.DIM_COMPANY_ID not in (‘1062’) Additional Supplier.PurchaseOrderline Notes [‘Files 20240819/svw_dev_flat\\project_purchaseorderline.sql and 20240819/svw_prd_flat\\project_purchaseorderline.sql are different.’,‘Files 20240819/svw_dev_flat\\project_purchaseorderline_202407.sql and 20240819/svw_prd_flat\\project_purchaseorderline_202407.sql are different.’,‘Files 20240819/svw_dev_flat\\project_supplierinvoice.sql and 20240819/svw_prd_flat\\project_supplierinvoice.sql are different.’,‘File project_supplierinvoice_202408.sql found in 20240819/svw_dev_flat but not in 20240819/svw_prd_flat.’,‘File supplier_purchaseorderline.sql found in 20240819/svw_dev_flat but not in 20240819/svw_prd_flat.’]

Release 30

by

NB Release 29 was Abandoned Additions Infrastructure.ChangeRequest for CAB report (description to follow) Project.PurchaseOrderline Changes Project.Expenses, changes from REPORT_CODE_GROUP NOT IN(‘Time’) >> REPORT_CODE_GROUP IN(‘Exp’), Validation: there is a risk there are other expense codes not included Project.SupplierInvoice, REMOVED HASH(Item_ID) Notes [‘File infrastructure_changerequest.sql found in 20240813/svw_dev_flat but not in 20240813/svw_prd_flat.’,‘Files 20240813/svw_dev_flat\\project_expenses.sql and 20240813/svw_prd_flat\\project_expenses.sql are different.’,‘File project_purchaseorderline.sql … Read more

Project.PurchaseOrderline

by

Purpose The Supplier Purchase Order Line Fact TableDesigned to display detailed quantitative data related to purchase orders from suppliers. This table integrates with various dimension tables to provide comprehensive insights into purchase order lines, including project associations, supplier details, and order statuses. The primary purpose is to support procurement tracking, cost analysis, and supplier management … Read more

Medallion.Platinum

by

Overview: The Platinum Layer is the most advanced tier in the data warehouse, dedicated to performing complex business transformations and machine learning (ML) exercises. This layer leverages advanced analytics to generate insights that support budget, forecasting and predictive analytics. The resulting transformed data and predictive models are then returned to the Gold Layer for delivery. … Read more

Medallion.Gold

by

Overview: The Gold Layer serves as the delivery centre of the data warehouse, providing secure and efficient access to the finalised data. This layer is responsible for delivering data to end-users and applications, ensuring that it is ready for analysis, reporting, and other business intelligence activities. Key Characteristics: Workflow Summary: Importance: RAID Risks: Issues: Dependencies: … Read more

Medallion.Silver

by

Overview: The Silver Layer, also referred to as the ‘Staging’ SQL database, is where data undergoes further refinement and transformation. This layer focuses on structuring the data into well-defined snapshots, adhering to a common data model. The goal is to create standardized and consistent data sets that are ready for analytical purposes and easy integration … Read more

Medallion.Bronze

by

Overview: The Bronze Layer is the initial raw data storage tier in the data warehouse, representing the true start of the data lake. It is designed to store data in its raw, untransformed state, yet in an organised and accessible manner. This layer ensures that the data is preserved in its original form, allowing for … Read more