Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Power bi Vs Backend databases

There is a question that has never been covered, regarding what is effective Power bi development, meaning that the dashboard is NOT trying to solve things that SHOULD be solved in the backend databases.

 
I talked about this with many Power bi analysts and there seems to be a consensus that a Power Bi dashboard that goes above the below 3 items range, is because there is no proper DB architecture in the backend. Therefore, we should see a red flag if:
 
1-A dashboard has more than 3-5 tables/views joined
2-A dashboard has more than 3-5 measures
3-A dashboard using measures that are longer than 1 entry line, meaning DAX that resembles a long SQL script rather than a simple DAX calculation.
 
I am not asking if Power Bi can handle dozens of table joins or 100 measures.
 
Q1:
What I am asking is if you agree that a typical company dashboard to see sales by region or injuries should conform to the above items ranges, and if not, it is because the backend databases were not well thought out ?
 
I am asking because my IT department has done things that don't seem right, for example:
 
1-To create a hierarchy table (who is who's boss), the IT dept.  provided 2 DB tables instead of one, plus I had to create many large measures just to produce a PBi hierarchy table visual.
2-Just to calculate active employees by month, IT also provided 4 tables (including a calendar table), plus I had to created several large measures just to produce a monthly graph.
3-One dashboard to understand simple metrics like total hours and productivity needed almost 90 measures and many of them are 5-10 lines in length, all this because the backend was not well thought out.
 
Q2:
So, wouldn't you say that the above 3 examples should trigger some concern about the way the backend databases are being developed ?
 
Q3:
In my opinion, proper backend DB development is when you are able to simply drag data fields into your visuals, everything goes smoothly, so you can build your dashboard without much effort. Would you agree?
 
I believe these are interesting questions, many times we face poor backend database development, and sometimes, we face analysts building monsters of complexity dashboards either to show off their skills and/or to try to find job security by complicating things.
 

Regards.

2 Replies

  • mahenkj2's avatar
    mahenkj2
    Solution Sage
    4 years ago

    Hi Anonymous ,

     

    Very interesting thoughts and questions.

     

    I would not say that such topic/questions are not covered yet, but rather this is related with proper BI development. I have gone through all of the phases of whole development of BI solution for our organization and being not of IT field originally; I started with questions from User's perspective and not of a developer's. Perhaps this helped me to search for a proper design of the solution we should look at. 

    Without going into technicality, there is difference in data entry and data retrieval for reporting and this difference needs to be taken care in the design of backend. What you are seeking is definitely possible that user just need to select fields from few tables and should be able to get answers related to a specific business process and build the report easily. There are few approaches to achieve this, and dimensional modelling is one of the most simple, efficient and flexible.

    Business process is broken down in natural sub processes and such each sub process have couple of fact table and several dimensional tables. In this case, user gets answers to his questions for that sub process by using just these tables. Then the sub processes are any way related each other and users need answers to their questions combining few sub processes and to achieve this, aggregate tables are further create depending the business needs.

    The above is a very rough description of what needs to be done for an optimized BI solution.

    In my specific case, I started with fetching data from ERP tables and in the beginning created reports directly transforming in Power BI. It was so quick to reach to dashboardreports, and used transformations in almost every table. Once learnt dimensional modelling concept, then used SSIS to fetch ERP tables, transformed in the form suiting to DM fundamentals and created data warehouse for each business process.

    Then created reports in Power BI using warehouse tables, we now no more access to ERP tables directly. Since warehouse tables are designed from user perspective, most key metric are suitably fitted in warehouse tables, in Power BI most of the time we need basic DAX (very basic) to do arrive at some KPIs. Certainly sometimes for some advanced calculation, we may try to leverage benefits of DAX, but try to use Power BI as visualization tool most of the time. Yet, we should not be hard on specifying number of tables, number of measures etc. It should depend on the nature of the process, given the process is properly broken down (naturally).

    If you need any specific guidance, further please let me know.

     

    Hope it helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello,

       

      Many thanks for your reply.

      I was thinking about what you said and realized that perhaps I asked the wrong questions.

       

      Perhaps the question I should be asking is:

       

      If a Power Bi dashboard developer had at his/her disposal a very saavy backend database developer to create any backend solution they might need, then you would not need to create lenghty DAX scripts and join many different tables or views within the Power Bi dashboard to solve the challenges presented by the databases you have available. So, when we have to utilize lenghty DAX scripts and have to Join too many tables/views, it is because the backend databases are not providing in an effective way what we need.

       

      Would you agree with the above ?