SSAS: get Cube Metadata

SSAS Dynamic Management Views (DMV’s) are very useful to query metadata of a cube. For developers, this is quite handy to provide simple documentation for the cubes they build. Some examples :

Continua a leggere

Annunci

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