none
Tabular models - developing a DW directly in tabular?

    Question

  • Hi,

    I need to "re"create several Business Objects reports in Power BI, and i must probably will use SSAS Tabular in the analytical layer - till now, all OK. My problem is that the existing DW (in Oracle), from what i've heard (haven't access yet), doesn't truly respect Kimball's dimensional modeling but, from what i've know and hear, dimensional modeling must be followed (yes, there are specific exceptions) in DW and BI systems, and particularly in Power BI.

    So, has i haven't got a DW and must probably won't have the authorization and time to develop, is it viable to create a DW directly in SSAS tabular, through carefully developed PL-SQL expressions? In a sense, these PL-SQL expressions will be the ETL to the DW...

    Regards

     

    Tuesday, June 12, 2018 10:20 PM

All replies

  • Hi,

    The best way is to create views in Oracle which perform your ETL operations and get the views imported into DSV in SSAS tabular and establish the logical relationships between the tables.

    I am not sure if all the functions of PL/SQL expression are supported in a named query in SSAS DSV although technically you could write a simple PL/SQL query in SSAS taular.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Tuesday, June 12, 2018 11:10 PM
  • Hi Lrmmf_,

    Thanks for your question.

    >>>is it viable to create a DW directly in SSAS tabular, through carefully developed PL-SQL expressions?
    No, I would not suggest to do this. If you are creating DW directly in SSAS tabular, You might suffer fron poor processing performance, hard readability and maintainability of measure and calculated column. While analyzing workload on the server during the processing, you may find out that most of the time SSAS engine was recalculating these calculated columns. Obviously, SSAS engine is not calculating values of those columns only in order to get new rows, but it is also recalculating all the existing rows.

    I would suggest to create views in Oracle as Ram mentioned. see below blog talking about advantages using views in tabular model:
    https://cathydumas.com/2012/02/27/using-views-in-the-a-tabular-model/


    Best Regards
    Willson Yuan
    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

    • Proposed as answer by alexander fun Wednesday, June 13, 2018 9:23 PM
    Wednesday, June 13, 2018 4:42 AM
    Moderator