Exploring the preview of datamart in Power BI!

We take a look at the newly announced preview of datamart in Power BI! A no code experience to build a relational database for analytics right inside Power BI!
Announcement Blog: powerbi.micros...
Documentation: docs.microsoft...
📢 Become a member: guyinacu.be/me...
*******************
Want to take your Power BI skills to the next level? We have training courses available to help you with your journey.
🎓 Guy in a Cube courses: guyinacu.be/co...
*******************
LET'S CONNECT!
*******************
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
🛠 Check out my Tools page - guyinacube.com...
#PowerBI #datamart #GuyInACube

[@culpritdesign]

Calling these data marts is a little disingenuous. Is it possible to persist data in these tables? meaning, do an upsert, keep records that were deleted in the source, create a slowly changing dimension, persist fact records regardless of what is happening in sources. If a source system goes away then data should persist in the data mart. Maybe these are features that will come in the future. I like the spirit here, and I like the idea of separating data models from power bi reports. This feels closer to low-code SSAS tabular. I saw they only allow reads for now, but maybe at some point you can do DML statements via low-code interface.

[@datacentric]

Great for democratizing data but how do we avoid this being another data silo.

[@infojupitersolutionsti3442]

This has been a huge dilemma for me in recent years! Democratization is great but there’s a BIG DANGER looming with regards to data governance! Here’s a use case : A friend of mine was working at a big furniture store. When covid hit, deliveries became a huge challenge and customer complained a lot; there was a lot of cancellation too. The company decided to charge customers on delivery date (instead of purchase date); hence it was decided by the data governance comity to modify the MONTHLY_SALES measure to use delivery date instead of purchase date. Despite the fact that this change took place in the primary enterprise DW, which is the official source of all the smaller datamarts across the company, Marketing and HR_sales_commission DID NOT update their local personalized MONTHLY_SALES measure. It was a mess! So, I’d say, if you decide to go the self-service route, make sure to invest money and ressources in a good CoE (CENTER of EXPERTISE), that will create a strong HORIZONTAL community of users across all departments. In anyway, that’s my solution to mitigate the danger.

[@shehzeervp2379]

Does this mean we don't need to create pbix files for data sets? For me it sounds like most of the development we do in pbi desktop can be eliminated, and especially the "download/publish pbix" cycle.

[@iampresent.digital236]

Question: is there still a place for dataflows? What is the best practice? Setting up a datamart to import data from an on prem sql server? Or keep using dataflows and then plugging datamart to it? Which is more efficient?

[@thejonath]

Wondering the same thing, if the end result of the datamart is, say, a dataset and not the individual dataflow queries, then I still prefer dataflow for the ability to reuse dataflow across multiple dataset... Lots of exploration to do!

[@RoyGodfrey]

That was my immediate question I would love a Dataflow vs Datamart use case video guys

[@ricardosaucedobanuelos4239]

You can check this video, might help with the concepts: kzbin.info/www/bejne/eXK9nZSfppdniZI

[@tommynguyen4253]

With dataflow, you cant connect from Excel, SSMS. It is not the case for datamart. Dataflow still has its own position, since you can give them different refresh schedules, and reuse them in different datamart, or power BI dataset

[@cristianprifti]

This will help clarifying this aspect. There is still place for both. docs.microsoft.com/en-us/power-bi/transform-model/datamarts/datamarts-overview

[@tszreder]

This looks almost too good to be true :) One question though: can you use datamart as a source for building another dataset? E.g. to combine data from different datamarts (and datamarts becoming a kind of reusable ETL just like dataflows :))

[@kaeldowdy]

Question ??: wondering about use-cases for PBI datamarts. The Cube Guys gave an example of a datamart being set up for a business area for that department's own use. What about taking a datamart approach to centralize core datasets to support all enterprise PBI reporting? What would be the pros/cons in that use case vs. using dataflows?

[@mugs132]

Same reason I’m here. Please do a video on this, cube guys!

[@seanoloughlin1543]

