0%

An example of writeback in accoMASTERDATA

First step SQL Database

In the following, we will go through the setup of accoMASTERDATA based on an example scenario where we will handle a dimension table named "DimEmployee"

First, you’ll need to create a table with your desired columns. In our example from the demo, we created a "DimEmployee" table with the following columns:

CREATE TABLE [dbo].[DimEmployee]( 
[EmployeeID] [int] IDENTITY(1,1) NOT NULL, -- IDENTITY(1,1) = auto-increment starting at 1, step 1. 
[Employee Name] [nvarchar](100) NULL, 
[Employment Date] [date] NULL, 
[Termination Date] [date] NULL, 
[Salary] [decimal](18, 6) NULL, 
[OrganizationKey] [int] NULL 
)

This table will be your writeback table, where any changes made in accoMASTERDATA will be stores, once the configuration is complete.

Furthermore, we’d advise that you create a view of your dimension with any possible transformations. For the purpose of our demonstration, we created the following view:


CREATE VIEW [dbo].[vw_Dim_Employee] AS 
SELECT 
    [EmployeeID], 
    [Employee Name], 
    [Employment Date], 
    CAST(FORMAT([Employment Date], 'yyyyMMdd') AS int) AS [Employment Date_Int], 
    ISNULL([Termination Date], CONVERT(date, '2152-12-31')) AS [Termination Date], 
    CAST(FORMAT(ISNULL([Termination Date], CONVERT(date, '2152-12-31')), 'yyyyMMdd') AS int) AS [Termination Date_Int], 
    [Salary], 
    [OrganizationKey]
FROM [dbo].[DimEmployee];

After creating our view, we are ready to add the view to our PowerBI data model using DirectQuery. The reason for doing so is that we want to see our changes instantly after saving, instead of pending for a new import data load.

0%