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.

Introduction: In this article, we will learn how to configure Power BI Gateway. Steps to configure Gateway Login to Power BI service. Select Downloads icon> Data Gateway. You will be redirected to Power BI Gateway Download Page and click Download Gateway. Run the PowerBIGatewayInstaller. Click Next and then Install. The Power BI Gateway will be Installed. Sign to Power BI. Give the Gateway a Name and Recovery Key and Click Configure. Now, we are all set.

Introduction: Recently, we encountered a strange behavior of Scribe On-Premise Agent when we deployed a DLLs for a Custom Connector. After deployment of DLLs on the Agent, the status of the Scribe Agent was stuck at “Updating” and the Connector was not visible under the connector drop down. Troubleshooting: We checked the “MICROAGE1 Agent 2” Agent log and found out an error in .Net Framework. Error Details: Message: Folder (C:\Program Files (x86)\Scribe Software\TIBCO Cloud Integration Agent 3\Connectors\ConnectorName) has the following exceptions during discovery:System.ApplicationException: Exception Type : FileLoadException Message: Could not load file or assembly ‘file:///C:\Program Files (x86)\Scribe Software\TIBCO Cloud Integration Agent 3\Connectors\ConnectorName\********.dll’ or one of its dependencies. Operation is not supported. (Exception from HRESULT: 0x80131515) StackTrace :    at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.InternalLoadFrom(String assemblyFile, Evidence securityEvidence, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm, Boolean forIntrospection, Boolean suppressSecurityChecks, StackCrawlMark& stackMark) at System.Reflection.Assembly.LoadFrom(String assemblyFile) at Scribe.Core.Access.AdapterDiscoverer.DiscoverConnectors(String folderName) — Inner Exception — Type: NotSupportedException Message: An attempt was made to load an assembly from a network location which would have caused the assembly to be sandboxed in previous versions of the .NET Framework. This release of the .NET Framework does not enable CAS policy by default, so this load may be dangerous. If this load is not intended to sandbox the assembly, please enable the loadFromRemoteSources switch. See http://go.microsoft.com/fwlink/?LinkId=155569 for more information. Steps to perform Resolution 1 Login to MICROAGE1 Server C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config Open CONFIG File “machine” Add the code at line 151 and Save the file <runtime> <loadFromRemoteSources enabled=”true”/> </runtime> Current Code screenshot: Restart the Scribe Agent in Services Check if the DLL’s are deployed successfully by creating a connection in TIBCO Cloud Integration for Channel Online. If still failed then perform Resolution 2. Steps to perform Resolution 2 Login to MICROAGE1 Server Stop Scribe Agent in the Services. Go to C:\Program Files (x86)\Scribe Software\TIBCO Cloud Integration Agent 3\Connectors\ConnectorName and Right Click on a DLL file. (For example: As per the screenshot, “Contact”) Click on “Properties” Under Security Section press “Unblock” Click Apply and Ok Again, open the Properties to check whether it is successfully unblocked. Repeat Step 4-8 for all DLLs. Start the Scribe Agent in the Services.  

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.