While this seems cool, I’m not sure if the common business user would see much more benefit from connecting to this versus a shared power bi dataset. Main difference I can see is the SQL server access, anything else?

[@hi55us2]

@Nick D And a premium-only feature. Hate how a lot of these new features are creeping in as a premium only feature which simply makes no financial sense for my company.

[@narayanawindenberger7396]

In the past, dataflows were saved as flat files. Querying those flat files was slow, especially if it was a large file, since PowerQuery had to load the entire file. To address they, MSFT created the Enhanced Compute Engine, but it was buggy and didn't seem to work that well. To me the big value add is that the output of PQ gets saved to a standalone, queryable SQL Server instance. Folding and query optimizations should now happen natively, so I expect good performance benefits from this, especially when loading the Datamart tables and doing further transformations into a new PBIX.

[@koborkutya7338]

I don't really see the difference in an user perpsective from a bigger dataset I built up in PBI Desktop and published it. Maybe the only difference is you can set up different refresh schedules for the different data sources and you don't need PBI Desktop - but then it should be called PBI Online or something. Maybe an IT head is excited that you can query SQL but I don't see why is that better than making some report page in a PBI report.

[@frikkinnorwegian5227]

My thoughts exactly. Seems like they just moved the dataset to the cloud, which means any business user would still have to deal with the complexity of Power BI Desktop, just in different steps. There's still power query, row level security, DAX, modeling, SQL statements in PQ have been kinda made into their own thing. Then since you're already in Premium, of course you could connect with SSMS since you got xmla endpoints enabled. But you got limitations like no calculated columns, no calculated tables, requires Power BI Premium, will likely chew away at your included premium capacity, and no write XMLA. Likely just going to push the dataset sprawl into datamarts instead of datasets, and a far more costly at that. A PBIX containing a dataset and dataflows for each data source would likely be far more manageable and reusable than a datamart. Feels pretty underwhelming to be honest. Like a bundle of existing components that are marketed as a revolutionary product still looking for a need to address, rather than a product designed to meet a certain need. I just don't see the value proposition here.

[@oscaranibalhernandez8590]

I watched the presentation on Microsoft Build 2022 thinking I hope GuyInACube talk about these tomorrow but you do not wait for tomorrow huh?

[@twitchyfingerss]

I'm super excited because my company uses both PowerBI and Tableau. I can do my ETL in Power BI, have some PowerBI reports, and reuse the same ETL in Tableau visualizations in those departments that are on Tableau! We don't have anyone in IT to do ETL, so I'm doing it on the side as a business user.

[@uncleted9362]

You can connect regular powerbi datasets to tableau too since 2019.

[@Knight4cJonas]

For business user?? ? they dont know the meaning of datamarts and do Sql. I see this as just another option to complicate again the Powerbi ecosystem. Why not providing a builtin Sql database in Azure data factory?

[@Molaa21]

Amazing feature!! How do you separate when to build a DW (+SSAS ??) and when to use a DataMart straight into PBI Service?, Does BI / DW Dev + Data Engineers skills are no longer need? or maybe less needed? A Business User with good skills in PQ and data modeling can create a super powerfull datamart without a BI Team?

[@rapchak2]

Hey guys! Love your videos! Great explanation here. Question for you, I stood up a Datamart for a client and was wondering how each query executes. With individual flows, I can schedule each to run concurrently. If one fails, the rest still run. Do Datamarts function in a similar fashion? Similarly, if I do not want to refresh specific queries (think master data connected to csv on SharePoint) when the model refreshes, is this possible?

[@AlexisVal-m3b]

Hello! Great explanation, i have a doubt. Why are you saying that now calculated tables are not needed anymore because we have sql queries? the queries i create cant be used in the model, they are just for analizing the data. What am i not understanding?

[@beardarianguard]

Boom, there goes one version of the truth and complete data sprawl across the organisation. Bad move especially as it is premium and thus I guess anything created cannot be shared unless to another premium user........costs through the roof.

[@GT-bf7io]

