cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KasperJ90
Helper III
Helper III

PowerBI datamart history data and access to SQL DB?

Hi,

I see the new introduction of datamarts as a great simple step for us who needs a step between source data and PowerBI. https://docs.microsoft.com/en-us/power-bi/transform-model/datamarts/datamarts-overview

 

However I have two questions to the new datamart.

1) I want to setup a datamart linked to a SQL DB, but here I need to add historical tables to show actual sales May 2022 vs forecast made in May 2021 etc. Can it be used to also create "history tables" in the Azure SQL DB behind the datamart?

2) Can I access the Azure SQL DB behind in the "Microsft Azure Portal" without using PowerBI?

 

Thank you in advance!

3 REPLIES 3
otravers
Super User
Super User

1) You can create snapshots in dataflows using the Bring Your Own Storage option. Then you can load these snapshots from ADLS Gen 2 in your datamart.

 

2) No, the datamart's SQL database can't be managed from the Azure portal. It is accessible via a SQL endpoint put that's purely so that you can query the views generated in the database by the datamart.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
More tips and guidance in my Power BI architecture and development blog
Ailsa-msft
Community Support
Community Support

Hi  @KasperJ90 

(1) I don't quite understand that you mentioned "historical tables" , in what form does it exist ? 

I tested a scenario. Create a datamart, then connect to SDL, after loading the data from SQL, I connect to the Excel data source again, and the data inside is also successfully loaded into the datamart. At the same time, the dataset automatically generated by datamart contains these two data sources, and I can create a report with the data of these two data sources at the same time.

 

(2) For your second question , you can access the Azure SQL DB in datamart .But I don't quite understand what is "behind in the "Microsft Azure Portal" without using PowerBI" , can you explain it in detail ?

 

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Ailsa-msft 

1) Thank you for your effort. Maybe an example could help.

Let's say I have SQL DB with a sales table in a company including both actual sales and forecasts. I want to show in PowerBI/Excel what is in that sales table today, but also the same date last year. This way I can see how the actual sales is today compared to the forecast last year. How can I set this up with datamarts?
2) I mean can I access the datamart created via PowerBI via the Azure web portal? This will give better control and possible I could also add further data to the SQL DB created by the datamart.

Thank you for your time and help!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors