Introduction: Recently, we faced an issue with integrating Unit Price to Dynamics GP through GP Web Services using TIBCO Cloud Integration. We were integrating the Unit Price from Dynamics 365 but the Unit Price in an Invoice was created with $0 Price. In this article, we will learn to resolve the issue for Dynamics GP Sales Invoice Unit Price always setting to $0 when creating through GP Web Services. Resolution: In Dynamics GP Web Service Policy Configuration for Update Sales Invoice Policy, you need to update the “Calculate Unit Price Behavior” to “Do Not Calculate”. Also, make Sure that you have selected the right Company and assigned proper roles for the policy. The role should not be Default. Note: The “Calculate Total Amount Behavior” should be “Calculate” otherwise the Unit Price will be always 0, even if you set the “Calculate Unit Price Behavior” to “Do not Calculate”.

Introduction: This blog will help you through connecting power BI to your D365 environment. Steps: Open Power BI Desktop, click on Get data connection, and click on More… Go to Online Services > then select Dynamics 365 (Online) > then click on connect. It will ask you about Web API URL, this is not the CRM URL, it is the API URL that will allow you to communicate to you D365. You can locate the Web API in your Dynamics 365: Go to Dynamics365 > Go to Settings > Customizations. Select Developer resource inside Customizations. Inside the Developer Resource you can locate your Web API URL. Copy the Service Root URL and paste it in your Power BI Desktop. After adding the URL click on and click on the Organizational Account and click on Sign-in. After successful sign in, click on connect, you will see list of entities available for the login. Select the tables and get going, you are good to go now.

Introduction: With the latest update of Power BI, you can now ask top-n type questions and get answers based on your dataset. Questions like ‘What are the top 3 products by sales?’ or ‘Top Manufacturer by country’ are examples. This is also valid for bottom-n type questions like ‘Least selling product in a country’. Steps: 1. Go to your PowerBI Workspace and select your report. 2. Click on the ‘Edit’ button. 3. Select ‘Ask a question’. 4. Type in your question and the visualization will form with the answer based on your dataset. 5. Make changes to your Axis, Legend and Value fields if required. Conclusion: This feature enables the User to obtain important information from the dataset quickly and easily using natural language thereby enhancing the capability of Power BI to provide self-serving business intelligence.

Introduction With the latest release of Power BI, new feature has been added to share the power BI reports as well. Yes, you heard it right, now we can share the Power BI Reports as well to teams and partners. How it works: Go to your workspace and open the report that needs to be shared. Click on the share option. Enter the email address and click on share, if the send email notification option is checked then notification will be send to users. Conclusion: This is a superb feature that is added to power bi, allowing organization to share reports as well with dashboards for detailed information.

Introduction: In this blog we will see how we can deploy SSIS integration package individually using file System Deployment. Steps: Convert the current project to package deployment model. After clicking the project deployment model, the screen will pop up with the compatibility check for all the packages with below screen, click ok if all is good. Below icon and name will be changes for the solution when you click Ok button from above screen. Create your package and test it in visual studio, save your package. Now we go for deployment of package: Capture the path for the package: “D:\PackageDeploy\Package.dtsx” Deploying Package to SQL Server and Configuring it to SQL Agent Job: Create New job Create the Steps in JOB Crete the Step. Select the Step type to SQL Server Integration Services Package Select the Package source type: File System as this is a package Deployment model. Provide the package path Note:  While providing the package path we need to make sure that SQL server can communicate to the path as this will be running inside the SQL server. Execute package and check the Job status: Conclusion: With the above steps you should be able to deploy the SSIS package using package deployment model, there are many more methods to deploy the package, like- by using manifest file, Project deployment models and so on, stay tuned for upcoming blogs.

With Power BI latest release, you can now hide the page in Power BI reports, this page is available while you are developing and when you hide it by right click on the package and selecting Hide Page, then that page will not be available in Reading mode. When you select the Hide page option then the option is checked as hide page. Page is marked as dull colour to show that it is hidden. When you publish this to your workspace then page will not be displayed in reading mode: So, you can technically hide page in your power bi report if you don’t want to use them, but if the user has rights to edit the report then he can change the settings.