Question: What about stored procedures, triggers, views? So in our good old SQL Server we got some stored procedures and triggers that help with automation. Can we create stored procedures PowerBI datamart?

[@zoloturu6322]

Question: Is there any way to do version control of Datamart? If somebody deletes it how to recover that?

[@MatthewRoche]

Love it! This is the most complete and concise introduction to datamarts that I've seen to date. Very well done!

[@matthiask4602]

I find it cheap to sell an online version of PBI(x) as something new. It is not. And: It is definitly not a anything near datawarehouse, all the pain of admin your many databases is still there. It is only the "view" of modelling some of your tables in a relational way. Is it not? So it would be no more than the waited for "MS Access online". Was that the project name? :-)

[@fenrir433]

can you connect datamart datasets to tabular editor?

[@robscheffler1864]

This is bananas! Will we be able to *easily* copy over existing Power BI desktop queries into the Datamart? Or even import an entire model (including all measures)? Like other commenters, I'm excited to push more of the power query ETL workload into a server and free up my local machine to do...well, I'll figure out something else for it to do!

[@jeroendekk1]

It's Power Query, so you can just copy and paste your query's. Like on Data Flows.

[@ruoyingli9443]

Question: if user could save their sql query result as a new dataset in datamart? like using the datasets there to query and then doing like create view or something to save what they queried as a new table saved in the same location where the data table stored?

[@antondickinson4720]

How does this work in the Power BI deployment pipeline? Been waiting years for this. Great stuff.

[@ashdam]

Whats the difference between this and creating a dataset?

[@neelterdal]

Context : We currently use Azure Analysis Services to power our dashboards. It's getting extremely costly for us to maintain the AAS - memory wise. Question: Can we shift our architecture to datamarts ? Provided that we need a daily refresh of those data sets and our current model size is around 120 GB.

[@srinivaspappu]

Hi Neel, Can you suggest if this worked for you ?

[@sirlanceohlot]

Thank you Adam & Patrick for diving into this in a way that is straight to the point :) I am excited for datamarts in Power BI as I can see this being a boon for our newer international offices where they don't have all of the bells and whistles we have in our US offices!

[@swerick]

Wow, this is really cool. Connecting with SSMS, etc. and SQL is a real game changer.

[@ewetoob3898]

Is there any place I can find info/guidance on how to successfully use the Datamart connection string in various 3rd party tools? It works automagically in PBID & Excel (using the SQL Server connector) but, at least for me, not so much in any other 3rd party tools.

[@jessecrammer2431]

Is there any ideas on when this'll be out of preview? I love the Azure SQL integration!

[@Bharath_PBI]

Hi, Does the dataset (auto created) has to be in direct query (will it allow different storage modes) or can it have agg models? I believe just like database endpoint, there's a dataset endpoint which we can connect to Tabular editor or any external tool. [PS: learnt from Reza Rad, it has dataset endpoint too] Wish the read/write will be available instead of just read, permissions to the database, in case IT has to be involved in something like optimization of queries. Finally, is it possible to download the bim / pbix , for version control? Thanks.

[@laurendittmann4765]

I too am wondering about version control. - If you (or your dog :)) deletes the wrong table accidentally, can you go back to the earlier version?

[@richardcroft3676]

Hurrah! Functionality to help users create more and more data silos 😂

[@AHMEDALDAFAAE1]

Thank you for this video!! However, I have questions: - Do we need a gateway for datamarts syncing? If no, that will be great in fact - Can we do ETL on an existing Azure SQL database?If yes, this feature is a revolution !! I think the only situation we need on-prem gateway is when use have data behind a VNET, please correct me if I am wrong! Thank you again!!

[@noahhadro8213]

Can you refresh individual tables here like you can in SQL? Also who can have access to the data mart. I see you showed RLS but do you also have to Authenticate them? This seems like Dataflows on steroids. Hope it performs better than Dataflows. Thanks for the great video

[@charleswebb5444]

