With Excel 2016, you can publish your Excel workbooks right to your Power BI site, where you can create highly interactive reports and dashboards based on your workbook’s data. You can then share your insights with others in your organization. Requirements: 1. Before publishing to Power BI, workbook must be saved to OneDrive for Business. 2. Only Excel 2016 with an Office 365 subscription will see the experience to publish with local files. Excel 2016 standalone installation will still have the “Publish” only behaviour which requires the excel workbook be saved to OneDrive for Business or SharePoint Online. 3. The account should be same for Office, OneDrive for Business, and Power BI. 4. Empty workbook or a workbook that doesn’t have any Power BI supported content cannot be published. 5. Encrypted or password protected workbooks, or workbooks with Information Protection Management cannot be published. Steps: In Excel, select File > Publish (Local file publishing). When you select Publish, you will be able to select the workspace you want to publish to. This can be your personal or group workspace that you have access to. You’ll get two options on how to get your workbook into Power BI. Upload your workbook to Power BI: When you choose this option, your workbook will appear in Power BI just like it would in Excel Online. But, unlike Excel Online, you’ll have some great features to help you pin elements from your worksheets to dashboards. You can’t edit your workbook in when open in Power BI, but if you need to make some changes, you can select Edit, and then choose to edit your workbook in Excel Online or open it in Excel on your computer. Any changes you make are saved to the workbook on OneDrive. When you upload, no dataset is created in Power BI. Your workbook will appear in Reports, in your workspace navigation pane. Workbooks uploaded to Power BI have a special Excel icon, identifying them as Excel workbooks that have been uploaded. Choose this option if you only have data in worksheets, or you have PivotTables and Charts you want to see in Power BI. Using Upload from Publish to Power BI in Excel is pretty much the same as using Get Data > File > OneDrive for Business > Connect, Manage and View Excel in Power BI from Power BI in your browser. Export workbook data to Power BI: When you choose this option, any supported data in tables and/or a data model are exported into a new dataset in Power BI. If you have any Power View sheets, those will be re-created in Power BI as reports. You can continue editing your workbook. When your changes are saved, they’ll be synchronized with the dataset in Power BI, usually within about an hour. If you need more immediate gratification, you can just select Publish again, and your changes are exported right then and there. Any visualizations you have in reports and dashboards will be updated, too. Choose this option if you’ve used Get & Transform data or Power Pivot to load data into a data model, or if your workbook has Power View sheets with visualizations you want to see in Power BI. Using Export from Publish to Power BI in Excel is pretty much the same as using Get Data > File > OneDrive for Business > Export Excel data into Power BI from Power BI in your browser. Publishing: When you choose either option, Excel will sign in to Power BI with your current account, and then publish your workbook to your Power BI site. Keep an eye on the status bar in Excel. It shows how things are going. To keep the data live, save your workbook to OneDrive and create a ODataFeed connection. Visit this blog article for more information. Let us know if there is any issue while implementing or contact us.

Introduction: In this blog article, we will see how to resolve timeout error in D365 Operations. Refer below screenshot for error. This error occurs when a report is processing many records and takes too long to execute. A report processing times out in 10 minutes. Steps: To resolve the error, follow below steps, Go to Tmp Table and change its Table Type property to TempDB. Go to RDP class and extend the class from SrsReportDataProviderPreProcessTempDB Save, Build and Deploy the Report.

