Problem Summary
I have multiple different applications which collate data and currently output different data files (CSV, JSON, XML). I have several reports that I am building in Power BI, which use the data files along with other existing structured data sources (SQL Databases, Microsoft Sentinel). I need to produce multiple of the same reports for different clients on a frequent basis.
I know I need to move away from the separate files, it's cumbersome and I can't search through all my data (all my client base) at once, but I don't know what the best reporting data store to move to is. I want to almost define a data store which contains data from all of my reports, which links to my clients so I can create efficient reports which show trends over time. I am not really limited by flexibility as I can manipulate data in the application before sending it to the data store, however I would like to avoid this if possible.
- The BI solution I will be using is Power BI.
- Obviously, I am very much at a concept phase.
- I have experience in using both SQL and NoSQL databases, but not solely for reporting.
Ideal solution
The ideal solution that I am looking for is a system which is fast, and integrates well with Power BI, probably using Direct Query. It needs to be somewhat scalable and I need to be able to shows trends in data over time for each individual report. In the ideal world it would be just as easy to search through data for all my clients as it is to segregate the data on a client by client basis. The reports must be able to show trends over time.
Questions
- Most of my data sources produce JSON data (or other file format), but is this any reason to use a noSQL database over a relational database?
- Are there common best practice approaches and technologies for designing data structures for reporting requirements?
- What sort of solution do I need to achieve this.
Sorry if this question is all over the place, I just seem a little bit lost with all the options (Data Lake, SQL, NoSQL, Dataverse, etc). I have tried to include as much information as possible, but if you require more info please ask and I can edit the response.
