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.

Introduction: In this blog we will discuss the different Power BI Premium Capacity nodes available and the steps to purchase these. We will also discuss how to assign a premium node to a workspace in Power BI. Power BI Capacity Based SKUs: There are 3 categories of SKUs that can be purchased which gives us a number of ways to embed our content using any of the capacities depending on our requirement. Following are the 3 different series provided by Microsoft. Power BI Embedded A SKUs This capacity is generally used by small ISVs for embedding own solutions. Unlike the other two series this series is charged on an hourly basis. It can be paused and started whenever needed and also it has additional scalability. Power BI Premium EM SKUs This capacity is also generally used by ISVs for 3rd party embedding in a custom application or in SaaS applications like Sharepoint or Teams. It offers everything provided by A SKUs and also offers the ability to share Power BI reports. EM SKUs unlike A SKUs cannot be paused and require a monthly or annual commitment. Power BI Premium P SKUs For this capacity, the hosting organization does not generally have any requirement for custom software development. It has all the features of E SKUs and also additional Power BI services like App sharing, Ad hoc dashboard sharing.etc. They allow the users to use Power BI’s browser based experience and are more dependent on the UI provided by Power BI. Purchasing the nodes: If you are a Billing Admin in your O365 tenant then you can purchase the capacity nodes under Purchase service in Billing. Currently the EM3, P1, P2 and P3 capacities can be purchased from the Office portal. The A series can be purchased from the Microsoft Azure Portal. Click on New and search for Power BI Embedded. Fill in the necessary details. A capacity administrator needs to be assigned, who will be responsible for creating the capacities and assigning it to the workspaces. Note: I still haven’t figured out where to find the EM1 and EM2 nodes but I’m presuming that they haven’t been released yet. Assigning a Capacity to a workspace: I am using the A1 node for this example. We can see that the Pause feature is available in this node. The first step is that the capacity admin logs into their Power BI account. It is important to note that the capacity admin should have a Pro License. Click on Settings and go to the Admin Portal. Next Select your capacity. Here we can manage capacity size, assign user permissions. etc. If you are opening the portal for the first time then there will be an Assign Workspace option available. Under User Permissions select Entire organization (Assign capacity to entire organization) or Specific users (Assign capacity to specific Users) Depending on the option selected above either the workspaces of the entire organization or the workspaces of the selected users will be loaded below Now we create a sample App workspace named ‘Test Workspace’ to which we will assign our capacity node. You can also use an existing workspace instead. Click on Workspaces and select Create app workspace Name your workspace and enter all the workspace members. Under the Advanced option there should be a Premium toggle button available(This option will only be available if the user is a Capacity Admin, Power BI Admin or a Global Admin and also the User permission mentioned above needs to be given). Turn the Premium option On. Click on Save. If you are using an existing workspace then go to workspace and click on the ellipses(…) beside the workspace and select the Edit Workspace option and enable the Premium option. To be sure that we have successfully backed our workspace with a capacity node, we can confirm by looking at the diamond icon beside our workspace name. Conclusion: We have successfully learnt how to purchase a Power BI capacity node and assign it to a workspace which will enjoy all the benefits provided by Power BI Premium and also give us Embedding Services with it.

