Hello everybody,
TL;DR: I am writing this question not to ask how to do things in Power BI, but I want to know if Power BI is the right tools for querying detailed data.
Question detail:
Currently, I am developing a business intelligence solution for our company, and things seem to me that I should use the Microsoft BI solution for our needs. But the problem is: I know that Power BI's xVelosity or Azure Analysis Service tabular model are powerful engines to AGGREGATE data which can perform actions like GROUP BY, COUNT, SUM, AVG.... very fast, questions like how many/ total sum of... can be processed very efficiently. Power BI Visualization is also a amazing tool to visualize these numbers....
But I really doubt if Power BI/Azure Analysis Service is a suitable tool to QUERY DETAILED DATA. In fact, the request of my company for the BI solution (currently) is mostly about query detail data. The pattern of questions is usually like this: "Find the detail data of a transaction that is made by customer A and have been processed by the system B....". The requests are usually about "querying the detailed data" and "displaying the detailed data in table". The needs of aggregation (count, sum, avg) is still, but I feel that they (high level executives/stake holders) care more about the detailed data over aggregation.
What I am aiming for is building a system that can do both detail queries and aggregation easily and effectively. I want to eliminate the needs of data engineers or somebodies that must have a deep knowledge of SQL to involve in making a report. I want that data engineers like me just need to handle the inner core of the system such as table relationships, calculated columns and measures, other staff only need to know the very basic drag and drop... And it seems to me that Power BI is very suitable for this need, espicially for aggregations and visualizations of aggregations...
But what about the detail queries? I have test several cases and it seems that the Power BI engine and a Star-Schema database can do the job, they can still generate the correct data and show them in table visualization (With some limitations but I think they are still acceptable). But I don't really know if I should use a engine that is optimized for AGGREGATING to QUERY detailed data. Is it a good practice? Is the performance good? I have even made some exceptions (which are clearly opposed to the star-schema best-practices) to the design of the data model, just adapt it to some patterns of requests.
Also, what I know so far, when querying data using power BI, the performer should also be aware of features like "Auto-Exists" or "Empty row removal" which may causes wrong result in some rare cases (such as mixing measures and columns from different tables in a table visualizaton). I would say, even I still fear of unintended triggering of "auto-exists" and "empty row removal". I doubt that if other people which is supposed to be "normal user" can know how to handle these cases.
Do you have any thought related to this? Any thought would be highly appreciated.
