Categories
Artificial Intelligence Machine Learning Power BI

Automated Machine Learning (AutoML) in Power BI

Automated Machine Learning (AutoML) in Power BI presentation by Hector Villafuerte at the SQL Saturday  – February South Florida 2020 and South Florida Code Camp in Davie.

AutoML was proposed as an artificial intelligence-based solution to the ever-growing challenge of applying machine learning. Business analysts can build Machine Learning models to solve business problems that once required data scientists. In this session, Hector will explain the principles of Machine Learning and AutoML (Automated machine learning) and he will demo AutoML PowerBI features end-to-end and show How to interpret and extract the optimum results based on specific business problems.

 

Categories
Artificial Intelligence Machine Learning Power BI

Artificial Intelligence (AI) for Business Intelligence

Artificial Intelligence (AI) for Business Intelligence presentation by Hector Villafuerte at the Power BI Fest – South Florida 2019.

This image has an empty alt attribute; its file name is image-2.png

Cognitive Services provides intelligent algorithms to see, hear, speak, understand and interpret user needs. Azure Machine Learning performs highly specialized tasks such as feature selection, algorithm selection or hyper-parameter optimization. Now with Auto ML in Power BI, business analysts can build ML models to solve business problems that once required high skilled data scientists. Hector will show and demo some original use cases with Cognitive Services, Azure Machine Learning and other new AI features recently available in PowerBI.

Event Date: November 16, 2019 8:00 am – 6:00 pm

Address:
Nova Southeastern University
Main Campus – Davie
3301 College Avenue
Davie, FL 33314

Categories
Power BI Uncategorized

Power BI for the Enterprise

Power BI for the Enterprise presentation by Hector Villafuerte at the Power BI Fest – South Florida 2019.

This talk provides attendees with insights on important aspects of implementing Power BI Service at organizations of all sizes. It empowers business users with the knowledge to establish a symbiotic and productive relationship with IT departments. This partnership makes it possible to effectively run an efficient data driven organization and departments; meanwhile, being compliant with data governance, security and other requirements.

– The presentation covers business and technical aspects on major topics; such as:

  • Data Governance (IT and Business perspectives)
  • Security
  • Version/Source control
  • Deployment
  • Data Pipelines

In a time where data privacy and breaches are a major concern, this session outlines important practices to business and technical users; making it a must see session.

Event Date: November 16, 2019 8:00 am – 6:00 pm

Address:
Nova Southeastern University
Main Campus – Davie
3301 College Avenue
Davie, FL 33314

Categories
No SQL Power BI

PowerBI for Large and Diverse databases 2019

PowerBI for Large and Diverse databases by Hector Villafuerte at the South Florida Code Camp 2019

Today’s enterprise business analytics requires run complex analytic queries against large datasets stored in different sources like Azure SQL Database, Azure Analysis Services, DynamoDB, MongoDB and others. In this session, BI Architect and Microsoft Certified Professional, Hector Villafuerte focuses upon the design, architecture and best practices that allows Power BI to offer the best functionality and performance combination. Throughout the session, we explore live demos of various large and diverse datasets and take advantage of Power BI latest features to achieve high performance and visual capabilities.

Event Date: March 2, 2019 7:30 am – 5:45 pm

ADDRESS:
Nova Southeastern University
Main Campus – Davie
3301 College Avenue
Davie, FL 33314

Categories
Datawarehouse Power BI SSAS

“Power BI for Large Databases with Composite Mode” at South Florida Power BI User Group

Power BI for Large Databases with new Composite Mode presentation by Hector Villafuerte at the South Florida Power BI User Group.

Today’s enterprise business analytics requires run complex analytic queries against large datasets stored in different sources like Azure SQL Database, Azure Analysis Services, Azure SQL Data Warehouse, Apache Hive, Apache Spark. In this session, BI Architect and Microsoft Certified Professional, Hector Villafuerte focuses upon the design, architecture and best practices that allows Power BI to offer the best functionality and performance combination. Throughout the session, we explore various large datasets and witness how to implement Power BI and different data technologies to achieve high performance and visual capabilities.

DATE: Thursday – October 4th, 2018
TIME: 6:00 pm

– ADDRESS:
Nova Southeastern University
Main Campus – Davie
3301 College Avenue
Davie, FL 33314

