Design Q: PickUp FlatFile, Load to DB, Associate to Cube, to Enhance Report
Hello,
I have a design question, on how, if possible, to pick up data from user and use in a cube. I have an existing tabular cube, and it has a table loaded with Accounts.
1. User has a list of certain accounts unique to his needs
2. Create table on SQL Server to set flat file spec
3. Note specific drop point for flat file (file share drive)
4. Create SSIS package to pick up and load flat file
5. Modify the existing cube model to integrate this one off new table
6. Once table created, SSIS and SSAS Model pushed, it would look for flat file, and load daily, no data it would trunc table, if new file of data (in proper format), then new data gets loaded
Result: Now User can use this in Power BI and Excel/Pivot reporting, and can drop a flat file at will to get new accounts into the load results for updated reporting.
So how feasible is this? It’s easy to spell out, what do I need to know or watch-out for in the details?
Thanks,
JPQ