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 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: 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.

With Scribe Insight, you can import XML as a data source to fulfill two main tasks: Support integration with locally available XML data. Receive data as XML from remote systems. Creating A Publisher Expand the Console site node. Expand the Integration Server node, and then click Publishers and Bridges. The Publishers/Bridges window opens. Click ‘Add’. The ‘Add New Publisher/Bridge’window opens. Add a new Publisher/Bridge using the following steps: (i) Step 1: Publisher/Bridge Type: Select the Type from the drop-down menu and give an appropriate name. Proceed to Step 4 – Publisher/Bridge Properties. (ii) In Step 4 – Publisher/Bridge Properties, click ‘Add’ after Connecting. Select the Entity and fill in the Message Label. Select the Event checkboxes you require and add any related parent or child entities as well as Priority. One can also choose to ignore changes from a User. (iii) Select ‘Active’ in Step 5 and click Apply. To Retrieve An XML File Now that a Publisher has been created, the User can get an XML file of a record that has been created or updated after the activation of the Publisher. To retrieve an XML file of a record created or updated, go to Queue Browser in the Console and click on ‘[INPUT] CLOUDFRONTSBI\PRIVATE$\SCRIBEIN’. The message label of the Entity in the Publisher will be displayed. Right-click on the Message Label and click on ‘Save Body’. Select the Destination Folder and click OK. This will save the XML File (.xml) of the record. To Retrieve XML Schema In Step 4. Publisher/Bridge Properties, click ‘XML Schemas’. Scribe Insight generates the new XML schema of ‘Account’, which is required when you connect to the XML schemas using the Scribe Workbench XML Source Connection Wizard. Browse to where you want to save the schema file (.xsd) and click OK. You are prompted to verify the location. Click OK.

Introduction: In this blog article, I will demonstrate how to enable ArcGIS in PowerBI, its features and how to use it to display data. The ArcGIS Visual, created by Esri, displays data in a unique and immensely customisable way. The Visual allows the User to select the type of map, various ways to represent data points and additional features like distance mapping and reference layers to make the visual interactive and informative. In order to enable ArcGIS, the User must agree to Esri’s terms and conditions. Enabling the ArcGIS Visual: To do this in PowerBI Service, go to the ‘Settings’ menu. Select ‘ArcGIS Maps For PowerBI’ and select the checkbox next to ‘Use ArcGIS Maps for PowerBI’. Click Apply. Now you can go to the ‘Editing View’ and select the ArcGIS icon from the Visualisations Pane. To enable it in PowerBI Desktop, select ‘Options and Settings’ in the File menu. Select ArcGIS Maps for PowerBI in the Preview Features section. Now, select the ArcGIS icon in the Visualisations Pane to get started. Using the Visual: After loading the data into PowerBI, drag your data in the appropriate fields in the Fields pane. Now here is where the fun begins! Click on the ellipses and select the ‘Edit’ option to start customising your visual. A task pane appears at the top of your visual. The first option is ‘Basemap’. In this option, you can select the type of map you would like to use and that would best showcase your data. The next option is Location Type. Here, you can select how you want your location data represented. Points will show you data points and Boundaries will display data by state boundaries. Map Theme, the next option, will let you select different visual ways to display trends in your data. ‘Size’ displays points variating by size based on your data, while ‘Heat Map’ shows where the values are high, medium and low by color. Symbol Style allows you to select which symbol you want to use to represent the data and customise the symbol by style and size. Pins allow you to highlight locations in your map that you want to showcase other than what your data is displaying. Just type the location in the search bar and select the correct location to add a pin there. You can also change the color of the pins if required.   The Drive Time option allows you to show which data points are within a given distance or driving time from a pin. This feature can also be customised by selecting color, transparency and outline preferences. Reference Layer is a feature that allows you to add data to the map based on common statistics like the country’s population, gender ratio and various other demographics like age and diversity. This allows the user to add more information to their map that could be of significance to the data being represented. One can also hover over states to see the reference data of that state. Conclusion: As you can see, the ArcGIS Visual is designed to make your data easy to understand and interactive while providing additional relevant data. Its customisability combined with its power-packed features makes this one visual that could really add immense value to any report or dashboard.