TRIM Function has haunted SQL DBA for ages. If you have been using SQL Server for a while, you will totally agree with me over here. In this blog post we will see how the new feature TRIM of SQL Server 2017 works in few simple words. SQL Server DBAs and Developers have always dealt with SQL Strings and the leading and trailing spaces often makes them crazy. Data query may not be at it’s best if there are leading spaces around strings and which are not useful for data comparison and well as storage point of view. In the previous server versions developers used to two different functions LTRIM and RTRIM around the string to get necessary results. However now in SQL Server 2017 we have a new feature introduced which is TRIM(). This function works just like LTRIM and RTRIM together. When you run above script you can see that when we use function TRIM around the string it removes leading and trailing spaces. Trim function is combination for LTRIM & RTRIM, and only available in SQL server 2017. For earlier version, LTrim and RTrim is available.

Introduction: The field properties pane allows you to change the name and description of a field from your fields list. Responsive slicers are the latest update to responsive visuals work. Responsive slicers: Responsive slicers are the latest update to responsive visuals work. With responsive slicers, you can resize them to a variety of different sizes and shapes, from horizontal to square to vertical, and the vales in the slicer rearrange themselves as you do. You can make both horizontal slicers and date and range slicers responsive. As with all our responsive visuals, the benefits are best seen when you use them on the Power BI mobile apps as well, since they resize automatically to fit well on all platforms. Field properties pane and field descriptions: The field properties pane allows you to change the name and description of a field from your fields list. You can access this pane by selecting Properties from the field menu. In the pane, you’ll be able to rename the field and give it a description. Once, you give a field a description, you can see if anytime by hovering over the field in the list. If you have multiple people editing your reports, this is a great way for everyone to understand what fields they should use and how measures are being calculated.  

Introduction: Narratives for Power BI is a product that automatically delivers dynamic narratives that explain the insights within your data. No more manually writing explanations and spending time interpreting data. Instead, the narratives, which are powered by advanced analytics, are perceptive and dynamic and explain what is most interesting and important in your data. Drill down deeper into your data and watch narratives update in real-time during the data discovery process Steps: Go to powerbi.narrativescience.com and enter your business email id.  A link for downloading the extension and installation instructions will be mailed to you A pibiviz file will be downloaded on downloading the extension. A pbiviz file is nothing but a custom visual which can be imported in Power BI Desktop. Import the file on Power BI Desktop Benefits: Automated Narratives generated that give more detailed insights about the report which may not even be obvious Real time update on interaction with data Many customization options to personalize your narrative Click on Narrative and select Dimensions and Values based on which Narratives will be generated. After selecting the fields you will have to select your narrative type. Discrete: For distinct data like that in Bar Charts Continuous: For continuous data like that in Line Charts Percent of Whole: For data by percentage like Pie Charts Scatterplot: For data based on scatterplot like Charts A narrative gets generated It also changes on real time interaction The type structure and verbosity can be customized in the Format Pane Type Can be Discrete, Continuous, Percent of Whole or Scatterplot. Structure can be either in Paragraph format or Bullet Points. Verbosity the level of information displayed. Low verbosity would show less detailed narrative with high level information while High verbosity would show a very detailed narrative. Medium verbosity would be a midway between both. I hope this blog encourages you to use this powerful extension to improve your reports by making it as detailed as possible with minimalistic efforts!  

Introduction: In this blog, I will demonstrate how to remove or filter duplicate records in SSIS using Sort Tranformation. Steps: First, configure you OLE DB as your Source using the OLE DB Source as shown below. Bring the Sort Transformation and configure it as shown below. Check the box at the bottom of the Window that says ‘Remove rows with duplicate sort values’. Now you can connect to your destination by dragging the required block and connecting the Sort Transformation to the Destination block. For blog purposes, I have connected it to the Multicast block. Your Data Flow should look like: Click on start to execute your Data Flow. Benefits: Removal of duplicate rows can be useful when trying to avoid Data Redundancy in your System as well as to reduce the memory required.