Forbidden Webpage when accessing community.fabric.microsoft.com
Hi I have a user who is trying to access this site, but is receiving a Forbidden page once the site loads. I'm not sure if its an account issue as it doesnt let her get to the sign-in page. I had her clear her cache/cookies and attempt the page again but same issue. I checked zscaler and our firewall and all traffic seems to be allowed. Any ideas what could be going on? I'm able to access the site fine among other users
Heads up: The Publish to web default is changing and it affects who can create public embed codes
Power BI publish to web enables users to quickly embed a report in a public website. It is not intended for users to share confidential or proprietary information. In the coming weeks we will roll out a change to our default settings that requires Power BI admins to allow public embedding before end users can create new embed codes. The change will not affect existing embed codes, which will keep working as they have been. We're sharing this information now so you can understand the coming changes before they reach your organization. This blog post describes functionality that has not yet been released. We expect the change to reach commercial cloud tenants the week of 1/27/2020 and government cloud tenants the week of 1/27/2020. What is changing By default, users will need to ask a Power BI admin to allow them to create a new publish to web embed code. When a user attempts to create a new publish to web embed code through either the new look or conventional UI … … they will see the following dialog box. If an embed code was previously created for the report, it will be shown as before. Power BI admins will see a new option within the Publish to web tenant setting to Choose how embed codes work. By default, it allows existing embed codes to work, which ensures no public web pages that host reports will be affected as we roll out this change. Users trying to create new embed codes will get the experience as described above. The Power BI admin can change the value to Allow existing and new codes if they desire people in their tenant to be able to create new embed codes. It may also be helpful to establish a process for your organization to ensure your users know when it’s appropriate to use the Publish to web capability. You can even govern this process by selecting the Specific security groups option. It is also valuable to periodically review reports published publicly from your organization. The Embed Codes section of the Power BI Admin portal shows reports published from your organization. The list shows embed codes created by all users and in all workspaces. You can export the list to run a review, view the report to check what it contains, or delete. Be mindful that when you delete an embed code, any users using it will lose access to it and it can’t be restored. Why we are making this change For new organizations just starting out with Power BI, and for existing ones relying on it more than ever, we want to provide a default experience that empowers admins to choose how our public report functionality is used in their organization. At the same time, we want to ensure that existing embed codes continue to work as we make the change to our defaults. Organizations can already prevent creation of publish to web embed codes, choose which users to trust to use the feature correctly, block certain groups of users from publishing reports publicly, and review and manage existing embed codes. The publish to web setting and the embed codes list available in the Power BI admin portal enable Power BI admins to perform these tasks. How to prepare for this change As a Power BI admin, familiarize yourself with the existing publish to web controls, and review existing publish to web embed codes. These are described in more details in other parts of this blog post. As a Power BI user, you’ll need a Power BI admin to take action to ensure you can keep publishing reports to the web. If you plan to create a new embed code following the roll-out date, coordinate with your Power BI admin so you can create your embed code in a timely manner. If you’re not sure who is your Power BI admin, ask your internal IT team or internal support organization. There are better options available for sharing reports internally Power BI offers several capabilities that ensure authentication and permissions are enforced before displaying content to users. You can share a report or dashboard with a user or group of users. This sends them an email with the link to the item you shared and gives the specific permission you choose. This is the preferred way for business users to share items, as it also appears in Shared with me and the Power BI mobile apps, making it easy for users to access and later find the items you sent them. If you are sharing with external users, Power BI integrates with Azure Active Directory B2B to ensure the sharing is more secure and governable. The embed in SharePoint Online option allows you to build modern pages with your content using the Power BI web part for SharePoint Online. Our embed in website or portal capability makes it easy to add your report to your internal web pages. It handles the authentication and authorization so that only those users who should see the data can do so. It’s as easy-to-use as publish to web. If you’re a developer looking to integrate Power BI into a custom built application, you can use Power BI's rich JavaScript and REST APIs to embed your content. A summary of the methods to share content with others is available in our documentation. What best practices should my organization adopt when using Publish to web As a Power BI admin, it is important to put in place a review process for embed codes. The embed codes list in the Power BI admin portal allows you to review and, if needed, delete any embed codes as needed. One challenge users may face is knowing how to contact their Power BI admin. We suggest you publish "Get Help" information using the options available in the Power BI admin portal. When the option is set, the Get Help link available under the "?" icon will direct users to a support page you provide. On that page, you can inform users how they can request to use the Publish to web feature. It’s also important to train your users how and when to use Publish to web. Helping your users understand what kinds of data are sensitive, confidential, and to recognize personally identifiable information that should not be shared publicly will help your organization work more effectively with data. Next steps We know this change will impact some users. We hope that by sharing this information early, you’ll be able to prepare for the change. As always, we’re keen to hear your feedback in the comments below and new feature suggestions at https://ideas.powerbi.com. Updated on 1/27/2020 to reflect updated release dates. Previous to this edit, the blog said: "We expect the change to reach commercial cloud tenants the week of 1/6/2020 and government cloud tenants the week of 1/13/2020." The updated dates are more accurate.Roadmap update: What's next for the new workspace experience upgrade in the coming months
The new workspace experience is the future of workspaces in Power BI. Already it's the default for new workspaces created in the service. Today, we wanted to announce our roadmap for workspace upgrade so you can plan ahead. Let's recap key milestones in the new workspace experience journey: The new workspace experience reached GA in April 2019 Opt-in upgrade for workspace admins entered Public Preview in November 2019 Now, let’s look at what’s in the roadmap for workspace upgrade: Workspace upgrade general availability is coming soon Allowing Power BI Admins to block creation of classic workspace Allowing Power BI Admins to hide empty classic workspaces Allowing Power BI Admins to upgrade classic workspaces Deprecation of classic workspace creation in October 2020 Tools for bulk upgrade of classic workspace for Power BI Admins Automatic upgrade of classic workspaces Workspace upgrade general availability is coming soon In the summer of 2020, opt-in workspace upgrade for workspace admins will reach general availability (GA). A banner at the top of the content list for classic workspaces will be shown to workspace admins, encouraging them to upgrade. This will still be an opt-in experience with no requirement to upgrade the workspace. The banner will continue to be shown until the workspace is upgraded. Allowing Power BI Admins to block creation of classic workspaces In the summer of 2020, we will introduce a Power BI tenant setting that allows Power BI admins to block creation of classic workspaces. When enabled for your organization, users won't be able to create classic workspaces in the Power BI service or through API. Any newly created Office 365 Groups won't create classic workspaces. Existing classic workspaces are not affected by this setting and will continue to work as they do today. When we add the block classic workspace creation setting, the default will be Disabled as shown in the screenshot. The Power BI Admin can opt-in to block classic workspace creation for their organization. Later in this blog we'll describe how and when we plan to starting to block classic workspace creation by default. Allowing Power BI Admins to hide empty classic workspaces Many classic workspaces that are empty because they were created automatically when an Office 365 group was created. We will introduce a new Power BI tenant setting allowing Power BI Admins to hide these empty classic workspaces and to delete them. We expect this capability to release by October 2020. Allowing Power BI Admins to upgrade classic workspaces We will introduce a new option in the Power BI admin portal to upgrade classic workspaces. The Power BI admin will use the admin portal workspaces list to pick a workspace and upgrade it. We expect this capability to release by October 2020. Additionally, we're evaluating the if we can provide an API for Power BI admins to upgrade workspaces and will update at a later time our roadmap for that capability. This Power BI admin opt-in upgrade will have the same impacts as workspace admin-initiated opt-in upgrade. Notably, content packs will be removed as part of the upgrade. Workspace admins will be notified that their workspace was upgraded by the Power BI admin. Importantly, Power BI admins should take steps to educate workspace admins about any planned upgrades. It’s important that workspace admins follow the steps to take after upgrade. Deprecation of classic workspace creation in October 2020 On October 1st, 2020, Power BI will start blocking the creation of classic workspaces for most tenants. For tenants in national clouds the same change will roll-out a few weeks later. Existing classic workspaces will continue to work without disruption. When this rolls out, new classic workspaces will not be created for newly created Office 365 groups. Effectively this will have the same behavior as if the Power BI admin disabled classic workspace creation through the tenant setting. Tools for bulk upgrade of classic workspace for Power BI admins Early in 2021, we plan to introduce tools for Power BI admins to bulk upgrade classic workspaces. These tools will enable Power BI admins to plan and control when and how quickly to upgrade all their classic workspaces. Automatic upgrade of classic workspaces We expect that in mid 2021 we will start to automatically upgrade all remaining classic workspaces to the new workspace experience. We won’t start the process until we believe the bulk-upgrade experiences provided to Power BI admins are reliable and we can address any feedback we receive from customers. How to prepare for these important changes Organizations should learn about the benefits and differences of the new workspace experience. We encouraged Power BI admins to work with workspace admins to upgrade classic workspaces. Content packs have been deprecated for several years and they will be removed through the workspace upgrade process. Now’s the time to move to Power BI apps. The options being introduced in the Power BI admin portal allow organizations to control when these changes occur for their tenants. We highly encourage Power BI admins to plan and communicate the changes to their organizations. This helps ensure there are no surprises when the Power BI service starts enforcing the changed behaviors described in this blog post. Stay tuned for more announcements on this blog. We’ll provide more updates here so you’re informed and can plan ahead. As always, we really appreciate your feedback and we know these changes can be significant for many organizations. We appreciate any comments on his blog or through our community. Feel welcome to contact your Microsoft account representative with questions as well. Next steps: Start creating new workspaces Learn about the new workspace experience Read our announcement of the new workspace experience GA Read our announcement of classic workspace upgrade public preview
Report Builder Connection to OLE DB
Hello, I am able to connect to OLE DB on Power BI Desktop, however when trying to use Report Builder (OLE DB Data Sources are defined on the on-premise report server), I am receiving the following error The 'OraOLEDB.Oracle' provider is not registered on the local machine. I have tried to also install ODAC 32bit for Report builder but as my Windows version is 64bit, it did not work. Any ideas?How to create Start Time and End Time Slicers for single Datetime column
Hello Team, We have one Table. This is the below columns, Date-Time , value and Legends. How to create different Start Time (Hours, Minutes ) and End Time (Hours, Minutes) Slicers . Please find the below screenshot for reference
Troubleshooting automatic aggregations to optimize DirectQuery performance
With the click of a mouse button, dataset creators of any skill level can improve the query performance of their DirectQuery datasets! In the Power BI portal, display the settings for a DirectQuery dataset, expand the Scheduled refresh and performance optimization section, and toggle the Automatic aggregations training option to On, as in the following screenshot, and don’t forget to configure a data refresh schedule to update your aggregations on a regular basis. That's basically all it takes. For more information about how automatic aggregations can help to improve the performance of your report visualizations, see the Automatic Aggregations Overview in the product documentation. Sometimes, however, when you enable automatic aggregations (auto aggs), you end up with no aggregations and no corresponding performance gains. In these troubled situations, it can be helpful to have more control over the auto aggs process than the user interface provides. Granular control lets you fine-tune auto aggs. You can exclude specific tables by setting a minimum size limit below which you don’t want to generate auto aggs. Or maybe you want to test auto-aggs training with different configuration settings and verify that Power BI indeed added auto aggs to your dataset. The Tabular Object Model (TOM) and the Tabular Model Scripting Language (TMSL) give you the advanced options to cover these scenarios, as explained in this article. If you are a BI developer and want to follow the .NET Core console application used in this article in your own environment, refer to the attached TOM sample code [attachment=1] for details. Make sure you use the latest Analysis Services client libraries and host your dataset on a Premium or Fabric capacity with XMLA Read/Write enabled. If you prefer to work with TMSL instead, keep your SQL Server Management Studio (SSMS) installation at the latest version. And if you are not a BI developer, you still might find the following troubleshooting guidance useful. So, let's quickly review how automatic aggregations work before diving deeper into various scenarios and code snippets. For auto aggs to work, you must accomplish the following three essential tasks: Generate DAX queries. You need to use reports or other means to query your dataset because the process of generating automatic aggregations relies on a query log that tracks the queries sent to the dataset over seven days. As the diagram below illustrates, the query log serves as input for auto aggs training. So, as a prerequisite to generating auto aggs, make sure you seed the query log! Perform automatic aggregations training. Auto-aggs training is the process that generates the system-managed aggs tables in the dataset. This process analyzes the query log as well as the cardinality and other characteristics of the source table columns. This process typically runs at dataset refresh time during the first refresh of the day, but you can also trigger auto-aggs training manually and programmatically. Run a data refresh. Keep in mind that auto-aggs training only generates the aggs tables and adds them to the dataset, but it does not fill these tables with data. Loading the aggs data is the job of data refresh, so do not forget to refresh your dataset and configure scheduled refresh to keep the aggs data updated. Only then will you see a query perf improvement for eligible DirectQuery tables. With the basics covered, let’s now dive into a common troubleshooting scenario: You enabled auto aggs, but you don’t notice any perf gains. In this situation, a good first step is to check that you actually have aggs tables in your dataset. Perhaps Power BI didn’t generate any because there just wasn’t any meaningful input data in the query log, or perhaps the dataset hasn’t been refreshed yet so training never ran, or perhaps training didn’t finish within the time limit of 1 hour, which means training hasn’t completed yet and will resume the next day. Auto aggs training can take a long time depending on how fast the data source can process the cardinality queries and other queries that Power BI generates during the training phase. Whatever the reason, if there aren’t any aggs tables in the dataset, your DAX queries can’t leverage aggregations, of course. Aggs tables are relatively easy to spot in SSMS. Connect to your workspace, expand your DirectQuery dataset, and look for tables with GUIDs as their names. The following screenshot shows one such aggs table named b83b1a0c-5712-4f35-8d73-d1db0a0c6c33. After scripting out this table, you can see that it is a hidden system-managed table with an inferred partition source. In managed code using TOM, it’s equally uncomplicated to check for aggs tables. See the following code snippet. As mentioned earlier, the TOM sample code [attachment=2] is attached to this article. /// Check if there are any auto aggs tables, /// i.e. system-managed tables with an inferred partition source. var model = database.Model; if (model.Tables.Where(t => t.SystemManaged && t.Partitions.All(x => x.SourceType == PartitionSourceType.Inferred)).Any()) { Console.WriteLine("This dataset has auto aggs tables!"); } else { Console.WriteLine("This dataset does not have any auto aggs tables!"); } Let’s continue our troubleshooting journey and assume the dataset has no auto-aggs tables yet. A logical next question could be if auto aggs are enabled at all. Now, you might be tempted to look for auto-aggs settings in the dataset. For example, you could check the AutomaticAggregationOptions property of the Model object. Yet, note that Power BI does not store its auto-aggs settings in the model metadata. The following screenshot illustrates this aspect. The dataset owner enabled auto aggs in Power BI, but there are no AutomaticAggregationOptions in the model. The auto-aggs settings that Power BI uses are only available on the dataset settings page. So, what’s the purpose of the AutomaticAggregationOptions property if Power BI doesn’t use it? Its purpose is to store a default auto-aggs configuration that you can apply programmatically to auto-aggs training by using the ApplyAutomaticAggregations method. Power BI doesn’t use this property, but custom solutions can. Instead of using a default auto-aggs configuration, you can also provide an explicit AutomaticAggregationOptions object as an input parameter to the ApplyAutomaticAggregations method. This is the method Power BI uses to submit its own configuration settings. And this is the method that lets you easily and quickly trigger auto-aggs training with different configuration settings as well. The following snippet code illustrates this approach. Refer to the AutomaticAggregationOptions Class in the API documentation for details about supported properties, such as QueryCoverage, DetailTableMinRows, and AggregationTableSizeLimit. /// Perform auto aggs training using a one-off configuration. /// model.ApplyAutomaticAggregations( new AutomaticAggregationOptions { QueryCoverage = 0.5 }); database.Update(Microsoft.AnalysisServices.UpdateOptions.ExpandFull); Calling the above ApplyAutomaticAggregations method is all it takes to trigger auto-aggs training. Perhaps training never ran before. Yet, don’t forget the database Update call to persist any generated aggs tables in the dataset. Still, even a successful completion of the ApplyAutomaticAggregations method is no guarantee that auto-aggs training indeed generated aggs tables. As mentioned several times already, the query log might be empty, so auto-aggs training only “successfully” finds out that there are no aggs to generate. Or auto-aggs training took longer than 1 hour. In this case, you can perform training repeatedly until it finishes. Training is incremental and resumes where the previous cycle left off. Just train, train, train, and when aggs training is finally done, perform a single data refresh operation. However, how do you decide that aggs training is finished? For this, you must capture the training status raised in an AutoAggsTraining - Progress Report End trace event. This is perhaps most easily accomplished by using SQL Profiler. Make sure you include at least the Progress Report End event class in the trace. The following trace includes an AutoAggsTraining - Progress Report End event with a few important pieces of information: aggregationTableCount is 0, so no aggs tables were generated. queryShapes.eligible is 0, which means in this particular case that auto-aggs training did not find any useful queries in the query log. queryShapes.discarded is empty because the query log was empty to begin with, but if there were discarded query shapes, the discarded property would include one or more of the following counters: Counter Comments DroppedByUser Query shapes dropped because the queried table(s) no longer exist in the dataset. ImportMode Query shapes dropped because the queried table(s) are in import mode, which auto aggs do not cover. LimitedRelationship Query shapes dropped because of a limited relationship between the queried table(s). RelationshipMismatch Query shapes dropped because of a relationship mismatch between the queried table(s). CalculatedColumn Query shapes dropped because the queried table(s) have calculated columns, which is not supported. DualRootTable Query shapes dropped because the queried table(s) are in dual mode, which auto aggs do not cover. TableExcludedByUser Query shapes dropped because the queried table(s) are explicitly excluded by the user. UnsupportedMDataSource Query shapes dropped because the queried table(s) use unsupported M data sources. UnsupportedNativeDataSource Query shapes dropped because the queried table(s) use unsupported native data source. SSOEnabled Query shapes dropped because the queried table(s) use an SSO-enabled data source, which is not supported. CardinalityEstimationFailure Query shapes dropped because the cardinality estimation of the queried table(s) failed. LargeSizeAggs Query shapes dropped because the aggregation size of the queried table(s) is too large. TableTooSmall Query shapes dropped because the queried table(s) are too small to create aggregations. LeftUncovered Query shapes dropped because the queried table(s) are left uncovered because of user-provided training targets. Unknown Query shapes dropped because of unknown failures. Of course, you can also create a trace programmatically, as the attached TOM sample code [attachment=3] demonstrates and the following screenshot illustrates. This time, 77 eligible query shapes existed. Power BI reports had queried the dataset. And aggregationTableCount is 1, so auto-aggs training was able to create one aggs table for this dataset. Success! Congratulations! At this point, we have mastered the first two essential auto aggs tasks. We generated DAX queries and performed auto-aggs training, which added a system-managed aggs table to the dataset. Yet, the aggs table does not yet contain any data, as the DAX query result in the following screenshot reveals. To load the data, we must run data refresh using any of the available options (manual, scheduled, or programmatic). The next screenshot shows this article’s console app performing a programmatic refresh through the following TOM code. The sample code then submits the above DAX query for all system-managed aggs tables to count their rows. In production datasets, there would likely be many aggs tables and any aggs table with actual data can help to improve the performance of your DirectQuery dataset. /// Refresh the model to import data into the aggs tables. /// model.RequestRefresh(RefreshType.Full); model.SaveChanges(); leshooting_automatic_aggregations_to_optimize_DirectQuery_performance And this concludes this little excursion into auto-aggs processing details. We hope the explanations provide you with sufficient details to master the most common auto-aggs troubleshooting situations. The sample code might also serve as a starting point for a custom solution to add fine-tuned auto aggs to your enterprise BI DirectQuery models. Don’t hesitate to download the attached TOM sample code [attachment=4] and try out different AutomaticAggregationOptions settings. For example, you could set the AggregationTableMaxRows parameter or the DetailTableMinRows parameter and see for yourself how different limits affect the generation of your aggs tables. And as always, please provide us with feedback as you leverage automatic aggregations to boost the performance of your DirectQuery datasets. We would love to hear from you!
Sort by ASC when value is present
Hello, I'm trying to sort a column, by another column when it's present. The picture shows Categories, in bold, Names as unbolded text, and beneath the names not visible in the picture is a GL number for each name. This GL number is only present in names under the category SG&A. I'm trying to sort these values by ascending them in my matrix. Any ideas?Announcing backup and restore improvements for large datasets near the size limit
By Wayne Pai (Senior Software Engineer, Microsoft) In August 2021, we announced general availability (GA) of backup and restore for large datasets. This Power BI feature closes an important gap to Azure AS. You can backup Power BI datasets on a regular basis to meet the data retention and disaster recovery requirements of your organization. You can also use this feature to migrate enterprise BI workloads from Azure AS to Power BI. Another common scenario is to roll back an existing Power BI dataset to a previous version. Yet, rolling back was not without challenges for large datasets near the max dataset size. But thanks to the latest improvements, you can now even restore a backup file when the dataset size is near the SKU limitation. You no longer need to be concerned that size limits impact restorability. For example, a P3 capacity has a max dataset size of 100 GB. If you wanted to maximize memory usage, you might have intended to host a dataset with 70 GB on this capacity. However, prior to the recent backup and restore improvements, you would find it challenging to accomplish this goal. While you can minimize the memory requirements for refresh operations through advanced refresh techniques, dataset restore operations would still require at least 50% of the available memory, effectively limiting the max dataset size to less than 50 GB. You could host a 70 GB on a P3 capacity. You could backup the dataset regularly to meet the data retention requirements of your organization. But you could not rollback the dataset to a previous version by using restore because there was not enough memory available to load the backup file. Restore would fail because it exceeded the max available memory per dataset on a P3 capacity: 70 GB for the originally loaded database + 70 GB to restore the backup file > 100 GB for the P3 max dataset size). Fortunately, thanks to the recent backup and restore improvements, you can now use a new /forceRestore option with the restore command to overcome this limitation. The following screenshot shows a corresponding restore command. When restoring a dataset with the /forceRestore option, Power BI will try its best to perform the restore operation even when the available memory is limited. Power BI might unload the original database, temporarily disconnecting the users, but the restore operation will succeed. If you forget to include the /forceRestore option when attempting to restore a dataset near the size limit, you might get the following error message to remind you that the /forceRestore option is required in this case: "We cannot restore the dataset backup right now because there is not enough memory to complete this operation. Please use the /forceRestore option to restore the dataset with the existing dataset unloaded and offline." Note also that the /forceRestore option is only available when restoring a dataset in Power BI Premium. It is not available with SQL Server Analysis Services or Azure AS. So, the scenario described above would fail on an S4 Azure AS server, while it can succeed on Power BI Premium. If you still have datasets on Azure AS, don’t wait and migrate these datasets to Power BI Premium now! And even if you do not need to migrate Azure AS datasets, we hope that the recent backup and restore improvements enable you to increase the sizes of your Power BI datasets where necessary and maximize in this way your return on investments in Power BI Premium.