You can setup individual incremental refresh rules for tables but otherwise the refresh schedules apply to all data sources. We authenticate using Azure AD. So a role is assigned to an AD identity - user or group

[@siddheshamrutkar8684]

Hey Guys.. It's a cool feature but can we allow business to do the self serve just by loading related tables? And how much data capcity it allows to hold and what kind of licensing one would need to hold that data and do the analysis.

[@JohnHall]

I do love what is happening in this space, but I also want to smack everyone involved when it all starts/ends w/ SQL structured data. The assumption is always that we have some structured data that I need to copy/move to some other structured place and structure structure structure structure structure... That's always the assumption. The journey is always "an exception" when it is un/semi-structured. That is 100% the reverse of the problem most of the people IN THE BUSINESS have. They'll build TOWARD structure in some ways over time. And this need to rush toward structure holds back so many Low Code projects. If I'm going to now analyze 2 million rows of data, sure, now I'm ready to structure my data. But if I'm building out a quick table for a department, why in the world do I honestly care about data structures? And why are we forcing the business to learn it? Most people in the business are building out Pivot Tables in Excel, but also DON'T KNOW HOW TO BUILD SIMPLE TABLES IN EXCEL!!! They are only following KZbin videos and don't actually understand data structure. So please, PLEASE, stop including structured data as the Alpha/Omega of Low/No-code conversations.

[@mcakir14]

Looks nice. We only want to make this functionality available to a select group of users instead of everyone within the organization. How do I set this up in the tenant?

[@zchase6631]

Question: I am on premium per user license, and have created premium workspace, can i make use of the dataset in datamarts to publish in power bi but to be consumed by user having pro license.

[@mjc543]

0:32 "Power BI dataflows was just announced at Build 2022" ... was it? Dataflows have been around for a while so why would they just be announcing it now?

[@matthiask4602]

Does it support field parameters? :-) That would be a must have.

[@ach8779]

Hi! great video! What is the difference between using an Excel file uploaded to OneDrive/SharePoint and using a datamart? The first thing that comes to mind is update speed: if I have a table that pulls data from an Excel file in OneDrive/SharePoint and this Excel file gets updated with new rows or changes, it takes some time for these changes to be reflected in Power BI. On the other hand, with a datamart, changes to the data source could lead to an immediate update of data in Power BI. Is that correct? Thank you.😃

[@afchattha]

Would like to explore how can we sync back DataMart to the on-premises SQL server . Datamart sql connection to SQL Connection to keep a copy on local Sql server.

[@tonya3308]

Can you use a PBI Datamart as a data source in PowerApps? If the backend of the PowerApps is sql and the PA captures data and sends it to SQL, which feeds the datamart, can I refresh the datamart from PowerApps?

[@powermykola]

may jump when it is planned to launch the ability to save requests in a datamart? ...Your query will not be saved. This feature is coming soon!...

[@wesleyfletcher7654]

Meh. Is Power BI online really the biggest need? I'd rather have improved visuals, etc...

[@imax8307]

Can you do a video on incremental refresh within the datamart

[@davidruiz4544]

I am trying to create a data group (grouping) like Power BI Desktop, but this option is blocked in web, what can I do??

[@mattr.6212]

I've been experimenting with a datamart in Power BI, but it seem like it doesn't support date hierarchies... Does that sound correct or am I missing something?

[@laryssasnascimento]

Great video, great new feature!! A question I have is related to refreshes. Can we setup separate refreshes for each table or only to the datamart itself?

[@denwo1982]

Yes this is what I wanted to know, otherwise I cannot see the purpose of using this datamart and stick to dataflow

[@maniebotmanie]

Any chance of fixing the audio on this as the heavy breathing audible whilst other is talking is very disruptive.

[@manojpraveen5976]

How to copy the data mart to different environments like Dev, UAT, Prod etc.

[@christinateilmannknudsen3264]

Very cool! But wonder how it affects the capacity and performance of other reports within that capacity?

[@hamedrismanian6056]

