Handle DistinctCount counts NULL

Analysis Services handles NULL and 0 as the same thing, and 0 would normally be considered something worth counting, whereas NULL wouldn’t.

When you count for example the sex of  distinct customer, you dont’ want to count who not have sex, (the null value). you have to do these:

  1. Create a new column on the fact table that indicates whether the field we want to distinctcount is null. To do this we add a new column and give it the fixed value 1 when the Attribute is empty.
  2. Create three measures
    • DistinctOriginal: Standard measure, DistinctCount on attribute, hidden measure
    • IsAttributeNumberNull:Standard measure, MAX on isAttibuteNull, hidden measure
    • DistinctAttribute: Calculated measure, Formula: DistinctOriginal – IsAttributeNumberNull

And that’s it, use the new calculated measure for a distinct count that does not count NULLs.

SQL – Dynamic Sorting

Users like to be in control. They want to define custom columns, sort orders, and basically drag, drop, and pivot chart their way to victory. i this specific case user want to order sales of a specific reporting services report based on different date. This is the specific query:

SELECT SalesOrderNumber, OrderDate, DueDate, ShipDate,

PurchaseOrderNumber, AccountNumber, SubTotal,

TaxAmt, Freight, TotalDue

FROM Sales.SalesOrderHeader

The user want to order data by: ORder date, Due date or shipdate.

To do this we use a stored procedure where we read the selected time attribute and give a query order by it.

The specific query it is :

DECLARE @SortOrder VARCHAR(50) = ‘[Time attribute selected]’;

SELECT rn,

 [list of attributes]

FROM (

SELECT CASE @SortOrder

WHEN ‘OrderDate’ THEN ROW_NUMBER() OVER (ORDER BY OrderDate DESC)

WHEN ‘DueDate’ THEN ROW_NUMBER() OVER (ORDER BY DueDate DESC)

WHEN ‘ShipDate’ THEN ROW_NUMBER() OVER (ORDER BY ShipDate DESC)

END AS rn,

[list of attributes]

FROM Sales.SalesOrderHeader

) AS x

ORDER BY rn ASC;

With this symple query you can order dataset with dynamic variable.

SQL – Replacing unwanted characters

Sometimes when you work with raw data or input by user it’s possible to have some problem with special characters. To resolve these problems we use a special feature of SQl server:  SQL CLR assembly

  1. First step is open visual studio and creating a new class library project with this code:
using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Text.RegularExpressions;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public class RegEx

{

[Microsoft.SqlServer.Server.SqlFunction

(IsDeterministic = true, IsPrecise = true)]

public static SqlString ReplaceIfMatch

(SqlString ip, SqlString exp, SqlString replace)

{

// some error handling

if (exp.IsNull || ip.IsNull || replace.IsNull)

return  SqlString.Null ;

Regex r = new Regex(exp.ToString());

//bool match = r.Match(ip).Success;

return new SqlString( r.Replace(ip.ToString(),

replace.ToString()));

}

[Microsoft.SqlServer.Server.SqlFunction

(IsDeterministic = true, IsPrecise = true)]

public static SqlBoolean IsMatch

(SqlString ip, SqlString exp)

{

// some error handling

if (exp.IsNull || ip.IsNull )

return SqlBoolean.False;

Regex r = new Regex(exp.ToString());

return new SqlBoolean( r.Match(ip.ToString()).Success);

}

// repeating for normal windows

public static string ReplaceIfMatchN

(string ip, string exp, string replace)

{

// some error handling

if (string.IsNullOrEmpty(exp)

|| string.IsNullOrEmpty(ip)

|| string.IsNullOrEmpty(replace))

return string.Empty;

Regex r = new Regex(exp);

return r.Replace(ip, replace);

}

}

Save the class library as dll in a specific folder. after that open management studio and select your db, and launch this script:

USE AdventureWorks2008R2;

GO

EXEC SP_CONFIGURE ‘clr enabled’,1;

GO

RECONFIGURE;

GO

After that reconfigure Sql server and assembly, specific privous path where you are saved the file:

CREATE ASSEMBLY

RegExBase

FROM

‘D:\<PATH>\RegExBase.dll’

WITH PERMISSION_SET = SAFE ;

GO

Last step i to create a function to use the assembly :

CREATE FUNCTION

RegExReplace

(@ip NVARCHAR(4000),@exp NVARCHAR(4000), @replaceNVARCHAR(4000) )

