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.

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.  

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

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.

New SQL Server Management Studio 17.3 Release update: This wizard was created to improve the current import experience leveraging an intelligent framework known as Program Synthesis using Examples (PROSE). For a user without specialized domain knowledge, importing data can often be a complex, error prone, and tedious task. This wizard streamlines the import process as simple as selecting an input file and unique table name, and the PROSE framework handles the rest. This wizard is driven in a very simple manner to ease the data load in SQL Server from Flat Files. This Feature limits to SQL Server Management Studio 17.3 and higher versions. To access the Import Flat File Wizard, follow these steps: Open SQL Server Management Studio. Connect to an instance of the SQL Server Database Engine or localhost. Expand Databases, right-click a database (ImportFlatFile in the example below), point to Tasks, and click Import Flat File above Import Data.   Sample Data Load From Flat File using Import Flat File Wizard. Run the Import Flat File Wizard. Click on Next to provide the Input Source File, your source file could be any delimited file. NOTE: File Extension doesn’t matter unless the file is a Flat file and is a Delimited file.   Click on Next, it will take you to preview of the data, the preview limits to first 50 Rows fetch from File.   Clicking on Next, will allow user to Alter the metadata of Columns that will be created by this wizard. Flat File provides default datatype, we can change it in below screen. Event Primary kea and Not Null Constraint can be created with this wizard.   Clicking on Next will take you to Summary page and will provide final information of the data transfer and Table that will be created.   Clicking on Next will start the import process.   Table create and data is successfully Loaded.   This features uses PROSE analyses data patterns in your input file to infer column names, types, delimiters, and more. This framework learns the structure of the file and does all of the hard work so our users don’t have to.