Q: Is it possible to uses Gateway in Datamart? I need to pull data from Jira which is on-premises.

[@srikanthravula90]

Hi Guys, I have a business need to solve, I need to get particular visuals from multiple existing reports into a single report and also giving the users the ability to control filters and slicers. But the real problem is each visual is made using big files sizes that cannot be loaded into a single report all together. Is there any solution from you guys to solve this, apart from suggesting dashboards. will datamarts help me here. Please help.

[@jackstrudley1039]

Hi guys, Amazing video as always. Any idea if Dynamic RLS works? I can only see capabilities for basic RLS. Thanks

[@leaveMYtoyALONE]

Same here, for sure dax style -> userprincipalname() filtering would be amazing. Any ideas?

[@marounsader318]

i find it unfair in datamarts we can use sql and see results directly while in PBI desktop we cant, bring it come on!! :)

[@rashidkhalid2010]

okay how do you access PBI Datasets inside the PBI Datamarts? Could not find a way.

[@sets4life769]

Hi I have a question, with the Datamart SQL Editor, am i able to write a query in SQL and use the query to build a dashboard

[@lakshmimurali4384]

This is amazing! I just want to know how this is different from dataflows? In the sense, what are the additional features that datamart provides that dataflow doesn't? Will be useful to know.

[@cristianprifti]

The dataflow will load and do the ETL, the datamart will act as the relational database where you load multiple dataflows, other sources and then you put them together including relationships etc.

[@lakshmimurali4384]

@@cristianprifti thank you

[@thomasivarsson1291]

The Power BI DM assumes matching keys and granularities so it's perfect to use on top of a DWH that has done all the integration work.

[@PeterNovakIng]

Hi, it is possible to feed the datamart in workspace1 with dataset from workspace2? or into the datamart need to be loaded the sources from the dataset is created?

[@theg9811]

Hi fellas, Do you know if a datamart retreive data from different power bi tennants? or do we have to copy them manually to datamart on main tenant?

[@erickampel8154]

so is this creating a local sql server db? Can you create temporary tables in it? Can you create stored procs?

[@janostoth6306]

Hmm, Power Bi Desktop capabilities without burning the CPU and memory capacity of my laptop? If that is the case I assume even quicker transformation steps in the server query editor than in my desktop

[@koborkutya7338]

don't really get this, if I make a report in PBI Desktop I just upload it to Service and it does not eat my laptop's resources anymore.

[@kadirkkkk]

3:30 wrapping things up and getting error, ahahahaha. yeah thats funny but real. there are some glitches that stops you getting further

[@williamrhopkins]

Can I consume the DataMart with just a PBI Pro licence?

[@brianeverhart5348]

Is there a way to use calculation groups in datamarts? Tested by loading my dataflows into one and it seems so much faster than my current dataset but I have all of my time intelligence built using calculation groups so hoping there is a way to accomplish the same thing in datamarts.

[@lucafontana3504]

Hi, did you found an answer to this? I have the same question

[@Chris_87BC]

can you create temp tables in the data mart using SQL?

[@micahdail]

I love the banter. You guys need to meet up more often! 3:27 😂

[@joelgagnon1]

This is a great functionality! Is there a chance for it to ever get GA?

[@manojpraveen5976]

Is there any parameters option to change the data source connections like in desktop?

[@johnbasha8561]

can we connect to Power BI data mart server as a sql connection in another Power BI desktop ?

[@ChristianKey]

Excellent new feature! Thanks for the fast update!

[@fredrikovesson]

I dont get it tbh. Is it just like a shared dataset but it can be queried using SQL?

[@venkyreddy4138]

I have a small dought can we push power bi service data in to database is it possible

[@charlesallen4821]

This is really cool technology and I can't wait to try it.

[@holahandstrom]

The "DataMart builder" enables quick 'Business built/requested DataMarts'. The 'Self Service' acronym was used in the 1950's for Gas Stations ...

[@cyberv]

