2

I'm relatively new to database programming and am looking for some some theory/best practices and feedback on an issue involving multiple dependent, cascading, computed columns for an OLTP SQL Server/Azure environment.

For some context, I've already translated a heavily denormalized reporting table that had hard coded excel formulas (no DAX) into a 3NF RDBMS with one main fact table and all of the applicable dimensions, all with proper primary and foreign key constraints, consistent grain, etc.

The fact table is simply just the heavily normalized set of foreign keys, where applicable, along with the data entry from a user form that will be used to compute the resulting field values.

Here's a super simplified version in a test environment: https://www.mycompiler.io/view/5WbQMoQOOyL

While I can of course render all of the proper denormalized foreign key values in a simple view using JOINS, I find it challenging to know what the best approach would be to render the multiple dependent, cascading calculated or computed columns.

I find a view is too simple and a trigger is (perhaps) too complex. Maybe a stored procedure and or various table value function? I'm honestly not sure.

I'd like to have this information persisted as opposed to computed or calculated each time the data is called in a query or used in a result set for external stakeholders, but I could also be totally off base here and need an entirely different approach. I just want to ensure speed, reliability and accuracy in the processing.

For more context, the total number of records is not very high, roughly 40-50k, adding maybe 9-10k each year, maybe a few hundred give or take each week.

Here's a simplified visual:

Column A -- data entry value by user, captured in fact table
Column B -- data entry value by user, captured in fact table
Column C -- A * B
Column D -- data entry value by user, captured in fact table
Column E -- C * D
Column F -- C - E

Any ideas or suggestions would be greatly appreciated.

2
  • 1
    We probably need a few more details to be able to adequately answer this. Persisted Computed Columns is one possible solution, as Avarkx points out. Indexed Views are another possibility, which will also persist the results of the computed columns. But with the amount of data you're talking about, it doesn't sound like you really need to persist the calculations, unless they're very computationally complex in themselves, in which case the previous two mentioned options might not even be applicable. So knowing how many columns in total, and the formulas being calculated would be helpful here.
    – J.D.
    Mar 14, 2022 at 22:41
  • 1
    Thanks, there are a lot of columns and the user form driving the data can be a little complex with some tiered lookup values and some cumulative tiered aggregations, so I'm going to rework the dummy data and try to re-write a few things before i respond. thanks for your help so far.
    – Will Adams
    Mar 14, 2022 at 23:09

1 Answer 1

2

For what it's worth, I think you already have the solution you're looking for: using PERSISTED computed columns. As a side note, you might want to ease up on your exchange rate datatype definition a little, as exchange rates can fluctuate wildly. I've gone with ( 18, 6 ) below as that tends to maintain typical FX rate precision in the vast majority of rate interchanges I've worked with.

CREATE TABLE [dbo].[Transactions_Test] (
    [Record ID] INT IDENTITY( 1 ,1 )    PRIMARY KEY,
    [Total Gross Fee Local Currency]    DECIMAL( 18, 2 ),
    [Exchange Rate]                     DECIMAL( 18, 6 ),
    -- [Total Gross Fee Local Currency] * [Exchange Rate]
    [Total Gross Fee USD]               AS CONVERT( DECIMAL( 18, 2 ), 
        ( [Total Gross Fee Local Currency] * [Exchange Rate] ) ) PERSISTED,
    [Account Fee Share %]               DECIMAL( 6, 5 ),
    -- [Total Gross Fee USD] * [Account Fee Share %]
    [Account Fee Share $]               AS CONVERT( DECIMAL( 18, 2 ), 
        ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
        * [Account Fee Share %] ) PERSISTED,
    -- [Total Gross Fee USD] - [Account Fee Share $]
    [Adjusted Gross Fee USD]            AS CONVERT( DECIMAL( 18, 2 ), 
        ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
        * [Account Fee Share %] ) ) PERSISTED,
    [Dept Fee Share %]                  DECIMAL( 6, 5 ),
    -- [Adjusted Gross Fee USD] * [Dept Fee Share %]
    [Dept Fee Share $]                  AS CONVERT( DECIMAL( 18, 2 ), 
        ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
         * [Dept Fee Share %] ) PERSISTED,
    -- [Adjusted Gross Fee USD] - [Dept Fee Share $]
    [Net Fee USD]                       AS CONVERT( DECIMAL( 18, 2 ), 
        ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            * [Account Fee Share %] ) )
        - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            * [Dept Fee Share %] ) ) PERSISTED,
    [AM Fee Share %]                    DECIMAL( 6, 5 ),
    -- [Net Fee USD] * [AM Fee Share %]
    [AM Fee Share $]                    AS CONVERT( DECIMAL( 18, 2 ), 
        ( ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            * [Account Fee Share %] ) )
        - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            * [Dept Fee Share %] ) ) 
        * [AM Fee Share %] ),
    -- [Net Fee USD] - [AM Fee Share $]
    [Total Net Fee USD]                 AS CONVERT( DECIMAL( 18, 2 ), 
        ( ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            * [Account Fee Share %] ) )
            - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
                * [Dept Fee Share %] ) )
        - ( ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            * [Account Fee Share %] ) )
        - ( ( [Total Gross Fee Local Currency] * [Exchange Rate] ) 
            * [Dept Fee Share %] ) ) 
        * [AM Fee Share %] ) 
);

INSERT  INTO dbo.Transactions_Test ( [Total Gross Fee Local Currency], 
    [Exchange Rate], [Account Fee Share %], [Dept Fee Share %], [AM Fee Share %] )
VALUES  ( 10, 122.12, 0.12, 0.30, 0.25 );

SELECT  [Record ID], [Total Gross Fee Local Currency], [Exchange Rate], 
        [Total Gross Fee USD], [Account Fee Share %], [Account Fee Share $], 
        [Adjusted Gross Fee USD], [Dept Fee Share %], [Dept Fee Share $],
        [Net Fee USD], [AM Fee Share %], [AM Fee Share $], [Total Net Fee USD]
FROM    dbo.Transactions_Test;

DROP TABLE [dbo].[Transactions_Test];
3
  • This looks like a good start. The Dept Fee Share isn't calculating properly, however, the total is far too low.
    – Will Adams
    Mar 14, 2022 at 22:51
  • @WillAdams Yes I had accidently tossed in an extra multiplication of the Account Fee Share is my copy-pasting frenzy~~ Adjusted now
    – Avarkx
    Mar 14, 2022 at 23:11
  • Not to worry! Thanks for the revision. I think I found a better workaround than these formulas in a CREATE statement. Using CROSS APPLY as a kind of table value function for the simple calcs executed by a stored proc which then executes an INSERT statement to a staging table, ultimately rendering the denormalized data in a vw.
    – Will Adams
    Mar 15, 2022 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.