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.  

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.  

Introduction: In this article, we are going to learn how to embed Power BI Tiles in your Dynamics 365 / CRM Dashboards. Steps: Sign-in to Microsoft Dynamics 365 as a user with the system administrator security role Go to Settings > Administration > System Settings. On the Reporting tab in the Allow Power BI visualization embedding option, select Yes to enable or No to disable. Click OK. Create Dashboards in Power BI and publish it. Then Create or edit a Dashboard in CRM. And insert Power BI Tile in TAB. Select a Power BI dashboard and a tile. Complete the dashboard, Save, and close. You are all set!

Introduction: In this article, we will see how we can generate Excel Report in SSIS without doing Physical mapping. Steps: 1.    Create Excel file with one data connection in the Excel Sheet. 2.    Create a sheet in Excel pointing to the data connection. This will lead to point the connection to the data source, and help create a table in Sheet. As soon as ok is pressed, the data from the connection is pulled. We can save this Excel as a template and next time we just need to refresh the connection and the data will be refreshed in the Excel Sheet. This is the day to day process which requires manual effort, WHAT IF WE CAN AUTOMATE THE REFRESH. It is possible through SSIS services, and Microsoft Interop libraries. Steps: 1. Create a SSIS package with a Script task with C#. Edit the script and include the following code in the Script task Use below script to refresh the data in the Excel. We need to include the reference for the Interop libraries. using Excels = Microsoft.Office.Interop.Excel; string filepath = @”D:\Excel Files\Employee Files.xlsx”; Excels.Application xlApp; Excels.Workbook xlWorkBook; Excels.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excels.Application(); xlWorkBook = xlApp.Workbooks.Open(filepath); xlWorkSheet = (Excels.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkBook.RefreshAll(); xlApp.ErrorCheckingOptions.NumberAsText = true; xlApp.DisplayAlerts = false; xlWorkBook.Save(); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); Benefits: 1. This process is used if we need to generate custom reports. 2. We can use the existing data in excel sheet to create pivot table, and create custom data formats. Feel free to contact us if there is any issue.  

Introduction: This document contains the steps on how the TIBCO Cloud Integration Agent with custom connector for Dynamics GP can be deployed on server. Install a TIBCO Cloud Integration on-premise agent A TIBCO Cloud Integration agent needs to be installed on the server where the target data store resides. Download TIBCO Cloud Integration agent You can download and setup an agent from https://app.scribesoft.com Once logged in to TIBCO Cloud Integration portal in the server where the target data store resides, navigate to ‘More’ and then ‘Agents’ and then click on ‘+’ and ‘On-Premise Agent’ to create a new agent. Please refer the screenshot below: A new window will open where you can click on Install On-premise Agent to download the agent as shown below: On clicking Install On-premise Agent, TIBCO Cloud Integration will provide you to download the agent. You need to save the same to your local system. Copy the Agent Key in your clipboard; it will be used during Installation of Agent. Navigate to the location where the ScribeInstaller was downloaded and start the installation Make sure pre-requisites are setup to be able to successfully continue with the installation and click Next:   The wizard will start. Click Next until you see the screen where Agent Key is asked by the installer. Enter the same key which appeared on the screen where new agent was provided to be downloaded. And click next. Select where you want to install the TIBCO Cloud Integration agent. Finally click Install Agent. Let the setup continue. Click Finish once the setup completes.   Turning TIBCO Cloud Integration Agent on and off TIBCO Cloud Integration Agent on your system is a Windows Service which can be stopped, started and restarted. View Services application on your Windows machine to locate the TIBCO Cloud Integrationweb service Locate Scribe Online Agent in the Services. This is where you can start and stop service in the event of updating the DLLs for the Scribe Online connector. Deploying Scribe Online connector on the target system Deploying the Scribe Connector to the target system consists of copying the required DLLs and PDBs to the following folder in the Scribe Online Agent directory located on the target machine: Locate Connectors folder in the Scribe Online Agent directory. Folder named “DynAD Connector” contains DLLs and PDBs required to be available in the Scribe Online’s Connector folder. This folder needs to be copied in the Scribe Connector’s Connector folder. The address for Scribe Online Connector is: C:\Program Files (x86)\Scribe Software\Scribe Online Agent\Connectors Once the folder is pasted to the Connector folder, the agent needs to be started again. Navigate to View Local Services on the machine and locate Scribe Online Agent and start the same as shown below. For the Custom GP Connector developed for AKA, the following files are required to be copied in the “DynAD Connector” folder. Note: Since the project is divided into 2 DLLs across a Visual Studio solution, CFS.Connector.GPConnector.dll and CFS.GPClient.dll are the 2 DLLs and their respective PDDs are required. Required Files: Connector.GPConnector.dll and CFS.Connector.GPConnectorA part of the GP connector (.dll) and its PDD file GPClient and CFS.GPClientA part of the GP connector (.dll) and its PDD file Core.ConnectorApi.dllThis .dll file is required by the ScribeOnline agent to be able to access the Scribe API provided by ScribeOnline. Json.dll and Newtonsoft.Json These .dll files are required by the connector to be able to JSON file stream where the fields of the target connector are retrieved. JSON files – 5.1 DynADCampaignHeader.json 5.2 DynADCampaignLineItem.json 5.3 DynADSalesCommission.json 5.4. DynADCampaignErrorLog.jsonThe above files are the JSON files which contain the fields required by Dynamics GP entities. These files need to reside in the folder as the .dll require these. Important: These files also need to be present in the C:\Windows\System32 of the system. Updating Solutions with newly installed Scribe Online Agent Login to Scribe Online Portal through https://app.scribesoft.com Open a Solution and resign the Solution with newly installed Agent. Repeat this for all the Solutions. Conclusion: By following the mentioned steps, you can deploy the Scribe Online Agent with custom connector for Dynamics GP on server.

Microsoft power BI is a collection of features and online services that enables you to share your findings, analyse and visualise data in the form of dashboard and reports and the whole process is termed as power bi analytics. Utilizing the Microsoft Power BI Integrations, you can import information that is huge from different sources into the Power BI work area. You can alter the information or plan on changing it in the wake of bringing in to your framework.  The Microsoft Power BI Service now and then is alluded to as Power BI or app.powerbi.com. You can keep awake to date with all the fundamental data that truly matters to you. Force BI arrangement will assist you with conquering the greater part of the issues you are looking for effortlessly.  Introduction: In this article, we will see how to embed Power BI in Microsoft Dynamics 365 Operations. Steps: Register your Dynamics 365 Operations as a web app on Azure AD. Open link: http://dev.powerbi.com/apps Login to your Power BI account Fill the required details: App Name App Type: Select as ‘Server-Side Web app’ Enter a redirect URL: Your instance URL with /oauth at the end Enter a homepage URL: Your instance URL Choose API access you need. Click Register App. Once the app is registered, you will get Client ID and Client Secret. Copy these 2 keys, you will need it later. Navigate to Power BI settings in Operations which is under System Administration section.  Enter the details in Power BI configuration: Client ID: Paste the Client ID Key from Power BI registration Application Key: Paste the Client Secret key from Power BI registration Redirect URL: Your instance URL with /outh at the end Enable the Power BI Navigate to Reservation Management for instance and you will see a Power BI column. Click on Get Started. Authorize Power BI by clicking on ‘Click here to provide authorization to Power BI’ You will be redirected to the new window with authorization success message Now, you can select reports and dashboards from Power BI and pin them to your workspace.