Introduction: There are times when you may want to use Excel to view and interact with a dataset that you have Power BI for quick stretching of data slice and dice. Few people feel comfortable with excel PivotTable and PivotCharts, so by using Analyze in Excel, you can do just that, and access PivotTable, chart, and slicer features in Excel based on the dataset that exists in Power BI. Supported Version and Requirements: This feature support’s MS Excel 2010 SP1 and Later version. Excel PivotTables do not support drag-and-drop aggregation of numeric fields. Your dataset in Power BI must have pre-defined measures. Working: 1. Go to Power BI Workspaces and then go to datasets. When you select Analyze in Excel from the menu (…) associated with a dataset or report in Power BI, Power BI creates an .ODC file and downloads it from the browser to your computer. When you open the file in Excel, an empty PivotTable and Fields list appears with the tables, fields, and measures from the Power BI dataset. You can create PivotTables, charts, and analyze that dataset just as you would work with a local dataset in Excel. Dataset should have a pre-defined measures. The .ODC file has an MSOLAP connection string that connects to your dataset in Power BI. When you analyze or work with the data, Excel queries that dataset in Power BI and returns the results to Excel. If that dataset connects to a live data source using DirectQuery, Power BI queries the data source and returns the result to Excel. Analyze in Excel is very useful for datasets and reports that connect to Analysis Services Tabular or Multidimensional databases (because it provides a live connection to MSOLAP), or from Power BI Desktop files or Excel workbooks with data models that have model measures created using Data Analysis Expressions (DAX). 2. Open ODC file in Excel. Enable the security warning to access the dataset. Note: Administrators for Power BI tenants can use the Power BI Admin Portal to disable the use of Analyze in Excel with on-premises datasets housed in Analysis Services (AS) databases. When that option is disabled, Analyze in Excel is disabled for AS databases, but continues to be available for use with other datasets. This is additional feature that provide security feature to your dataset. Now that Excel has opened and you have an empty PivotTable, you’re ready to do all sorts of analysis with your Power BI dataset. Just as with other local workbooks, with Analyze with Excel you can create PivotTables, charts, add data from other sources, and so on. And of course, you can create different worksheets with all sorts of views into your data. Easy to Share: Once your workbook is saved, you can share it with other in your organization. When a user with whom you’ve shared your workbook opens the workbook, they’ll see your PivotTables and data as they appeared when the workbook was last saved, which may not be the latest version of the data. To get the latest data, users must use the Refresh button on the Data ribbon. And since the workbook is connecting to a dataset in Power BI, users attempting to refresh the workbook must sign into Power BI and install the Excel updates the first time they attempt to update using this method. Since users will need to refresh the dataset, and refresh for external connections is not supported in Excel Online, it’s recommended that users open the workbook in the desktop version of Excel on their computer.

Introduction: In this blog article, we will explain about the new Visual of Power BI: Ribbon chart! Ribbon Chart: The visual is like a stacked column chart, but each column’s inner categories are sorted according to their rank for that column. Additionally, the inner categories are connected by ribbons across the columns. Refer below Screen Captured: You’ll find this new chart option to the right of the combo chart. The chart’s field well and formatting options are the same as a stacked column chart with some extra formatting options for the ribbons. There’s a Ribbon card in the formatting pane that lets you control the ribbon style: Spacing between ribbons – You can use this option to set a gap width between the ribbons. By default, there is no spacing. Match series color for ribbons – By default, the colour of the ribbons is to match the category’s color, but you can turn this off and the ribbons will be grey in color. Transparency for ribbons – You can control the transparency of the ribbon’s color. By default, it is set to 30. Border for ribbons – By default, the ribbons don’t have a border, but you can turn borders on.  

Introduction: There may arise a situation where we may want to link one report to a more detailed report dynamically in Power BI. Although we can link dashboards to reports and also provide a custom link for a tile in a dashboard, report to report linkage is currently not a feature available in Power BI. There is a way to achieve this by using URL filtering. Below is a report which shows Card transaction details based on Expense Type. We want to link this report to a more detailed report after clicking on the URL link icons in the table. You can download the sample CSV file from here http://bit.ly/nitincsv1 After clicking on the URL Link icon a report like this should open up which is filtered by Expenditure Type. Steps: The steps to achieve this are mentioned below: 1. We first need to understand how URL filtering works. The syntax for URL filtering is URL?filter=Table/Field eq ‘value’ • Table and Field names are case sensitive • Value should be put in single quotes The Table and Field can be found under Fields in Power BI Desktop The URL can be found on the web page in Power BI Online. Every page in a report has a unique URL in Power BI.  You can find it in the browser address bar of the report. The URL filter for filtering ExpType in CardDetails for the value ‘Bills’ would be, https://app.powerbi.com/groups/me/reports/6ea11c00-85ca-4b8e-907a-42979eaadcaa/ReportSection1?filter=CardDetails/ExpType eq ‘Bills’ 2. The above is a very static example for filtering ExpType but this can be made much more dynamic by using DAX Create a new Calculated Colum for CardDetails   3. Enter the following line as the DAX Code: Link = “https://app.powerbi.com/groups/me/reports/6ea11c00-85ca-4b8e-907a-42979eaadcaa/ReportSection1?filter=CardDetails%252FExpType%20eq%20%27″&CardDetails[ExpType]&”%27” In the above function we have put the ASCII values for blanks, apostrophes and equal to sign for ensuring that the URL works efficiently • Blank -> %20 • Equal -> %252 • Apostrophe -> %27 We append the CardDetails[ExpType] in place of value in the URL Syntax. The rest of the URL remains the same. After pressing Enter the column generated will look something like this in a table visualization 4. The String generated in our column needs to be converted into a hyperlink. Click on Link in Fields, Go to Modeling and select Data Category as Web URL The Link Column should now look like this, 5. We can convert the hyperlink into a more pleasant URL link icon by going to Visualizations->Format->Values->URL Icon(Turn On) 6. The final Report generated looks like this 7. Clicking on the URL icon will dynamically filter a new detailed report in a new tab in your browser. To confirm whether the report has been filtered or not, we can look at the Filters section in Power BI Online as well as the URL. Conclusion: As you can see, we have successfully created a Report with Links which on being clicked generates a detailed report with dynamic filtering.