Categories
Datawarehouse Power BI SSAS Uncategorized

My presentation for SQLSATURDAY 755 South Florida Conference

This is the presentation for my session at SQL Saturday, South Florida, June 9 2018

Link to download PPT: Download Presentation

Categories
Power BI

.NET App Security (MVC, Web Forms, Web API) with SQL 2016 RLS

In this article, I’ll show the implementation of Row-Level-Security (RLS) with SQL 2016 and any .NET application like Web MVC, Web Forms, Web API, Windows App, etc. In previous articles, I showed you how to implement RLS in SQL 2016 and how client tools like Excel or Power BI can take advantage of this security. Now we’ll focus in .NET applications. Traditionally, we use to implement the security for data by coding the necessary logic using C# or VB.NET in the middle tier application.

Now, SQL 2016 allows the implementation of RLS, the security logic can be implemented right in the database, reducing the security code in the .NET middle tier app and centralizing the security in the source database, so any application or third party tool can connect to the database and reuse the same RLS security.

The following sample shows how to implement RLS in SQL to allow any .NET application with or without Active Directory or using another Security Membership Framework to query a SQL Server 2016 database and use RLS.

In order to do this, the .NET application should set values in the SESSION_CONTEXT after connecting to the database, and then security policies in the database will be able to read the values from SESSION_CONTEXT and filter rows using the Context values.

This article uses the same database I use in other articles.

In Visual Studio, Create Wasp – ASP .NET Project – Check ASP Forms and MVC. You can implement this in any .NET Windows or Web application: Console App, MVC, Web API, etc. For simplicity we’ll use Web Forms, but you might also implement this using Web API and use Angular for your web app.

Select MVC Template, Windows Authentication.

Add new item to the project: ADO.NET Entity Data Model, to the sample database. This will create a reference to Entity Framework. Call the model AdventureWorksModel.

Build the connection to the database.

For this sample, we’ll chose the tables DimCustomer and DimGeography.

You’ll get this model.

Create a Web Form called: TopCustomers.aspx. Add a RadGrid and ScriptManager Control to the Form. Add this code to code behind file TopCustomers.aspx.cs:

I add a Telerik Radgrid (You can add any control you want to display a list of customers) and the script manager.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace WebAppWithRLS

{

public partial class TopCustomers : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

AdventureWorksDW2014Entities db = new AdventureWorksDW2014Entities();

var topCustomers =

from customer in db.DimCustomers

join geography in db.DimGeographies on customer.GeographyKey equals geography.GeographyKey

select new { Name = customer.FirstName + " " + customer.LastName, geography.EnglishCountryRegionName };

RadGrid1.DataSource = topCustomers.ToList().Take(100);

RadGrid1.DataBind();

}

}

Set the TopCustomers.aspx page as “Start Page”

Run the application in Visual Studio.

The page shows records from Australia and Canada. Even though the user gwilliams belongs to the Active Directory group “AW United Kingdom” so it should retrieve customer records from England only.

The user gwilliams is logged to the ASP .NET application, even though the Web App becomes a middle tier application that connects to the database using another middle tier user. In visual studio is the user account executing running visual studio and in IIS is the AppPool configured user.

The way to resolve go around this scenario is to send the context values in our context session before executing the SQL query or command, so SQL 2016 RLS can read this values and apply the corresponding security.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data.Common;

using System.Data.Entity;

using System.Data.Entity.Infrastructure.Interception;

namespace WebAppWithRLS.Models

{

 public class SessionContextInterceptor: IDbConnectionInterceptor

 {

  public void Opened(DbConnection connection, DbConnectionInterceptionContext interceptionContext)

  {

   // Set SESSION_CONTEXT to current UserId whenever EF opens a connection

   try

   {

    var loginName = System.Web.HttpContext.Current.User.Identity.Name;

    if (loginName != null)

    {

     DbCommand cmd = connection.CreateCommand();

     cmd.CommandText = "EXEC sp_set_session_context @key=N'LoginName', @value=@LoginName";

     DbParameter param = cmd.CreateParameter();

     param.ParameterName = "@LoginName";

     param.Value = loginName;

     cmd.Parameters.Add(param);

     cmd.ExecuteNonQuery();

    }

   } catch (System.NullReferenceException)

   {

    // If no user is logged in, leave SESSION_CONTEXT null (all rows will be filtered)

   }

  }