Overview: Welcome to this 2-part blog series on Data Export Service in Dynamics 365. This is an Add-on service made available as a Microsoft Dynamics 365 Online solution that adds ability to replicate D365 Online data to Azure SQL datastore in a customer based Azure subscription. Supported Target Destinations – Microsoft Azure SQL Database Microsoft Azure SQL Server on MS Azure Virtual Machine Data export initially synchronizes schema and data and thereafter, delta changes as they occur. Prerequisites: Your Dynamics 365 Online instance must be December 2016 update or higher Entities should be enabled for Change Tracking. Code is run in the context of a user with Sys Admin role. You’ll need to link your Office 365 to the Azure Subscription i.e. add the Office 365 tenant in the Active Directories of the Azure Subscription Azure SQL Database and user with correct permissions to be setup Install Data Export Service from the App Source from within your Dynamics 365 Dynamics 365: You can get this in the App Source of the Dynamics 365 and add it to your organization. On selecting the same, proceed with the Wizard by accepting terms and conditions And it will setup in the background Once completed, it will appear in the Settings area in Dynamics 365. Settings > Data Export 4. One successfully authenticating with Azure, you’ll see this disclaimer to which you have to click OK to proceed. Setup Azure SQL Database: To be able to run the test successfully, you’ll need to setup SQL Database on your Azure Customer Subscription. 1. I have the following SQL Server created in my Azure account. 2. And the following database created under it. To be able to do #3 below, you must do the following: The subscription must support the volume of data being replicated from your Dynamics 365 instance. Configure an Azure SQL Database server-level firewall rule using Azure Portal. Recommended to Allow access to azure services to be enabled. 3. And finally, connect my SQL to the database hosted on my Azure. Create Access Permissions for Users in SQL Open the Master Database and create a user for the user ‘dataexport1’ for the database. This user is then used in the Dynamics 365 Data Export Service to connect to the database Once done, use the below script to create the user in the created Azure SQL database We gave db_owner access to the user to provide full permissions. Link your Office 365 tenant to your Azure AD (if required) If your Office365 and Azure accounts are different, you can add the Office 365 in your Azure by doing the following: Navigate to Azure portal and then select Active Directory Then, click on + New from the bottom and chose Use existing directory as option and proceed. You’ll be logged out and asked to login again using the Office 365 credentials you want to add. Once signed in, the Office 365 tenant will be linked to your Azure Subscription and seen as below in the Active Directory area Now, what we’ve accomplished so far is – Adding Data Export Service to your D365 instance Setting up Azure SQL and connecting the same from your SQL Adding your O365 tenant to the Azure Subscription (option) In the next part, we will see – Creating Key Vault in Azure to store the connection strings to the Azure SQL Creating a Data Export Profile Testing out the functionality. Key benefits of using Data Export Service. Part 2 of this blog series will be out early next week.  

Yes, you heard it right, you can import Microsoft Excel Workbook to Power BI and show you selected section in you powered dashboards, let’s see how it works, as Microsoft Excel is one of the most widely used business applications around. It’s also one of the most common ways to get your data into Power BI. Requirement: Power BI supports importing or connecting to workbooks created in Excel 2007 and later. Workbooks must be saved as .xlsx or .xlsm file type and be under 1 GB. Raw Data Vs Range Tables: If your workbook has simple worksheets with ranges of data, to get the most out of your data in Power BI, be sure to format those ranges as tables. This way, when creating reports in Power BI, you’ll see named tables and columns in the Fields pane, making it much easier to visualize your data. Power View Sheets, Pivottables And Charts: How your PowerView sheets and PivotTables and charts appear, or not appear, in Power BI depends on where your workbook file is saved and how you choose to get it into Power BI. We’ll go into this more below. Data Types: Power BI supports the following data types: Whole Number, Decimal Number, Currency, Date, True/False, Text. Marking data as specific data types in Excel will improve the Power BI experience. Saving you Excel file local or OneDrive can make a difference: Local – If you save your workbook file to a local drive on your computer or another location in your organization, from Power BI you can load your file into Power BI. Your file will remain on your local drive, so the whole file isn’t really imported into Power BI. What really happens is a new dataset is created in Power BI and data and the data model (if any) from the workbook are loaded into the dataset. If your workbook has any Power View sheets, those will appear in your Power BI site under Reports. Excel 2016 also has the Publish feature (under the File menu) which is discussed in our other blog. OneDrive – Business – If you have OneDrive for Business and you sign into it with the same account you sign into Power BI with, this is by-far the most effective way to keep your work in Excel and your dataset, reports, and dashboards in Power BI in-sync. Because both Power BI and OneDrive are in the cloud, Power BI connects to your workbook file on OneDrive about every hour. If any changes are found, your dataset, reports, and dashboards are automatically updated in Power BI. Two Ways to Import Excel workbook: 1. OneDrive 2. Import Excel data into Power BI Import or connect to an Excel workbook from Power BI 1. In Power BI, in the navigation pane, click Get Data. 2. In Files, click Get. 3. Find your file. 4. If your workbook file is on OneDrive or SharePoint – Team Sites, choose Import or Connect. Open the workbook and select he section that you want to pin to Dashboards. Try this yourself, it’s an amazing feature and allows us to use the standalone data points to show in Dashboards when even required. Feel free to contact us in case of any issue.  