RETURNS NVARCHAR(4000)

AS EXTERNAL

NAME RegExBase.RegEx.ReplaceIfMatch ;

GO

 

Now we can invoke the function and see the result.

Power bi Movies Analytics

Browsing internet i found that i can recover information about movies directly from imdb and Rotten tomatoes. Using a specific site i have created a power bi Report very interesting.

The site to retrive these infos is : omdbapi.com

I used a simple API to retrive different information about the top 100 movies of 2015-14-13 and some movies fo 2016. I found for example plot, imdb rating, rottenmeter, box office, date of release, genre, number of review and i create two different example:

  1. A general dashboard where i can see Average of reviews and Number of Movies:

Immagine 064.png

2. A specific film where i can select a Movie and i can see review meter , director, plot and genre:

Immagine 065.png

To try the analytics go to this link: Power BI Movie Analytics

 

Power BI April Update

Welcome back to Power BI News Resume. This Month we have some news on Mobile App and Some on Power BI services.

Mobile App:

  • SSRS ON Android: Microsoft announce a preview of the Power BI app for Android, with support for SQL Server 2016. View your mobile reports and KPIs using SSRS folder navigation and access your data quickly by using the favorites section.
  • Windows 10 Live Tiles: The Power BI app for Windows 10 already supports Live Tiles as a shortcut to dashboards, but with this update we can display actual metrics on our Start screen
  • new report gallery on Android: This update allowing you to access your reports directly from the main menu.
  • WIndows 10 Presentation: With this update Clicking on the full screen icon will expand the app across all of the available screen, and the navigation bar can be collapsed to reveal even more screen space. Full screen mode makes it easier for you and your viewers to focus on the presentation
  • global search and recent to iOS: This new add allows you to search for dashboards, reports, and groups by name. Simply begin entering your search terms into the input field, and results will instantly appear.

Power Bi Service:

  • ExpressRoute: ExpressRoute is an Azure service that lets you create private connections between Azure datacenters and your on-premises infrastructure, or create private connections between Azure datacenters and your colocation environment. From now  you can create a private network connection from your organization to Power BI
  • Row-Level Security: With this change, if RLS is defined for those dashboards and reports that are distributed as part of a content pack, then the security rules will be respected for all instantiated content packs.
  • Vimeo Video: Now the video tile supports Vimeo videos too.
  • Analyze excel : now the analyze in excel feature is avaible for all user, free and pro.
  • Improved multi-user account experience: If you accidently entered in the credentials for a different account, you will get a sign-in failure message and a chance to try signing in agai

Power bi Tiles add-in Office

Few week ago i have spoke about a workaround to create a complete pdf from power bi. Today i found a new and more interesting solution .

On this site you found a new add-in that allow you to embed PowerBI visualizations inside an Office Document, for example a PowerPoint presentation. For now it’s supporde by:

  • Excel 2013 Service Pack 1 or newer
  • Excel Online
  • PowerPoint 2013 Service Pack  or newer
  • PowerPoint Online
  • Access Online

it’s Very simple to use:

  1. Open an Office Program, in our case Powerpoint
  2. Go to the “Insert “Tab adn select Store Button

Immagine 055

After that search “Power bi tile” on search tab

Immagine 056.png

and activate it

Immagine 057.png

After That the app create a ne object. Frome the object you can :

  • Select from power BI and you can see your power bi Report ( from your subscrition office 365)
  • Select Publich Dashboard to insert a specific link

If select from power bi you have this situation:

 

Immagine 058.png

You can Select a dashboard ( and it show you a componet fo dashboard like mobile app) or a specifi report. If select a report this ios the result:

Immagine 059.png

Filter and object connection works! a very simple way to create a dynamic presentation of your data. REquire internet connection, but works Great!

 

 

 

ssisproject.ispac Loading Error

Sometimes when a SSIS package on  visual studio on debug mode crash after that we have this error:

ispacerror01

ispac file in use by other proces

System.IO.IOException: The process cannot access the file ‘c:\folder\ssisproject.ispac’ because it is being used by another process.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.File.Delete(String path)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow)

To resolve this problem you have to :

  1. Open task manager
  2. Finf operation with these names:
    1. DtsDebugHost.exe
    2. SSIS DEbug Host
    3. SSIS DEbug
  3. Kill them

This happens when SSDT/Visual Studio crashes during runtime. If you kill SSDT then the SSIS Debug Host will still be active locking the ISPAC file.