Hello All,
I am setting up the database for our not-so-technical business users to be able to leverage Power BI. What structures are best for this purpose ( dimensional modeling, copies of source system tables, purpose built views for each report, defined Power BI datasets )? I’m interested to hear success stories about what’s working for end user ad-hoc type reporting.
@DaleB Welcome to Enterprise DNA community and thanks for posting the question on the forum.
Well the Database setup is subjective and can’t be the same for every organization. It depends how you are setting up the database, what system software(s) you are running, what’s the magnitude of dataset, how the reports will be refreshed and how many times. It’s very convenient if you can actually access your database directly from Power BI and extract the data which is not the same in my case where I have to first export the excel or CSV files from the system and then bring it back to PBI.
It’s not just the setting up of a database, there are also other factors to consider like how the reports will be distributed across the organization and roles management.
Even if you are setting up the database for ad-hoc type reporting, I would advise you to set it up properly because the current ad-hoc type reporting can soon become permanent reports that will create difficulties in the future.
Maybe if you can share more details like how you are looking to set it up and what results are you trying to achieve, you can get loads of invaluable information here.
Thanks.
Hi Dale,
Good question. You want to have the back-end (tables, views, relationships, semantics…) as smooth as possible so that developing in Power BI becomes a breeze right?
The most common solution in IT for this problem is to build a Datawarehouse. It is a set of tables which combine data from multiple source systems into aggregated tables, which can then be loaded into Power BI.
However, building a solid DWH is quite a project. If you are interested in this and the microsoft tooling, you could look into Azure Data Factory, Azure Synapse (or Azure SQL DB).
Note that you can also fulfill your needs on a smaller scale by creating stored procedures and views in SQL. You could concatenate multiple tables into a single well-organized view and let users from Power BI connect to this view.
In the end, it is important to understand that the better you set up your back-end (via good source system control, good aggregated tables (via SQL stored procedures or via bigger DWH tooling), the easier it will be to build Power BI reports.
Regards,
Rens
3 Likes
Welcome to the Enterprise DNA Forum @DaleB. We’re happy to have you in the community. To get started, I highly suggest you check these guides that Sam created for us to build a more collaborative environment. How To Use The Enterprise DNA Support Forum
Hi MudassirAli,
Agreed that there is a lot to consider. We do allow access to the database(s) from Power BI. We get feedback that the EDW fact and dimension type model doesn’t conform to the users conception of the data they see in their source system. Alternatively, providing copies of source system table structures raises training issues because of the complexity of the data and relationships. Which structure do you prefer for reporting?
@DaleB It seems both the methods you mentioned has his cons. If I had the choice in my company, I would have preferred to directly access the database and then import (Not Direct Query) the reports I need for the analysis. Providing source copies add one extra step that means additional supervision and management of data.
In your scenario, you know best what method can easily be implemented with less hurdles and training.
Whatever method you choose, we will fully support you in your decision 
Hi @DaleB, did the response/training content provided by @MudassirAli help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Hi @DaleB. If the EDW fact and dimension tables aren’t how the end users would like to interact with the data, then you can develop a model that models the EDW data in a way that makes sense to them in Power BI and issue as a template (PBIT) for the end users to do their own ad-hoc analyses.
For forum users to pursue further, please post a small sample of your existing EDW structure that doesn’t work for your end users, a mock-up of the related structure that would be useful to your end users, and a mock-up of their desired outcome.
Greg
Hi @DaleB, did the response provided by @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Hi @DaleB, we’ve noticed that no response has been received from you since the 11th of December. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
Hi @DaleB, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
While providing direct access to source tables can be efficient, it may also expose users to complexities and risks associated with modifying or querying raw lead data enrichment. Providing copies of source tables or curated data sets can help mitigate these risks while still enabling users to perform ad-hoc analysis.