Introduction: What is Workflow User group? Workflow user group is a group of users under one group title in Workflows with the users’ number in a process sequence such as an approver chain. To define an approval request to be not approved until multiple approvers in a approval chain have approved it, set up approvers with incremental sequence number.Here the approval request is sent to the user with highest sequence number i.e. 1. This user will have to approve the request then an approval will be sent to the next user with sequence number 2 and so on. To define approval request is not approved until multiple equal approvers have approved it regardless of the hierarchy assign same sequence number to the users.Here the approval request is sent to all users in the group at the same time. Here, both the approval requester and approver must be set up in the Approval User Setup. This blog provides a step wise procedure how to create approval workflow using workflow user group. Pre-requisites: Microsoft Dynamics NAV 2017 Steps: I’ll explain workflow user group using a scenario. Scenario:  A sales department has a group of two sales managers. An approval request must be sent to the sales managers if the sales order amount is greater than 4000 and even if one of the sales manager approves the request, the document should be released. 1. Create a workflow user group Navigate to Workflow User group in the search bar in Dynamics NAV and click on New. Enter the code and description for it. Insert the approver users with sequence number as 1 2. Create a new Sales Order Workflow Open workflow using the path CRONUS International Ltd./Departments/Administration/Application Setup/Workflow/Workflows Click on New Workflow from Template button from the ribbon. click on the Sales Documents category and select Sales order workflow Here the Sales Order Approval Workflow opens. Click on the first event condition and add a condition on amount > 4000 The response of the first event sets the Sales order status to Pending approval, creates and sends the approval request to the approvers. 3. Set the Approver Type to Workflow User group  We are using the approval type as workflow user group hence, we need to specify this group in the workflow. Now click on the assist edit button of the first response and click on the third response line. By default the approver type is Salesperson/Purchaser Select the Workflow User group from the Approver Type drop down and select the workflow user group code. Now the approval request will be directly sent to the Workflow User group with code SALES MANAGER 4. Set the sales order workflow condition Click on the second event ‘An approval request is approved’. Here we observe that the condition reads Pending approval :0 which means that when all the approver users approve the request i.e. when the the count of the pending approval is 0 then according to the response, release the document. Now we want even if one of the approver users approves the request, the sales order document must be released. we’ll have to change the condition to Pending approval :1 Here when the approval request is sent to both the sales managers, the pending approval count is 2. Now when one of the sales manager approves the sales document the count decreases to pending approval:1hence when the one of the sales manager approves the sales order document the sales order is Released Finally enable the workflow 5. Send an sales order approval request Navigate to sales order and create a sales order greater than 4000 and click on send approval request. Observer the status of the sales document changes to Pending Approval. Login Microsoft Dynamics NAV with the sales manager user credentials and navigate to ‘Request to Approve’ To view the sales order click on Open Record To approve the order click on Approve. This will Release the sales order document. To reject the order click on Reject. This will change the status to Open. Comments can be added by clicking on comments. The sales manager approves it and the document is approved thus the status changes to Released Conclusion: To summarize everything, create a sales order workflow from the template. Create a workflow user group and assign sequence number to 1. Set the condition of amount > 4000 and Pending approval to 1. Select approver type as Workflow user group and select the workflow user group code. Finally enable the workflow.

