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

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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors