Splitting a Power BI (PBIX) File into a Dataset and Lean Report File
HTML-код
- Опубликовано: 2 авг 2024
- Learn how to take an existing import mode Power BI Desktop (PBIX) file and split it into two files. One for a Power BI Dataset Model and another for a Lean Report connected to the published dataset.
RELATED LINKS 🔗
Power BI Tips - powerbi.tips/2020/06/split-an...
LET'S CONNECT! 🧑🏽🤝🧑🏽 🌟
-- / havensbi
-- / reidhavens
-- / havensconsulting
VIDEO CHAPTERS 🎥
0:00 - Start of Video
0:06 - Content Intro
0:35 - Start of Demo
1:30 - Creating Connected Dataset
2:36 - Creating Lean Report
HAVENS CONSULTING PAGES 📄
Home Page - www.havensconsulting.net
Blog - www.havensconsulting.net/blog-...
Blog Files - www.havensconsulting.net/blog-...
Files & Templates - www.havensconsulting.net/files...
Consulting Services - www.havensconsulting.net/consu...
Contact & Support - www.havensconsulting.net/conta...
EMAIL US AT 📧
info@havensconsulting.net
#PowerBI #PBI #microsoftpowerbi #HavensConsulting #powerplatform #microsoft #businessintelligence #datascience #office #data #digitaltransformation #dataanalytics #tableau #excel #powerapps #datavisualization #dashboard #sharepoint #python #bi #analytics #cloud #azure #bigdata #sqlserver #software #sql #dynamics #dataanalysis #yammer #microsoftpowerbi #onedrive #machinelearning #bhfyp #powerbidesktop 
Наука
as a former access database programmer, I totally appreciate the ability to split the "front-end" from the "back-end"....and this was definitely on my wish list. Just didn't think to do a google search for split data file.....very efficient. Thanks for the tip, totally did not want to go the powershell route.
Awesome tip. I was wondering how to separate the data from the visuals for a while. It is easier than i thought in the first place. I will implement it asap. This will save me a lot of time
Great video Reid, thanks for the tip! Good way to reuse premade datasets across multiple reports
I stumbled on that video at the exact right time ! I have some dashboards that I needed to put online and I thought I had to recreate all the models online in datasets ! Thanks a lot ! This spared me dozens of dozens hours of work ! Cheers !
Glad to hear it saved you so much time!
@@HavensConsulting One question, can you schedule a refresh on the dataset (e.g. once a day) and see the desktop version of dashboard refreshed at this frequency or do you need to publish the dashboard linked to the dataset in order to see the refreshed data ? Thanks again for this great tip.
@@erikdessiex1026 as long as the Dashboard contains tiles pinned from the Thin Report it will maintain a connection with it, and get updated data when it does. More info about refreshing and tiles can be accessed here. docs.microsoft.com/en-us/power-bi/connect-data/refresh-data
wow!! Eye opening. Thanks Reid! This is awesome
Reid, I followed the instructions (removed the tables) but I get the error, The connect live option for this file is disabled because it already contains data from another data sources. (this happens when I select Power BI dataset). I have the August 2020 version. any thoughts? when I opened a blank power bi report, then it works when I select Power BI datasets
Thanks for sharing this Reid...got the tip linked from Patrick at GiC. Looking forward to learning more from your channel, too!
Awesome! Thank you!
wow. don't know what i just did. but it works. thanks
Thanks you Reid. Its really helpful!!!
Glad it was helpful!
What is not explicitly shown in the video is that with the "report without data" -file you need to also delete the data tables from the right side of the UI from the Fields-listing. I struggled with this for a while, getting the "power bi connect live option for this file is disabled because it already contains data from another file" -error. Cheers for a great video!
That’s exactly right. The model needs to be completely empty. Either of Power Query queries or DAX calculated tables. 🙂
This comment saved me!
Amazing! Thanks very much, short and sweet :)
Great insight, thanks so much!
You came at the right time...again!!! Thank you
Happy to help :)
Great¡. I usually design the pages in the data model file and then copy and paste all visuals in the final report files. This lets me test new measures faster.
This works really well, and I often do this. :)
absolutely love the idea, thanks👍🏻
Fantastic tip!
This is a life saver!!
This is exactly what we do. We also do this so that when someone wants to use our "PBI Template" PBIX file as a start for a live file...they simply delete the built in calendar and other queries we have and then connect to the proper datasets.
Works so well!
Awesome!
Real Great explination
Ótima dica!
Good approach , I haven't tried yet this way
Very cool
Amazing 🙏
Great trick! Awesome
Really Awesome!
Thanks Daniel :D
Nice. Thank you!
Sure thing!
Thanks for the video, that's very handy. I got a question here, after we split the dataset and report file, how do I edit the report again and view the details of the measures?
Great trick!
Thank you!
Is there a way to do it from PowerShell or some other code? We want to separate dataset and report for better governance in our premium capacity.
TOP!
Cool trick !! 🥂🥂
Thanks!
Hi Reid, great video, thanks! I used one Dataset to feed two identical reports for different audiences sitting on two different Workspaces and in separate Apps for distribution, but the datasource is only refreshed by the only one dataset, which is great. Another doubt I had is how to backup the pbix file, but in PBI Service, Datasets, in the elipsis there is the option to download the pbix file, so all good. 👍
I found a show-stopper problem here. The user in the second Workspace's App where the report is reading the Dataset in the first Workspace cannot see the report. This user doesn't have permission to the first Workspace's App where both the Dataset and the other identical report reside. The error showing with this user in the second Workspace is something like this: "Can't see the report because doesn't have permission to the underlying dataset".
Great video! it is exactly what I need to get to the golden model reusable level. I've already trying to apply this. Itried the steps and I'm stuck on permissions in the PBI service when I publish the PBIX with the report and pointing to the dataset. Haven't been able to find why I'm able to publish the dataset pbix in the earlier step just fine. Something about live connection access.I have access to the original data.
Yeah I'm honestly not sure why either with the level of info you provided. Hopefully you get it to work though!
Hi, please can you reiterate the benefits of splitting the dataset from the frontend report view? also can you still publish this split report on pbi server? if yes, does it still make it a spllit?
Great video and so usefull! If i can ask a not so related question, why does your dashboard seems so huge? Do you have any custom page size set up? I’m a new user and when i’m creating my visuals, my page seems rather limited and cluttered…
I’m honestly just using the standard 16:9 default size for the pages. Though it might be because I configure the font size for my visuals as well.
Really a nice option ! We might have saved a lot of time if we got to know this earlier itself, as we came across same issue and literally copy pasted each visual by creating a new report !
But if we got report specific measures, then we need to create those again after splitting the dataset and report. Is there any cool trick for that as well, if I need to retain few measures in the report but not want to make those available in the model.
Hi Manohar, you will have to create new measures. But with live connected models, you can add new measures directly in the Lean Report (PBIX) file. So no need to add them to the actual dataset if they only apply to one report, just add them in the one report file. 😊
Great tip, thanks for sharing! Have you experienced some performance bottleneck by publishing model and report in different workspaces?
Hi Marcio, I have not. Splitting the model and report doesn't impact the performance, versus the exact same model in a single file with the report.
If the Dataset and report are published in separate workspaces and we want this in a premium capacity, do both workspaces need to be marked as premium?
Is it possible to split the data model from the report when using Power BI for Report Server?
Report server doesn't support Power BI datasets as a data source sadly.
thanks for a great tutorial.. what are limitations of lean report file which use a live connection ..
i observed that :
we cant use power query editor anymore?
edit relationships in report file possible?
some options are gary out
You certainly still can! The model isn't in the lean report any more. So model changes need to be made in the model PBIX file, where you can make those Power Query transformations if needed when editing the model. 😊
Is there a way to hide the model tab? So if someone else is creating a report they don't have access to the model?
Can u pls guide me or give me a some material to refer to on how to make a report based on dates
For example: File overdue for review (where I need to go and compare two date columns but I see the date hierarchy when I set the column type to Date or date/ Time so I am super confused on how to handle these two date columns).
Pls help me out of this confusion.
Great video, how do you deal with calculated tables?
For the thin report, and DAX calculated tables would be selected from the model view and deleted, after that then you can connect it to the published model. :)
i have published both lean report and dataset to a workspace. can we refresh the data within the lean report?( by clicking the refresh button there) without directly refreshing the data model
Good question. The lean model just connects to the dataset. Refreshing from there only pulls data from the dataset it won’t trigger a dataset refresh. That will only happen during scheduled refreshes of the dataset itself. Or if you go to the dataset options and trigger a manual refresh.
Thanks for the video! I have a concern: after publishing the dataset, what if i want to modify my Measure? Do I need to download the data set to powerbidesktop => modify => republish?
Hi there! You should be definitely versioning and saving the PBIX/PBIP files either to SharePoint, OneDrive, Git, Azure DevOps. Reports/Semantic Models shouldn't be re-downloaded from the service.
In a typical deployment process. You'd reopen your stored PBIX (Semantic Model), make a change to the measure, table, relationship, etc. Save it to your storage that has version control. Then re-publish to the appropriate Power BI workspace. :)
Thanks for your video! Do you have any suggestions or best practices for the management and publishing of these splitted report parts? For instance: Use a separete workspace for dataset, how to handle permissions since you might have to manage the permission of an app and of the dataset in another workspace and so on ..
Thanks for your feedback!
It will really depend on the org, and current governance practices. But typically I keep the dataset and report in the same workspace, but just as few datasets as necessary to service the X number of reports you have.
@@HavensConsulting Thanks for your answer. That makes some sense. But what if the owner of the model differs from the owner(s) of the report(s)?
@@robertbarkovicz800 even in this case. If there is an established governance practice, or version control practice (i.e. using SharePoint to host PBIX files) the both the report and model developer will still be following them. Typically I see more scenarios where the developer manages both models and reports.
Hello, Thanks for the video. Do you know how fixing RLS security in this case. It seems not working when the report and data are separated. Do you have any solution to fix-it please ? thnaks a lot.
RLS in the thin report would be inherited from whatever is configured in the model, and can still assign people to the designated RLS groups. Unfortunately without looking at the model and workspace configuration I don’t have enough information to determine the root cause of the issue
Very interesting. I see a great advantage when the data set could potentially be used throughout an organization. Does this method improve performance of the lean reports?
Performance is unchanged as it is still the same model aggregating the data. But the reusability is amazing!
@@HavensConsulting So it's better than you published SSAS tabular model because Power BI has power query, right ?
@@Lixia123 SSAS also has access to Power Query in Visual Studio. Both it and Power BI can leverage those data transformation tools
Thank you for a nice tutorial. Just a question, how to split a pbi report which includes custom created DAX table properly? I have tried it and it stayed in the dataset file, but is not accessible in the report, which is connected to this dataset (and I was forced to delete it before it allows me to connect to the dataset). Thank you for your opinion. Best regards, Monika
Hi Monika, all objects in the dataset should be accessible from the new report connecting to it using the Power BI datasets feature. When you're splitting them though you have to delete both all queries in the query editor, and all DAX generated tables, before you can connect to the power BI dataset you split.
I like this technique and I keep the original desktop pbix so I can update the dataset. Is there any other way to change the dataset aside from republishing it from the original pbix?
Good question. You could use Tabular Editor to connect to the dataset and make edits, but that is only available for Premium (Per User or Capacity) workspaces with XMLA read/write enables. Otherwise Dataset edits today can only be made in desktop
Thanks for a great tutorial. I was wondering whether it is possible in a similar fashion to decouple the data model edition capability from the data file itself. What I want to achieve is to upload a very large (huge!) data file that is being updated incrementally, which could feed several 'lean' report files, but I want to retain the ability to easily edit the data model and uploading it to the cloud without uploading all the data every time along with it.
Hello! I don't know if this could be useful but you can try setting incremental refresh in Power BI
For anyone reading this with the same issue, a way around this is to add a Power Query parameter (n) to your data model and then have a PQ step that then limits the fact table to only load n rows of data. You need to ensure the step folds back to the data source for this to be worthwhile.
Then when you work on the file in Desktop you can limit the rows to a manageable number (a few thousand for instance).
Then publish the dataset and change the parameter in the PBI service to a number much bigger than the max no. of rows your fact table will ever have.
Hi, thank you so much for this video! I have a question. when I try to delete the queries I get a msg that says cannot be deleted because it's being referenced by another query: and it gives me only one option to close
Good question. That means that another query is using that one as a data source (reference). So in order to delete this one, you'd need to delete any other query first that is referencing this one, before deletion. Also doing a select all (control+a) on all the queries in the editor will allow for all of them to be deleted at the same time.
In this splitting, can we able to create DAX measures and do something in Power Query editor in the report pbix?
Good question. In the lean report you can create Report Level measures. Any model related changes (Tables, relationships, query transformations, etc.) can only be added to the dataset.
@@HavensConsulting Thank you :)
Hi Reid. Once the report and dataset are decoupled, what's the best way to maintain the dataset going forward? Edit it from the pbix that was used to publish the dataset to the PBI service? Will re-publishing that to the service cause it to break connection with any of the reports that use it? Thank you
Hi Andy, to make model changes you need to make that on the "dataset" PBIX file. Republishing that doesn't break anything, it will just update it. Breaking changes would be if you were to delete a measure from the model (as example), and then republished, then reports using that measure would have a broken visual.
@@HavensConsulting that makes sense. Thanks very much!
Can you make a connection to 2 different datasets? For instance I want to publish multiple small data models, so I can mix & match according to what I need (i''m going to create multiple reports and most of them will use 1 master dataset in combination with some specific dataset).
You can by using composite models :)
learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models
Thanks for your video. Is there any workaround to add new queries or tables into that newly created report ? I did not find the way...
Good question. The lean report only uses data in the dataset. If you want a new query or table for the report, add it to the dataset, and you'll see it show up after publishing the dataset, when opening the lean report.
@@HavensConsulting This sounds legit! Does it impact the performance of the other lean reports? I mean, if 10 people all have 3 extra tables to add to the base file.. Is there a guideline to review if or when you need to duplicate/renew your base model and start adding to it?
@@bobsnijders4182 there really isn't a specific guideline. It's more of a case by case basis where you need to review the data that needs to be added, and determine whether or not you should add it to the core model, or add it into the lean report. You also can now create composite models against tabular (Power BI) datasets. So you can create a live connection against a published dataset, and add additional imported tables if you want! This feature just came out in preview in December 2020. powerbi.microsoft.com/en-us/blog/power-bi-december-2020-feature-summary/
Thank you for the tutorial, but how to do the opposite action? I mean, I have a lean report and I want to attach to him a physical database from which it is built
Hi Mike! So this is a one directional item based on the design of Power BI desktop. To recombine you'd need to copy/paste back and rebuild the report pages, bookmarks, etc back into the core dataset. Easy one way, not easy the other direction today..
Excelate video. Thanks for sharing. But, it could have enriched the content a little more if you had shown the size of the different reports before and after the connection to the Dataset.
Thanks for the feedback! Size is good to consider as well but the primary goal of seperating the two is to reduce the number of models you'd need to manage. E.g. 10 reports and 10 dataset (silo'd), vs 10 reports and 1 dataset (shared). Huge reduction in maintenance and cost to keep those updated or when requirements change :)
How did you recover the reports after deleting the tabs
If you're using PBIP's and source control with GIT, you can use the branches to restore to a previous version. Otherwise you'd need to revert to a previous version of the file. PBIR is coming soon though and will make report rollbacks like this easier :)
Reid, how do you go back to the report if a small change is required, e.g. changing a slicer default value, and publish it again?
Any changes such as default slicer selections or other report changes can be made to the lean report file, then republished 🙂
@@HavensConsulting Thank you for answering. Yes, I got confused when in PBI Service, in the Workspace, Reports, clicking in the elipsis (more options) there's no option to download the pbix file. But opening the report then under File there's the option to do it. Cheers.
What is the name of the pie chart you used in your visualization in this video?
Great question! It's the Chord visual. appsource.microsoft.com/en-us/product/power-bi-visuals/wa104380761?tab=overview
We want to use CI/CD to deploy our reports (currently pbix) to multiple environments. Can we still do that if the report is split. Currently plan on using "Power BI Actions" Az devops extension for the deployments.
Hi there, I'm not entirely following when you say "environments". But you can publish this model to one workspace, and have reports connected to it, published to other workspaces in the tenant.
@@HavensConsulting Thanks for replying so promptly. Sorry for confusion, yes by environment I meant workspaces. Our workspaces represent development environments like DEV, TEST and PROD. Our goal is to automate all the publishing to different environments using Azure DevOps. Hopefully, data model files can be published similar to report files.
@@creedeffect in that case it should work fine. You could even have a master workspace with most of your models, then publish reports from them to various workspaces.
Is there a way to refresh the lean report using a power automate flow? Currently the dataset reside in one workspace and the report resides in another workspace.
Hi there! So the report itself doesn't have anything that needs refreshing. When you open it, it always pulls the latest data from the semantic model into the visuals. You can use Power Automate to refresh the semantic model whenever you'd like though, per the workspace max refreshes of a semantic model
@@HavensConsulting Thanks, Appreciate your response. Your channel is very informative and helpful.
The issue that I encountered with this is that if your report has variety of roles based on row level security (Under Manage Roles), then you can not test these roles out anymore, as the 'View as' is disabled in the Lean report file :(
Can there be a workaround for that?
The only way to test a role is in the model file itself. If needed you can Control + A to select all visuals on a page in the lean report, Control + V to then paste them into the model PBIX file and quickly test a report page that way with RLS in the model. Or keep a few hidden role test tabs in the master model as well
Hello, Can we make this work with RLS ?
On PBI Service, I wasn't able to test a role as it says "Role testing in row-level security (RLS) currently requires a report. Please build a report with this dataset to use role testing."
On PBI Desktop, I am unable to select "Manage Roles" and "View as"
If you want to test RLS in the service using the "test as user role" it will need an accompanied report page. In this case your model PBIX file could have a single page with one visual purely for testing purposes that has data that would change. Publish that, then use the "test as user role" feature.
Thanks for the info. How can you reverse the process from the 2 files and create 1 master file?
Hi Juan, great question. It's mostly a one directional process. You can copy/paste each report page visuals (using Control A, copy/paste) between the reports. But this wouldn't take with it bookmarks, etc. This process is really meant just for splitting, not re-combining.
@@HavensConsulting thanks for your time. That's exactly what I've faced into.
I find it's often the case where we'll have one "Master Report", for example a Full Sales Dashboard. Then multiple smaller specialist reports, eg "Sales Rep Performance Tracker".
Because of the extra time associated with modifying and improving an existing report/dataset that is split across 2 pbix files, would you see any reason why we shouldn't have our "Master Report" as 1 unified pbix file and then any other specialist reports just connect to the master report dataset?
The downside now comes with the fact that whenever the "master report" needs updated, then you also need to update the semantic model, since they're one and the same. If you split them, you don't have to update both. If you're also using deployment pipelines, then this means you might even then need to migrate these model updates across 2-3 workspace even. So that's why it's typically recommended to split all reports from the model. To prevent unnecessary updates to the semantic model, unless you're actually making model changes. Hope this helps!
Can we do it vice-versa..
I mean, now since the report is connected to online dataset (workspace), can we connect the same report back to an offline dataset (local copy on desktop).
Hi Girish, it only works in this direction. Since the other way would require the queries to be created, relationships, measures, etc. So the model would need to be built that way
@@HavensConsulting .. thanks for this.. I was just scrolling thru PBI community for the same but couldn't get any relevant. Seems when we connect once the report to online dataset, there is no way back as the connection to this dataset is treated by Power BI as "Live Connection". All the buttons in toolbar gets freeze.
Kudos.. Your videos are super awesome and super easy to follow with nice tricks.
Pls do a video on pbix vs pbit vs pbids with latest info.
What type of chart is the one on the bottom right? Is it a custom imported one? Or included with PBI? Also, thanks for allowing me to discover the ribbon chart.
Power BI Chord Visual :)
appsource.microsoft.com/en-us/product/power-bi-visuals/wa104380761
Hi all, I was wondering, Is there any way to hide or lock the DAX formulas in a PBIX file? We provide PBI consulting services to companies and we want to protect our intellectual property while sharing the PBIX file? Any suggestions? Thank you.
Not currently. Typically the way people do that is hosting the AAS models themselves, and letting customers connect with them. However, I've found that clients don't like getting the product they paid for. So I give them the models I've built, 99% of the time they will need additional expertise down the road, and will still come back to good quality work, wanting more.
@@HavensConsulting Thanks for your prompt response. Yeah! We are doing the same as you for now. I hope to see that feature in the future. Thanks again!
Nice! But how do you edit the dataset after the split from and removal of the report?
You would make a modification to the "dataset" PBIX file. Any changes made there can be used/updated then in reports connected to it after publishing
@@HavensConsulting I understand that that is what you do first, but what if you want to alter the dataset after you have removed it's original report?
@@robinneven1237 same thing. Any changes made to the dataset file after you remove the report will pass through to any lean reports connected to it.
@@HavensConsulting so you cannot make any changes to the dataset anymore once you have removed the dataset pbix from both your local computer and the power bi service right?
@@robinneven1237 why would you do that? If you delete the file and remove it from the service, all the lean reports using it would break. The goal is to have that dataset published and separate from the report, to allow multiple lean reports connect to it
Can you also due the opposite? Combine dataset and report into 1 file?
Not as easily sadly. You’d need to create new report pages and copy/paste the visuals one page at a time. This also wouldn’t copy things like bookmarks either
Thanks for the tutorial! I want to share with you one problem with splitting report and dataset. If we publish the dataset in one workspace and the report in another workspace, the users need to be at least contributor at both workspaces to download reports created by using the dataset.
I reported this issue and they actualized the list of limitations and proposed an idea to solve it. This is a limitation and it is quite confusing since, if you give build access to users to the dataset, then, they can create, publish, view and edit reports with this dataset, but they can't download it.
Please, would you vote for the idea to solve this problem?
Voted!
@@HavensConsulting Thanks!!!
Hi Reid, have you seen the new option in the Service > File > Download this file > A copy of your report with live connection to data online (.pbix). To me this should be the easiest way of creating a lean report. However, I get a strange problem with the resulting lean report. When I make changes and then publish those changes aren't seen in the service. Have you tried it?
Hi David! This is a great new way for sure! However that copy doesn't delete the report from the original published dataset/report. Which is what the benefit of the splitting does as well :)
So when you download with your new method. Where are you publishing? Are you trying to overwrite the original file or publishing this lean report to a new workspace? When you have a unique PBIX and name, editing in desktop then republishing to the same workspace it got published to should have those changes visible.
@@HavensConsulting Hi, yes these are great points. I want to delete the previously published import mode report, however this is a bit of a problem given that this is generally in an App and, of course, that has to be unpublished first in order to delete the report. Ideally I would like to just publish the live connect mode report and overwrite the import mode report, however this doesn't work. I tried the all of the manual steps; unpublishing the App, deleting the original report and then republishing the lean version. At first this seems fine, but if I subsequently make a design change to the lean report and republish I find that, even though Desktop tells me publishing is complete, the changes are not actually changed when I look at the report online.
Essentially a rich man's ssas model held in the pbi service. Not necessarily a bad thing. Could do with something that considers the source control side of things. Release pipelines need integration to devops.
Honestly I'd argue the opposite to this. An SSAS model requires an on-prem server or Azure, both of which would cost more to host. Power BI is the least expensive of the three almost universally for people. SSAS to me is the rich man's model
@@HavensConsulting not if your using a premium pbi capacity. But it is a neat way to go. Its basically the same tech in pbi to aas. But with front end to deploy to.
@@DarkOri3nt even at the Premium level it is a bit still less expensive than having the same size Azure instance to supply the same number of models and reports and then connect to them with thin reports in Power BI. The nice thing is that it isn't basically the same, it is the same. Power BI models are azure AAS models hosted in the cloud with a Power BI front end. One differences are really the level of configuration you can do in Power BI, versus AAS.
🌟🌟🌟🌟🌟
Do you have a video on how to reverse this step? When you would want to change the Lean Report to use again a common data source and not the published Dataset? Or adding an additional data source into the same Lean Report turning it back into a PBIX file? Once I have done the splitting the Get Data section is grayed out.
Here’s a video showing how to add additional data with a connected dataset 🙂
ruclips.net/video/zktkhM9ZTAE/видео.html
@@HavensConsulting Thank you, that's also useful. However it looks like I can't switch back from a published Lean Report using a live connection to PowerBI dataset on the server to a local connection without using External Tools like Hot Swap ( ruclips.net/video/sygIUPMlgi0/видео.html ). Or has there been a Microsoft supported solution since?
@@eszterkovacs3081 there isn't sadly. It's really a one way transition once you separate the model from report. Outside of copy/pasting all the visuals per page from the lean report back into the model PBIX file. It copies everything except bookmarks.
@@HavensConsulting yeah, that's what I want to avoid as it is quite bookmark heavy. I'll give it a go with Hot Swap out of curiosity but possibly it will be easier to maintain the two files separately till a major developmental change is requested. Thank you for your prompt replies though, it's really appreciated.
@@eszterkovacs3081 is there a reason you’re wanting to combine them again? Usually most people don’t go back after splitting them
Hi, somehow this doesn't work for me: connecting to the dataset works but when I publish the report it seem the PBI service creates a copy of the data set. as a result I end up with a dataset for each report again and the live updating does not work. Am I doing something wrong, is there a trick?
Hi Tobias, I just tested this process and both the dataset and lean report published successfully. When you're publishing the "report" can you confirm it has no local model, and is successfully connected to the Power BI dataset published in the service? You'll see that status saying live connection in the lower right of the PBIX file when open, for the report if so.
Great. Now how to do that in reverse? Marge Dataset Report with Visuals Report
There isn't really a direct way to do that honestly. The closest could be Control+A and select all objects in the lean report. If it's built off the live report already, then pasting them onto a blank report page in the actual model PBIX file should be a clean transfer between the two. One downside is the select pane order can change, and bookmarks don't pass through. Otherwise they paste to all the same spots on the page, and with the fields and formatting working
I wish we can add additional tables to the dataset in the second report.
You can do this! It's called Composite Models over Power BI Datasets or Azure Analysis Services (AAS) Models. It's been available since December 2020. More info here! docs.microsoft.com/en-us/power-platform-release-plan/2020wave2/power-bi/direct-query-over-power-bi-azure-analysis-services
Thanks a lot for this great method. I faced a problem connecting to dataset while implementing this because my base file is connected to Excel files, Google analytics, Facebook, Python script and smth else similar. Is there a way to overcome it?
I'm not sure I understand? Splitting the report and model doesn't change how the model would refresh. If you're having issues with connections that sounds like more of a modeling issue. I'd recommend posting this to the Power BI community forum for technical issues.
@@HavensConsulting I see "Connection in real time is switched off for this file" warning. This happens after uploading dataset-deleting-connecting. For a new file everything works great
@@aplitman thanks for further detail. So I'm trying to follow your explanation. You upload the dataset, then delete it, then connect? Though I'll say I've not seen your particular error message before, and nothing shows up on google when I search for that, which is surprising.
I don't add a screenshot because I work in a Russian model. )) Look, I have a suspicion that it is also necessary to delete all the tables that do not appear at Queries like DAX or manually generated. It is not shown in the video but I guess you mention it verbally
Kinda that helped, thanks again for ideas and support
I think that the issue here is when you have to change eg. names of measurements or columns. It becomes a bit tedious to fix errors in the report.
Honestly I wouldn't consider that an issue, rather a pipeline and governance issue. Table, Measure, and Column names should be set before you deploy, and shouldn't be getting changed regularly after production. New tables, columns, and measures won't have this issue. To me it's not much different than creating a website URL, then if you share that link with people, don't change the URL or it will break the shared links.
Question. If I have multiple lean reports connected to just a single data set. Will this save space in the workspace capacity compared to just duplicating the pbix files itself, causing duplicated datasets?
Yes. As example, having only one model and multiple thin reports connected to it will save a large amount of space, refresh requirements, maintenance, etc.
@@HavensConsulting Thank you for the prompt reply. Appreciate it and this video. Really need this.
@@HavensConsulting I have another question: Am I able to change stuff in Power Query (Create reference tables, append and merge) on the reports without doing it in the data model ? Or do I have to do it in the data model first then it will show on the reports?
@@jomzrpg6312 that can only be changed in the model. The report just connects to it, it has no ability to edit/modify/configure the model it has a live connection to.
@@HavensConsulting Thanks!
Hi Reid, Great video!
I have watched it several times when I had to do this - just to be sure...
However, doing the trick now and selecting a dataset creates a Direct Query connection to the dataset. Not a Live connection.
I can make a Live connection if I start from scratch.
Hi Klaus, a simple connection to a Power BI dataset is a live connection. If you need to also include any IMPORT data, then it becomes "direct query over analysis services dataset". More info about this can be found here. docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-services
@@HavensConsulting True, Reid. Funny part is, some of the tables in my model got imported, the major part were DQ. But all I did was delete all Power Query and then connect to a dataset. Don't get it... However, I made a reporting template and I can then copy/paste my visuals to the new reporting pbix. It seems to work fine.
But I believe only weeks or months ago the trick worked fine with same datamodel. Perhaps a change acc. to version (using April 2021)??
Anyway - Thanks for your reply :)
@@klaustrampedach7312 you also need to delete any calculated tables as well (e.g. calendar table, etc.). From the model, otherwise it defaults to a direct query composite model. Previously this wasn't available so it wouldn't even let you connect at all until you deleted everything from the model first.
@@HavensConsulting You nailed it! Overlooked the 'delete table part' as that's never really been an issue in my models. But this time there were 2 calc tables. The devil's in the detail, they say. Thanks, Reid!
Actually i don't know if you need to do this on a couple hundreds of thousand records fact table (small company) because, at the end, if you have to build or change a measure, you have to open the dataset for it, right?
It really depends on the complexity of the models, and the source datasets. I will say I've yet to have a single client engagement where I don't use at least one shared Power BI dataset between a few or more reports.
@@HavensConsulting sorry, just one more: once you're working your pbi files in a server environment, all users that have access to that server can see or change your dataset. As far as you know, is there a way of protecting your dataset, for example, they can see your model but they can't change it? And for the M code, is there a way to "hide" it?
@@PedroCabraldaCamara with Power BI datasets that are published, those are only accessible for people who are assigned certain roles. Roles like the "reader" role won't have any access except read access for the reports. docs.microsoft.com/en-us/power-bi/collaborate-share/service-new-workspaces
@@HavensConsulting thank you very much for your answer
Ressurecting this old post to add in a little gotcha -
If you remove the report file from the workspace, then publish an updated version of the model - it will actually duplicate the Dataset in the service. In other words, the overwrite functionality does not work if the Report itself does not exist. At least this is the behavior when updating using the APIs say from a DevOps pipeline.
Great callout! Thanks for mentioning this
And I imagine if you ever wanted to make any edits to the model, you just open the saved workbook "dataset" make your edits/republish and you're good?
Unfortunately, the report cannot be published later Public (Web)
For that feature you're correct, publish to web (public) won't work with a lean report. Though the only time I deploy those are for demos or presentations. Never ones for customers.
@@HavensConsulting Too bad, it could save resources.
Only the main database would be updated daily, and other reports would use this data. Thus, each report must be updated separately if it is not a direct query. Thank you. Greeting
For anyone reading this from 2024 onwards, the step @3:29 is now look for "Power BI Semantic Models" as Microsoft recently changed the name of Datasets to Semantic Models.
Thanks for pointing that out!
What is the different from this and certified dataset. Isn't it just two different ways ? You reach the same goal. Maybe there is something I do not catch
A certified dataset is a setting that can be applied to any dataset, whether or not the report and model has been split or not. This is just a practice to reduce the number of unique models in your tenant. Certification is used to indicate the data in a model itself has been verified, and is good quality. docs.microsoft.com/en-us/power-bi/connect-data/service-datasets-certify
You need gateway running to make use of this if your report users are located on some other local network, isn't it?
Unless you're using Power BI On-Premises both the published dataset and report are in the cloud service, no gateway required.
@@HavensConsulting Can you make use of scheduled refresh in PBI Service in case without gateway?
@@1yyymmmddd yes for datasets that are connected to a cloud service. Gateways are only required for on-prem data sources
@@HavensConsulting You publish the data set from PBI Desktop which has some IP address. PBI Service is on Microsoft cloud that has another IP address. How does PBI Service know your IP address to be able to refresh the data set without gateway?
@@1yyymmmddd it doesn't matter where you publish from, the only thing that requires a data sources inside the report, if all data sources are cloud based, then refreshes in the service ONLY refresh from those cloud sources, it doesn't care where the PBIX file came from
End what next? Should I publish lean report as usual, or upload pbix file into web servise?
I’m sorry but I don’t understand your question. You’ve split the report and model, and publish both of them.
Be careful, your report URL/link will change. If you have shared report directly to the users instead of via an app. The old report link won't work and you need to provide access again and share the new link with them.
Power BI datasets are unrelated to sharing a report link. What you’re mentioning isn’t an issue here. As I’m connecting to a published dataset, not sharing a report link. More info about the feature I discuss in my video can be found here. docs.microsoft.com/en-us/power-bi/connect-data/desktop-report-lifecycle-datasets
Just watched your video again. gotcha, You have used a completely different name for the dataset and used this dataset as a live connection in the existing report. That will do. I tried to maintain the same name to the dataset and hence the report link has changed.
FYI - It created a new report with the same name but pointing to the new dateset. It didn't replace the existing/old report.
@@PrasanKumar Hi Prasan, correct. When you do this two new files are created, a report connected to a live model, and a dataset. Because the report is a live connection, and a different artifact type in the service, it can't "replace" an artifact that was an import model.
@@HavensConsulting since they are two different artifacts , we do need to share the new report links to the users who have access to the old report, isn't it?
You are too fast in speaking; it seems there is fight on your back.
I'm just excited about teaching :)
If you need to slow the video down the gear icon gives you an option to adjust the speed, without changing the audio pitch.
Awesome!