Introductions: This blog highlights the feature for Drillthrough in Power BI Services, this is the ability to define a report page as a drillthrough page. You can now define an individual report page as a Drillthrough page. Ideally these report pages should be configured across a single entity. With that focused report page, users can right-click on a data point in other report pages, and drillthrough to the focused page to get details that are filtered to that context. Report Example: Below example uses the NORTHWIND database as a data Source, you can download sample database from here. To use drillthrough, create a report page that has visuals you’d like to see about the type of entity for which you’ll provide drillthrough. Your entity Relation should exist while you provide the drillthrough. Entity Relation Diagram: To use the drillthrough functionality right click on the any model and then you can go to the next related page, and data will be filtered according to that data point. How to create Drillthough in Power BI Reports: Select the entity that is used in the report, place the column that will be used from the drillthrough and at the page level drop the column in Drillthrough section below, this needs to be added to child page. When this is added in child page, there will be a back button that will be enabled in the child page. This button helps to navigate back to the parent page or to Drill Up. When this setup is complete you can use the drill up functionality in the parent page when you right click on any visual, option will be enabled. You can try this by yourself on the below power BI Report: Parent Page: Product Child Page: Orders, Region, Customer Feel free to leave comments if there are any issue while implementing the same, or feel free to contact us.

Introduction: Encryption is the process by which information is encoded so that only an authorized recipient can decode and consume the information. Steps: The process to setup and enable Office 365 Message Encryption is easy. There are three main steps that need to be followed: Activate Azure Rights Management. Setup Azure Rights Management for Exchange Online. Setup transport rules to enforce message encryption in Exchange Online. Step 1: Activate Azure Rights Management for O365 Message Encryption. Sign in to Office 365. In O365 Admin Center, go to Settings > Services & Add-ins and select Microsoft Azure Information Protection. Click on Manage Microsoft Azure Information Protection settings and you will be redirected rights management Activate the Rights Management. Step 2: Set up Azure Rights Management for O365 Message Encryption. In this step we will use PowerShell to connect to Exchange Online, Open PowerShell as Administrator and enter the following commands to connect and import the session Set-ExecutionPolicy RemoteSigned $cred = Get-Credential $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $cred -Authentication Basic –AllowRedirection Import-PSSession $Session Verify your IRM is not already configured Get-IRMConfiguration Configure RMS with the online key-sharing locationfor Exchange Online with PowerShell (locations below). Set-IRMConfiguration -RMSOnlineKeySharingLocation https://sprms.ap.aadrm.com/TenantManagement/ServicePartner.svc  Location RMS key sharing location North America https://sp-rms.na.aadrm.com/TenantManagement/ServicePartner.svc European Union https://sp-rms.eu.aadrm.com/TenantManagement/ServicePartner.svc Asia https://sp-rms.ap.aadrm.com/TenantManagement/ServicePartner.svc South America https://sp-rms.sa.aadrm.com/TenantManagement/ServicePartner.svc Office 365 for Government https://sp-rms.govus.aadrm.com/TenantManagement/ServicePartner.svc Import the Trusted Publishing Domain(TPD) from RMS Online Import-RMSTrustedPublishingDomain -RMSOnline -name “RMS Online” Verify successful setup of IRM in Exchange Online. Test-IRMConfiguration –sender admin@domain.com Disable IRM templates in OWA and Outlook.  Set-IRMConfiguration -ClientAccessServerEnabled $false Enable IRM for Office 365 Message Encryption. Set-IRMConfiguration -InternalLicensingEnabled $true Viewthe IRM Configuration. Get-IRMConfiguration Step 3: Create transport rule to encrypt message. In Office 365 Admin Center, go to Exchange Online Admin Center. Go to Mail Flow > Rules. Click the + and create your transport rule. This rule will encrypt anything that is sent external. Make sure the rules are active. Testing that the transport rule applies Office 365 Message Encryption. Conclusion: Its easy to encrypt your mail and secure it in Office 365.

