data repository
1 Topic
Best Option for Power BI Data Repository
The current Finance role that I am in involves standardizing reports for a wide audience to use. I have developed multiple Power BI reports that others are using, and I want to have a data repository for all current and future Power BI reports. The current location of my data is on my personal computer, but I want the data to be available for other analysts to grab when they want to develop their own Power BI reports/analysis. Unfortunately, I cannot connect directly to SAP HANA (IT will now allow it) but instead have to query the data out of SAP HANA using Analysis for Microsoft Excel and connect to flat files/workbooks that hold the data. I am currently trying to decide on the best location to house the data for the Power BI reports, and I am currently looking into using SharePoint, SQL database, a shared server, or Azure. I think Azure is the least likely option due to cost and the required involvement of IT, but I still want to include it as an option, nonetheless. Is there someone that can give the pros and cons of each (to use as a data repository) for the organization’s Power BI reports? I have never used SQL or Azure as a data connection in Power BI, so any information would be helpful. It is just me that will be maintaining this data repository, so I am trying to keep it simple but efficient. Ideally, I would not be adding a week’s working hours as a database administrator on top of my current role. Below are what I have so far as my options: SharePoint- This is the option I am leaning towards mostly as of now. Pros -It would not require an on-premises data connection for Power BI Service. -Can use a macro to refresh the queries held in each excel workbook that would be held on SharePoint and then refresh the reports in Power BI Service -The team I work for manages the SharePoint site for the organization -It would not require additional cost unless we start to get close to the storage capacity of our SP site Cons -Takes a long time to bring data into Power BI Desktop and slower refresh connecting to an online data source -Can potentially be a lot of excel workbooks and folders SQL database Pros -Can use Select statements within Power BI reports to gather only the data I would need instead of pulling in an entire dataset (Not a huge benefit I think but a benefit, regardless) -Additional capabilities in Power BI because of connection to database? (Direct Query?) -Can create relationships between datasets (Not sure if I would use/need this feature) Cons -Would have to use an on-premises data connection for Power BI Service. Would maybe require a virtual machine to run scheduled refreshes. -Would require the skill and time to manage the database Shared Server Pros -Would be faster than using SharePoint (Online data repository) Cons -Would have to use an on-premises data connection for Power BI Service. Would maybe require a virtual machine to run scheduled refreshes. Azure- Do not know too much about this option. Pros -Can handle a lot of data (More than a SQL db I believe) -Additional capabilities with Power BI? -Would I need an on-premises data connection in Power BI Service for this, since the data is housed in the cloud? Cons -Most complex solution -Costly -Would most likely require assistance of ITSolved13KViews0likes13Comments