Be aware that Power BI datamart backend is a managed Azure SQL database, but with a limited database size up to 100GB. For more than 100GB database, Azure SQL is still your option. For big data in PB size then still AZURE Synapse

[@jorgedelgado4846]

My dataflows used to work perfectly few hours ago, them apear the message of datamart and start to get crazy!!!! Did you know something about issues like these before the publication of DATAMARTS in BI Services?

[@alexisalbaceterodriguez1723]

Hi guys! I have been exploring datamarts this month and I have found a big issue with RLS. If I use a dataflow to extract data from a datamart with RLS, the RLS does not work. I can still see all the rows in the table. I have done the test in Power BI Desktop connecting to the datamart or the dataset (using both connectors), and here the RLS does apply. I have also connected to a datamart with management studio, and the RLS here does not work. So, there are still some BIG SECURITY issues using the SQL or Azure SQL connection.

[@rcb3921]

Rock Paper Scissors Lizard Spock!

[@kirill53]

It feels so frustrating that Microsoft offers tons of Datasources to pull data from, except: from other Power BI Datasets, in particular, from Push Datasets created and populated with REST APIs. It makes Push Datasets way less useful.

[@miranikkila9672]

What about the Azure SQL capacity pricing, it cannot be offered for free? Can you use existing Azure SQL database?

[@shadow9600]

This seems like SSAS in the PBI service but using Power Query and DAX. Is there a benefit to using SSAS over Datamarts or Datamarts over SSAS?

[@isjajo]

I think SSAS is probably better for large scale and Datamarts is better for ease of use

[@hugomancera9330]

With all this Hogwarts magic going on with Datamarts.. Would PBI Web Service be adding some Deep Learning or Machine Learning with Datamarts in a future path?

[@jac.ber.444]

You can create calculated columns. I did it.

[@anilkumaryarra5468]

Hello Adam& Patrick. You guys are amazing. Well explained. I have question that is What is the maximum size of a data mart? Thanks in advance.

[@anthonjhnon1]

Does this replace dataflows?

[@juliocaubin8091]

ROLAP is coming back!!

[@fenrir433]

can you connect datamart datasets to tabular editor?

[@davidlopez-fe2lb]

That Power BI drag & drop GUI for building data looks oddly familiar to Tableau Prep, just sayin.

[@MuhammadShoaib-ui5jz]

That is amazing!! I am just curios if we could connect datamarts with the SSRS. I mean same sources for BI and SSRS reporting.

[@ZiggyBoon]

Granted, I’m being a schmuck, but if both of you are doing the video together, wouldn’t you be “guys” in a cube? 😀

[@srbasha74]

With experience from other BI platforms, I feel there is too much excitement for something slightly better than what is there today. It's funny when you say this bridges the gap between business and IT. What does that is a logical object model, which the business can understand, like a semantic graph or Ontology of sorts

[@jfnisar]

Wow this looks amazing!...

[@missubetam7098]

Hello Patrick, I am really appreciate your work including your greetings😃. I am learning power bi and your videos help me a lot.

[@fernalication]

Nice, although 25 secs to return a select * statement..... I'm scratching my head right now 🤔

[@sahmedalee]

how to get the data from the datamart to PowerBI

[@AkshayKumar-vd5wn]

Move along, move along. This won't be free it will be premium feature which will include creating additional account to make another additional account where you will have to pay extra for premium premium feature and then sell your soul.

[@Creative_Cabin]

❤

[@Szpzer]

Hi Adam & Patrick, thanks a lot for the nice explanation. My question is: is it possible to share datamarts between workspaces? Is it possible to (1) create a datamart outside of a workspace or (2) create a datamart inside a workspace and share this datamart with other workspaces? Has this anything to do with the endpoint (at the end of your video)?

[@data2decision255]

Hi Pat, quick one. If i can create datamart in PBI, store data in PBI and use it in SSMS or Excel etc... how will i incremental refresh the data and create history if required..

[@FastingDude]

I knew that tshirt wass familiar! I have the same Rock Paper Scissor Lizard Spock!