Hi everyone,
i would like to get your opinion on what would be best practice to store data and use in Power BI.
I have Sales Data from 2015 up to now and sales information are update on daily basis. At every month end, i go through month end check then combine into yearly table and MTD data are during the month. I have about 600k-1millions rows in a year sales data file.
Also i have Customer Master data, promotional plan Master data, Master product data, Master customer outlet table.
Current Sales data are in Microsoft Access and Master data are in excel file. All future Sales data will be coming from ODBC feed into Power BI.
Sales data need to perform quite a few ETL steps once imported from the Database. Do you suggest to perform ETL then store in Database then use in Power BI? Power BI refresh is struggling so how should i set up past data up to last year and current year data?
Should i be using other type of cloud database like Microsoft Azure SQL database so that refresh can be done online as currently refresh cant be done online due to Microsoft Access.
In future, get stores visit information from CRM and compare with B2B sales growth to determine sales effectiveness. i would like to get data from Google Analytic to analyse consumer trend and overlay with B2B sales data. May be work with unstructure data. I think this is to be considered when designing how we store data.
Could you also suggest learning path for me to design the architecture of the business then use Power BI to analyse and create dashboard?
Also is there any suggestion on how to publish dashboard to non-Power BI users ? Is there any good solution to use Power Apps to publish dashboard based on role like, Sales Manager, CEO, CFO ? There are about 30 to 40 users in the business and i am the only one responsible for data.
I would very much appreciate any guidance on this. I am doing everything for the business so any tips and contacts would be appreciated.