  public void Opening(DbConnection connection, DbConnectionInterceptionContext interceptionContext)

  {

  }

  public void BeganTransaction(DbConnection connection, BeginTransactionInterceptionContext interceptionContext)

  {

  }

  public void BeginningTransaction(DbConnection connection, BeginTransactionInterceptionContext interceptionContext)

  {

  }

  public void Closed(DbConnection connection, DbConnectionInterceptionContext interceptionContext)

  {

  }

  public void Closing(DbConnection connection, DbConnectionInterceptionContext interceptionContext)

  {

  }

  public void ConnectionStringGetting(DbConnection connection, DbConnectionInterceptionContext < string > interceptionContext)

  {

  }

  public void ConnectionStringGot(DbConnection connection, DbConnectionInterceptionContext < string > interceptionContext)

  {

  }

  public void ConnectionStringSet(DbConnection connection, DbConnectionPropertyInterceptionContext < string > interceptionContext)

  {

  }

  public void ConnectionStringSetting(DbConnection connection, DbConnectionPropertyInterceptionContext < string > interceptionContext)

  {

  }

  public void ConnectionTimeoutGetting(DbConnection connection, DbConnectionInterceptionContext < int > interceptionContext)

  {

  }

  public void ConnectionTimeoutGot(DbConnection connection, DbConnectionInterceptionContext < int > interceptionContext)

  {

  }

  public void DataSourceGetting(DbConnection connection, DbConnectionInterceptionContext < string > interceptionContext)

  {

  }

  public void DataSourceGot(DbConnection connection, DbConnectionInterceptionContext < string > interceptionContext)

  {

  }

  public void DatabaseGetting(DbConnection connection, DbConnectionInterceptionContext < string > interceptionContext)

  {

  }

  public void DatabaseGot(DbConnection connection, DbConnectionInterceptionContext < string > interceptionContext)

  {

  }

  public void Disposed(DbConnection connection, DbConnectionInterceptionContext interceptionContext)

  {

  }

  public void Disposing(DbConnection connection, DbConnectionInterceptionContext interceptionContext)

  {

  }

  public void EnlistedTransaction(DbConnection connection, EnlistTransactionInterceptionContext interceptionContext)

  {

  }

  public void EnlistingTransaction(DbConnection connection, EnlistTransactionInterceptionContext interceptionContext)

  {

  }

  public void ServerVersionGetting(DbConnection connection, DbConnectionInterceptionContext < string > interceptionContext)

  {

  }

  public void ServerVersionGot(DbConnection connection, DbConnectionInterceptionContext < string > interceptionContext)

  {

  }

  public void StateGetting(DbConnection connection, DbConnectionInterceptionContext < System.Data.ConnectionState > interceptionContext)

  {

  }

  public void StateGot(DbConnection connection, DbConnectionInterceptionContext < System.Data.ConnectionState > interceptionContext)

  {

  }

 }

 public class SessionContextConfiguration: DbConfiguration

 {

  public SessionContextConfiguration()

  {

   AddInterceptor(new SessionContextInterceptor());

  }

 }

}

Create the class SessionContextInterceptor.cs and add the following code:

 

The class SessionContextInterceptor as the name suggest will intercept a SQL command from EF model and execute the code in the Opened method. Using sp_set_session_context we set the value of @LoginName variable. This variable will be alive and accessible form SQL Server only for that session.

Now, let’s implement the RLS in SQL. We create the predicate function which reads the context values using SESSION_CONTEXT(N’LoginName’).

USE [AdventureWorksDW2014]

GO

CREATE FUNCTION RLS.GeographyAccessPredicate(@GeographyKey AS INT)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN SELECT 1 AS AccessResult

FROM dbo.DimGeography G

INNER JOIN dbo.DimActiveDirectoryUser U

ON G.GeographyKey = @GeographyKey AND

