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.

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

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

We do have many different development and deployment Environment and we do have different configurations with respective to the environments. These configurations may be tricky but it provides ease to user to deploy to different environment without changing configurations again and again. Below are the screen shots that will help to setup these configurations. Let’s create one more environment, let’s call that QA. Now you can see QA also in the environment. Let’s now create project parameters and configure it against environments. After creating few project parameter, we can configure it. Then you assign values to parameter with respect to environment. Then Click OK. When you click ok you can see the values assigned to the parameter. Let’s change the environment to QA, and you can see the change in the parameter. This kind of parameter we can use in the expression in different package, so we just need to change at one place.

Package configurations is very important to achieve flexibility SSIS package, easy configurations allow used to changes and reuse the solutions in different environment with them customize settings and rules. SSIS package supports different types of configurations, these package configurations is also depended on deployment model. In this blog we will discuss about the Project Parameter configuration using the Project Deployment model. Let’s make the SSIS package Solution to Project Deployment model. Right Click on the SSISProject Solution Name, Click Project Deployment Model. Follow the Wizard: Click Next: This will provide you list of packages that you need to convert, then click next. Next will be setting up the protection Layer for the Package: Then Click on Next and then it will convert your Solution to Project Deployment model. Project Deployment Model allows you to configure the package at project level, it groups the Package configurations together that allows user to easily configure Parameter.