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.  

Introduction: We can split an Address column into multiple columns for Flat Number, Building, Road, City etc. in order to make it easier to process the data. In order to do this, we can use the Derived Column Transformation in SSIS. Steps: In the screenshot below, you can see the Address column has an aggregated address for each employee. In your SSIS Package,  bring a Data Flow Task. Configure you Excel Source in Connection Manager and select the sheet you want the data from. Check in the Columns Tab to verify if the Columns are correct. Now drag the  Derived Column Transformation and connect the Excel Source to it. Double click the Derived Column Block and configure it as shown in the screenshot below. Flat: SUBSTRING([Col 2],1,FINDSTRING([Col 2],”,”,1) – 1) Building: SUBSTRING([Col 2],FINDSTRING([Col 2],”,”,1) + 1,((FINDSTRING([Col 2],”,”,2) – FINDSTRING([Col 2],”,”,1)) – 1)) Road: SUBSTRING([Col 2],FINDSTRING([Col 2],”,”,2) + 1,((FINDSTRING([Col 2],”,”,3) – FINDSTRING([Col 2],”,”,2)) – 1)) Area: SUBSTRING([Col 2],FINDSTRING([Col 2],”,”,3) + 1,((FINDSTRING([Col 2],”,”,4) – FINDSTRING([Col 2],”,”,3)) – 1)) City: SUBSTRING([Col 2],FINDSTRING([Col 2],”,”,4) + 1,((FINDSTRING([Col 2],”,”,5) – FINDSTRING([Col 2],”,”,4)) – 1)) PIN: SUBSTRING([Col 2],FINDSTRING([Col 2],”,”,5) + 1,((LEN([Col 2]) – FINDSTRING([Col 2],”,”,5)))) Once the configuration is done, connect Derived Column Transformation to you Target Block. Configure your Target Block to your Destination as shown below and check the Mappings to ensure the correct Source and Target columns are mapped. This is what your Data Flow should look like: Click on ‘Start’ when done. Once the execution is complete, check your Target Database for the desired output. Conclusion: This can be a requirement in many scenarios and by following the steps mentioned above, we can achieve the desired output.

Introduction: In this article, we will learn how to configure Live connection for Analysis Services in Power BI. Requirements: Power BI Licence SQL Server Analysis Service Power BI Gateway (Refer this link to configure Power BI Gateway) Steps to Connect Analysis Services Live in PowerBI Open PowerBI Desktop Click Get Data and Select Analysis Services In the Analysis Services Connection details, select Live Connection Mode for Live Connection. Once the Data, Measures, KPIs are loaded you can create Power BI Dashboard. Live Connection enabled status is visible at the right bottom of the Power BI Desktop. Before publishing the Dashboard, login into Power BI Service. Under Manage Gateway, we must add a data source for Analysis Services. Note: The account should be a Server Admin on the Analysis Server and should be a domain account in the same domain that the Analysis Services instance is located. (Click here for more information on Analysis Server Connection) Go back to Power BI Desktop and publish the Power BI dashboard. In the Power BI Service, under the Reports you will be able to see the published Live SQL dashboard.

Introduction: In this article, we will learn how to configure Live connection for SQL in Power BI. Requirements: Power BI  Licence SQL Server Database Engine. Power BI Gateway (Refer this link to configure Power BI Gateway) Steps to Connect SQL Live in PowerBI: Open PowerBI Desktop Click Get Data and Select SQL Server. In the SQL Connection details, select DirectQuery Mode for Live Connection. Once the Data is loaded, you can create Power BI Dashboard. DirectQuery enabled status is visible at the right bottom of the Power BI Desktop. Before publishing the Dashboard, login into Power BI Service. Under Manage Gateway, we must add a data source for SQL Server. Go back to Power BI Desktop and publish the Power BI dashboard. In the Power BI Service, under the Reports you will be able to see the published Live SQL dashboard.

Introduction: In this blog article, I will talk about the November Updates for PowerBI Desktop. Conditional Formatting based on Rules: You can now set the colour of the text based on a predefined rule depending on your business logic. This enables you to colour-code your data based on a set of rules that you set based on your business logic. To do this, go to the Conditional Formatting card and select the field you want to make changes to from the drop-down menu. Switch the ‘Font Colour Scales’ option On and select ‘Advanced Controls’. The general Font Colour Scales dialog box will open. Select the ‘Colour by rules’ checkbox. The Rules pane is displayed. There are dropdown lists to select the conditions, text fields to set values and a colour selector to set the colour of the data that satisfies the condition. If left empty, they default to the Minimum and Maximum values. To add additional conditions, click the ‘Add’ button. Once done, click OK. You will see the Conditional Formatting Rules being applied and the data being colour-coded accordingly. Also, if a data value satisfies multiple rules, the bottom rule applies. Cell Alignment for Table and Matrix: Now, you can set the alignment of data to Left, Right or Center for cells in a table or matrix. This feature is very useful when formatting your report. To do this, go to the Field Formatting card and select the field you want to make changes to. Go to the ‘Alignment’ option and select the alignment of your choice from the dropdown menu. You will now see that all the data in the selected fields have been aligned according to your selection. Selection Pane Update: The Selection pane now has an added feature of letting the user select which objects are displayed on top of the canvas if there is overlapping. Locking Report Objects: There is now a checkbox called ‘Lock Objects’ that allows you to fix the position and the size of each object in a report so that no changes are accidentally made. This is very useful when browsing reports as the layout will not be affected. One thing to keep in mind is that this setting will not be saved with the report and to ensure this checkbox is ticked when browsing. New options for slow/large data sources: Sometimes, if you have a large or a slow data source, obtaining a response to actions taken in the report can take time. To improve this, options have been introduced to send fewer queries so that it is easier and quicker to interact with the report. To enable this option, go to Options and settings in the File menu. Select Options and then the Query Reduction tab. From here, you can disable cross-highlighting and automatic slicing and filtering within the report. For slicing and filtering, an apply button can be added instead so that the report is only sliced/filtered once you click the ‘Apply’ button. As you can see above, the pie chart is not sliced when ‘Product 1’ is selected. It is only sliced when the ‘Apply’ button is clicked as seen below. Improvement in filtering: The limit of 500 values on the number of values you can match when filtering has been removed. This is applicable for all data sources except Live connections to analysis services models. Conclusion: As you can see from above, the November Update is full of extremely useful features that further optimize the way we use PowerBI Desktop.

Introduction: In this article, we will learn how to successfully connect to Analysis Services in Power BI for Live Connection. Checklist for Connection: The Domain Account must be a Server Admin in Analysis Server. Right Click on the Analysis Server > Select Properties > Select Security > The domain account must be added/present here.   Check Properties of Domain Account User for UPN (User Principle Name) Open Server Manager >Select Tools at right Upper Corner >Select Active Directory Users and Computers. Drill down the domain name >Select Users >Double-Click on the domain account. Click the Account tab. Note the User Logon name. User Principal Name (UPN) Mapping in Power BI Under the Analysis Server Data Source setting in Power BI Service, Navigate to Users and select the user and click Map User Name. Select CustomData and enter Replace data as the User you are logged in with and With Data as the Server Admin Domain Account UserLogon Name you had noted before. You can even test the mapping rule. For more information on UPN, watch the video for UPN Mapping in Power BI.