(

(

U.LoginName = CAST(SESSION_CONTEXT(N'LoginName') AS nvarchar(150))

OR

U.LoginName = REPLACE(SUSER_NAME(), 'DOMAIN\', '')

)

AND

G.EnglishCountryRegionName = REPLACE(U.GroupName,'AW ','')

)

GO

CREATE SECURITY POLICY RLS.AWSecurityPolicy

ADD FILTER PREDICATE RLS.GeographyAccessPredicate(GeographyKey) ON dbo.DimCustomer

 

 

After that we run he program and now we can see only customers from United Kingdom, so the records are filtered by using the user name gwilliams.

In this article, I showed how RLS can be implemented regardless of the type of authentication (Database Custom Membership, Windows, Forms, OWIN, etc.) in our .NET application and type of .NET application: Console App, Window App, Web API, ASP .NET MVC/Web Forms, etc.

Categories
Power BI SSAS Uncategorized

My presentation for SQLSATURDAY 678 Orlando event

http://www.sqlsaturday.com/678/Sessions/Details.aspx?sid=66680

 

Categories
Datawarehouse Power BI

SQL Saturday Presentation – Data Security with Power BI, SSAS, SQL Server and Active Directory

Hector Villafuerte speaking on SQL Saturday South Florida

Saturday, Jun 10, 2017 Conference

http://hectorv.com/wp-content/uploads/sites/3/2017/06/RLS-SQL-Saturday-1.pptx

Categories
Power BI

Table or Matrix for Side by Side Column Comparison with columns with different formats in the same column.

Note: This article was updated with a new formatting feature released. See the updated version of this article:

http://hectorv.com/table-or-matrix-for-side-by-side-column-comparison-with-columns-with-different-formats-in-the-same-column-updated

 

Note: As of now, April 2017, Power BI does not support different formats for the same column in a Table or a Matrix. This article shows a work around to achieve different formats for the same column. This work around can be applied to a Table or to a Matrix Report Visual.

You can download this Power BI desktop file from this link: https://1drv.ms/u/s!AjlEiXFjD5fu2SehP8jKkcpwJacf

There are many cases where we need to use a table or a matrix and we need to specify different formats in the same column. Showing the format with the numeric values makes the report easier to read than a simple number with no units.

In the case of this financial report below, we can see three different formats in the same column:

Currency ($), Currency and Thousand ($K), and Percent (%).

Another example is a side by side comparison of products. In the case below, we have different formats like: text, mAh, decimal number, Whole number and Amp formats, all of them in the same column.

https://media.nngroup.com/media/editor/2017/02/24/amazon-powerbanks-2.png

I’ll show you this solution based on financial sample data downloaded from:

https://powerbi.microsoft.com/en-us/documentation/powerbi-sample-download-the-financial-sample-workbook/

This spreadsheet will be imported using Power BI desktop and you have to choose the worksheet: financial. Notice that this solution works for both: Power BI imported mode and live connection mode to SSAS Tabular. In the case of SSAS Tabular, you would have to import the excel spreadsheet in the Visual Studio Project.

I created a flag URL for the image, which is not part of the objective for this article. Even though, I’ll show you the calculated column formula created in the Financial Table:

 Flag = "https://image.ibb.co/" & SWITCH([Country],"France","d4vwba/fr.png","Mexico","h8uHhF/mx.png","Germany","nGmV2F/de.png","Canada","dsjuUv/ca.png","United States of America","c023NF/us.png")

The following table was easy to create. Each country has a row and we can do a row by row comparison.

Even though, this is not what we are looking for. We are trying to get a side by side column comparison having all country fields in one column. We can create a matrix moving the fields to the matrix sections: Column, Rows and Values as shown below:.

This is the Matrix Report version, after changing the flag and Country fields to the Column section in the Matrix. All fields under the configured Values section are shown as separate columns.

What I really want is only one column per country and also the columns header in the left side of the table as the very first column.

There are few challenges we have to overcome here:

  1. In Power BI, the visual table and matrix do not support custom value formatting on rows. We have different formats in the same column for the report we are trying to create:
  2. The Flag field has Image URL format.
  3. The Country field has Text format.
  4. The Unit Sold field has a Whole Number format.
  5. The Gross Sales and Profit field have Currency format.
  6. The Gross Profit Margin field has Percentage format.
  7. The order should be specific and we should have control over it. Meaning, if I need a different order in the rows, I should able to change it.

The Solution

This are the steps we need to create a comparison report with only one column per country,

  1. I need to create a ReportTable in Power Query. If you are using import mode in Power BI Desktop, you can use the Enter Data option in Power Query.

This ReportTable holds the structure, order and description of every row in the report, we do not include the Flag field, since we’ll use this field as report header.

If you are using Tabular SSAS with live connection, you can create the report table in your model by importing this from Excel or from a SQL Server database or you can also create the table with DAX using the following:

select 1 as [Index], 'Country' as [Description]

union all

select 2 as [Index], 'Units Sold' as [Description]

union all

select 3 as [Index], 'Gross Sales' as [Description]

union all

select 4 as [Index], 'Profit' as [Description]

union all

select 5 as [Index], 'Gross Profit Margin' as [Description]

 

Now, this is the trick: we need to create a Measures that will change dynamically based on the context of the row and it will calculate the corresponding measure with the correct format.

This measure will be created in the ReportTable.

ComparisonMeasure =

SWITCH(MAX(ReportTable[Index]),

1,

FIRSTNONBLANK(financials[Country],1),

2,

FORMAT(SUM(financials[Units Sold]),"###,###,###" ),

3,

FORMAT(SUM(financials[Gross Sales]),"$###,###,###"),

4,

FORMAT(SUM(financials[Profit]), "Currency"),

5,

FORMAT([Gross profit margin], "Percent"))

 

Then, we’ll use ReportTable[ComparisonMeasure] measure in the Values section of the Matrix. The Rows section will have the ReportTable[Description] field. Last, we’ll use the Financial[Flag] calculated column in the Columns section, as shown below.

This is how the report looks like now:

The table looks good. Side by side comparison with different formats in the columns was achieved! But there are still two issues with this Report.

  1. All the values are aligned to the left and Power BI does not have a feature to align column or cells in Tables nor in Matrices. The values in the columns are all text now, aligned to the left by default, so we need to align the currency, number and percentages to the right.
  2. Also, the order of the Matrix is alphabetically by ReportTable[Description].

Solution:

In order to fix the alignment, we’ll change the measure again and add empty spaces to force alignment to the right. This kind of alignment workaround is not pixel perfect, but we need to do this way until Power BI release a new alignment feature for table or matrix reports.

ComparisonMeasure =

VAR GrossSales=FORMAT(SUM(Financials[Gross Sales]),"$###,###,###")

VAR GrossSalesAligned=CONCATENATE(REPT(" ",20-LEN(GrossSales)),GrossSales)

VAR UnitsSold=FORMAT(SUM(Financials[Units Sold]),"###,###,###" )

VAR UnitsSoldAligned=CONCATENATE(REPT(" ",22-LEN(UnitsSold)),UnitsSold)

VAR Profit=FORMAT(SUM(Financials[Profit]), "$###,###,###")

VAR ProfitAligned=CONCATENATE(REPT(" ",20-LEN(Profit)),Profit)

VAR GrossProfitMargin=FORMAT([Gross profit margin], "Percent")

VAR GrossProfitMarginAligned=CONCATENATE(REPT(" ",23-LEN(GrossProfitMargin)),GrossProfitMargin)

RETURN

SWITCH(MAX(ReportTable[Index]),

1,

CONCATENATE(REPT(" ",4),FIRSTNONBLANK(Financials[Country],1)),

2,

UnitsSoldAligned,

3,

GrossSalesAligned,

4,

ProfitAligned,

5,

GrossProfitMarginAligned

)

 

After this change the Report looks better aligned.

Now in order to solve the order issue, we verify that the ReportTable[Index] field is selected as default in “Sort by Column” option.

Even though, after this the Matrix is still sorting by Description and not by Index. Then, I’ll create a calculated column in ReportTable, to concatenate the Index and Description in only one column:

 

SortedDescription = [Index] & " - " & [Description]

Now, the ReporTable shows the new column:

When SortedDescription replaces Description in the Rows section, the Matrix displays the correct sorting.

We add some slicers and we have both versions side by side matrix and simple table.

There is extra effort to create this kind of report in PowerBI. Even though, there are some cases where the side by side comparison in column and the formatted values are much easier to read than table with rows by row comparison. Especially, when there are multiple items or rows, it is easier for the end-user to compare columns side by side, where you can scroll up and down rather than scroll the table horizontally in a row by row comparison.

You can download this Power BI desktop file with this link: https://1drv.ms/u/s!AjlEiXFjD5fu2SehP8jKkcpwJacf