In our day to day life we are more concern about our data security and sensitive information’s when we fill out some purchase form and other billing application which requires your Credit card and other personal information. This data can be viewed easily in the backed users if it’s not secure controlled. SQL Server 2016 provides a feature known as Dynamic Data Masking which is a security feature that limits the access of unauthorized users to sensitive data at the database layer. As an example of the need for such a feature is allowing the applications developers to access production data for troubleshooting purposes and preventing them from accessing the sensitive data at the same time, without affecting their troubleshooting process. Another example is the call center employee who will access the customer’s information to help him in his request, but the critical financial data, such as the bank account number or the credit card full number, will be masked to that person. Dynamic Data Masking, also known as DDM, is a simple security data protection method allows you to determine your “sensitive” data, by field in order to configure the suitable masking function to hide it from queries. This feature requires no coding effort from the application side or encrypting or applying any change to the real data stored in the disk. Features: Provides configurable masking policy using simple T-SQL command. Database is not changed physically and database operations are not affected. Flexibility to define a set of privileged SQL users or roles for unmasked access. Types of Masks: There are 4 types of masks are offered by SQL Server 2016. 1. Default Mask The default mask masks the full data according to the assigned data type. For string types (char, nchar, varchar, nvarchar, text, ntext), it replaces the data with XXXX or fewer Xs, in case size of field is less than 4. For numeric datatypes (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real), it uses 0 value. For binary datatypes (binary, varbinary, image), it uses a single byte of binary value 0. 2. Email Mask The email mask especially for the fields that stores emails. It exposes only the first letter of email followed by XXX, followed by @ sign, followed by XXXX and a constant suffix “.com” to form an email. E.g. aXXX@XXXX.com 3. Custom Mask SQL Server 2016 allows you to define your own mask for a specific field. In this method, you can define the prefix and suffix characters to be exposed, and the padding will be shown as it is. “prefix,[padding], suffix”. 4. Random Mask Random mask can be defined over any of the numeric fields. It exposes a random number within the defined range. Example 1. Create a table with different mask functions CREATE TABLE [dbo].[Contacts] ( [ID] [int] IDENTITY(1,1) NOT NULL Primary key, [FName] [nvarchar](30) MASKED WITH (FUNCTION = ‘default()’) NOT NULL, [LName] [nvarchar](30) NOT NULL, [CreditCard] [varchar](20) MASKED WITH (FUNCTION = ‘partial(2, “XX-XXXX-XXXX-XX”, 2)’) NULL, [SalaryINR] [int] MASKED WITH (FUNCTION = ‘default()’) NULL, [OfficeEmail] [nvarchar](60) MASKED WITH (FUNCTION = ’email()’) NULL, [PersonalEmail] [nvarchar](60) NULL, [SomeDate] [datetime] MASKED WITH (FUNCTION = ‘default()’) NULL ) Insert Some data in the table 2.  Create a user with only select permission. CREATE USER [ReadOnlyUser] WITHOUT LOGIN; GRANT SELECT ON Contacts TO ReadOnlyUser; 3.  Now select the data with the Existing using and the new User that is created The data will be masked for the ReadOnlyUser. Conclusion: This technique allows a developer to debug the Production environment without breaching security or confidentiality for a user.