none
Designing tables for a report, dimensional or OLTP model ? RRS feed

  • Question

  • Hi,

       I got a vague requirement from the client and wondering if I should design that in a Analysis Services. However, I'm new to the SSAS. So please advice if I should build dimensional, fact tables and cube to build this report or just a OLTP table and implement Slowly changing dimensional concept to load the data.

    If dimensional, should I build one dimensional table per each business vertical and one fact table for all dimensions or one fact table per each dimensional table as the drilldown report will have different columns per each business vertical.

    Report:



    • Edited by guest369 Friday, August 16, 2019 4:42 PM
    Friday, August 16, 2019 4:12 PM

Answers

  • Got you, thanks Will!

    If you have no other doubts, please kindly close thread by marking useful reply as answer.

    Thanks for your contribution.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by guest369 Friday, August 23, 2019 3:06 PM
    Friday, August 23, 2019 8:01 AM

All replies

  •  I got a vague requirement from the client

    You got a vague requirement and we don't know that one … nor existing structures, data, etc., so it's not possible to give you an advise.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, August 17, 2019 5:51 AM
  • Hi guest369,

    Thanks for your post.

    For such report, it is hard to answer your doubts. I don't know how to get the values of these columns in the report, and what is the structure of the data source like? What is the business logic between the data columns of data source and the columns of the report above?

    Best Regards,

    Will

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 19, 2019 6:13 AM
  • Thanks Olaf & Will!

    I think I've put everything in one question which is confusing. Basically I need to design the tables for Sales data first and they should hold the Pass and Fail values for each KRI and I'm thinking to do something like in the step 2 in the process which I've explained below.  Wondering if that's ideal or can I leverage SSAS and cubes for this step 2 and is this Slowly Changing Dimension Concept or should I implement Dimension and Fact table concept ? I've no idea.

    So the source data for Sales Business Vertical is just one flat table like below. Ideally there will be one flat table for each Business Vertical.

    3 Steps:

     1. Load piece - Load data into the flat table using SSIS

     2. Design tables and load the data into those for each KRI like below and mark as Pass, Fail1, Fail2, etc. depending on the business rule of that KRI. Here is the data structure sample for the top 2 KRI's that are in Sales.

    KRI 1 rule - rule is if the email sent date is <=5 days of the order received date then Pass otherwise Fail

    KRI 2 rule - rule is if the coupon amount is greater than $9 then it's Fail otherwise Pass for less and No coupon code.

    3. Report - Report will be build using the top 2 tables that are designed per each KRI.

    Basically the idea is to build a consolidated KRI (Key Risk Indicators) report for various business verticals in the company and each business vertical has it own set of KRI's and rules using which the report should be build.

    Example just take Sales Business Vertical from the report and if you take the first row which is for the Sales vertical, the title column has an description of an KRI and the rule is if the email sent date is <=5 days of the order received date then it's under compliance otherwise out of compliance. Also, if the overall result% is below 5% for this KRI then the Score Flag column should have pass.

    Also, when the user clicks on the count under "Total Number" it should bring both the sales records from the KRI tables. If the user clicks on "Number in Compliance" count then it should bring the records up which are under compliance as per the business rule.  

    I've to do this for all the KRI's in sales business vertical and aggregate them by KRI.

    Later on do the above 3 steps on each Business Vertical KRI's and then consolidate the data in one report for all the business verticals.
















    • Edited by guest369 Monday, August 19, 2019 6:23 PM
    Monday, August 19, 2019 4:33 PM
  • Hi guest369,

    Thanks for your detailed explanation.

    I know that each rows of the report are based on different business conditions, then compute the aggregate data. It sounds like that requirement is not possible to create specific dimension tables, because values of the dimension column are different kinds of conditions for the fact table, some rows are overlap calculated.

    In my opinion, it is not suitable to achieve it in SSAS.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 20, 2019 9:04 AM
  • Got you, thanks Will!
    • Edited by guest369 Thursday, August 22, 2019 3:57 PM
    Thursday, August 22, 2019 3:57 PM
  • Got you, thanks Will!

    If you have no other doubts, please kindly close thread by marking useful reply as answer.

    Thanks for your contribution.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by guest369 Friday, August 23, 2019 3:06 PM
    Friday, August 23, 2019 8:01 AM