Dataflow, SQL for newbie

[randomcobbler]

I only know how to create dashboards in PBI using Excel and I was tasked to search about APIs, Datalakes, Datamart, and SQL server. These are new to me since I only know the basics

Context: I will create a training dashboard metrics wherein it refreshes weekly. Data source will be SharePoint MS Forms, SuccessFactors LMS, and a vendor-led LMS. Probably all are in text format.

How do I connect these data to PowerBI if we want it to have less manual intervention? Do we need SQL? Or will Datamart suffice (I still have to learn what Datamart is too)?

Badly need help because I am really not well-versed in this.

Thank you!

[randomcobbler]

thank you so much!

[Martin_Hubsters · Accepted solution]

Yes, you need to build the PowerQuery accordingly. E.g. instead of hardcoding a training or survey into a query (e.g. as part of the URL for a connector) you need to keep the query dynamic, e.g., first load a list of trainings or surveys, then per survey load the responses. This is just an example, you need to check the structure of the data which is specific to each system and potentially different in each system.

Be aware that using the PowerQuery graphical user interface to create the PowerQuery steps tends to create rather static code where you'd think it creates dynamic code. For example, if you load data from an Excel spreadsheet, PowerQuery automatically creates a step to apply datatypes to the columns. But this is static code, not updated on every refresh, so a refresh would fail if you change the name of a column, even if in a later step you discard the column anyway. But this is no problem, you just need to be aware of this and check the generated PowerQuery code and keep it appropriately dynamic for your use case, so the automatic refresh will work and even include new trainings or serveys automatically. It's up to you to implement this capability in PowerQuery, the tools are there.

[Martin_D]

Hi @randomcobbler ,

If this is all requirements you have you don't need Datalakes, Datamart, or SQL Server. You can load your training data directly from the source systems into Power BI, as you are used to from Excel files, but without creating files inbetween (if the vendor-led LMS doesn't give Power BI access to the data, then any other of the suggested solutions won't help either for automation).

• For getting data from SharePoint, use the built-in connectors in Power BI. just read the manual: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-sharepoint-online-list 
• Connecting Forms to Power BI used to be a bit tricky (but what is not tricky in the Microsoft universe?). Here is one possible solution: https://www.youtube.com/watch?v=HupBVO1P8_M 
• For connecting SuccessFactors use the OData connector or Web connector in Power BI. Information can be found in this community: https://community.fabric.microsoft.com/t5/Desktop/PowerBI-and-SuccessFactors-LMS/m-p/986080#M470122 
• For connecting to your vendor-led LMS you need to provide more information about how data is stored or which interfaces are available for getting data out of the LMS or ask the vendor.

BR

Martin

[randomcobbler]

Hi @Martin_D appreciate the links and will look into it today. Will this work if there would be continuous data coming in due to scheduled trainings? we're looking into putting less work on manual importing and more on just scheduled refreshing

[Martin_D]

Yes, you would set up a so called "scheduled refresh" in Power BI Service which can load the latest additions up to every 30 minutes automatically. But in most use cases a refresh once a day is sufficient (probably, if so far manual preparation was sufficient for you, and the requirement is just to reduce manual work, not to increase refresh frequency, once a day would be the right choice for you to start with).

[randomcobbler]

Agree on the scheduled refresh option.

Let's say we have new training data from SF. When connecting data directly to PBI or from SQL to PBI, will the new data add to the dashboard upon refresh, or do I still have to edit via Power Query?