Introduction: You can import XML as a data source, using Scribe Insight, to execute two primary actions: Integrate with locally available XML data. Receive data as XML from remote systems. Steps: Open Scribe Workbench and click on ‘Configure Source’. Click on ‘New’ and select XML. The XML Component Connection Wizard will open. Click Next. Select ‘Use as a Source’. Click Next. Select ‘Dynamic’ and click Next. In the ‘Sample Source Document File’ field, input the XML File (.xml) path and in the ‘Schema File or URL’ field, input the path of the Schema file (.xsd). Click Finish and give a name to the XML source. Click OK. Select ‘XML Objects’ and select the entity. Click OK. It will now be configured as the Source. Now you may proceed with mapping the fields required for integration with XML as the Source.

Introduction: Power BI comes with a powerful language recognition engine that lets you ask questions of your data using conversational phrases and questions. Based on these questions, Power BI dynamically creates charts and graphs. For example, if the data is defined as a date type, it is more likely to be displayed as a line chart. Data that is categorized as a city is more likely to be displayed as a map. Questions can be asked on a dashboard. The feature is called as Q&A or Questions & Answers. Natural Language Q&A is really an underused tool in Power BI despite of being really powerful. Following is my dashboard on a Credit Card transaction dataset. I will be performing simple Q&A questions on this dashboard. Pre-Requisites: Power BI Subscription Features of Q&A: Auto Prompts: This prompts are created on the basis of: a. the questions used to create tiles that are already pinned to the dashboard, and b. the name of tables in the underlying dataset(s). Can build a question using the prompts (Eg. What is the total amount in April) Dropdown pops up while typing a question. Helps with auto-replacement terms as well. Can use Aliasing table to make querying more powerful. Featured Q&A Questions Click on your Dashboards Ellipses and then select Settings. Click on the Datasets tab and select Featured Q&A questions. Select Add a question and type a question and then click on Apply. Now whenever a user starts typing on Q&A they will be prompted with this question first. Dims words it does not understand. Can combine results from more than one data set. When you type a query, Power BI looks for an answer in any dataset that has a tile on that dashboard.  If all the tiles are from datasetA, then your answer will come from datasetA.  If there are tiles from datasetAand datasetB, then Q&A will search for the best answer from those 2 datasets. Dynamically generates a visual depending on the question. Can change the visual type using the ‘as’ keyword. Can pin this new visualization back to your dashboard. The visual answers can also be edited. Just by using the Visualizations and the Filter panes on the right side of the screen we can alter the layout, adjust filters and change fields. Conclusion: We have discussed the various features that can be used with Q&A which can improve our ability in analyzing data.  

Introduction: In this Blog article, we explain about the new feature of the power BI: Bookmarking. Bookmarking: Bookmarking is the a very powerful way to save and share out your Reports and Dashboards with other people. Bookmarking lets you save your interesting states as part of your report. You can create list of bookmarks. Using this Bookmarking you can create report navigation and many more. You can add a bookmark to your report for: The current page Drill location Filters Slicers Visibility ‘Focus’ Mode Once you turn on the preview feature, you can add bookmarks by opening the bookmarks pane under the view tab. Set up your report, with all the filters properly set, and click the Add button. Once you hit the add button the it will add a bookmark to pane. You can rename this bookmark and also you can update or delete the bookmark. You can easily access the save states. You can add many bookmarks as you want. Also You can used your Bookmarks as a story by clicking on the View option. By clicking on the view you will enter into the view mode of the bookmark. In the view mode, there is a title bar for each bookmark that includes the bookmark name and navigation arrows. At this point, you can close the bookmarking pane if you want. This mode is a good way to preview your bookmarks in the Power BI Desktop. It’s also a great way to navigate bookmarks in the Power BI service, where you can also enter into full screen mode to hide all the navigation menus. To enable the preview option: This feature is in preview, so you need to enable it the first time through File>Options and Settings>Options. Open Preview features tab – > select Bookmarks option.