Power BI – Your model matters

Power BI – Your model matters

I’m trying to address a crucial topic (to me), but more complex to communicate than a technical feature.

DATA MODELING

This article gives you some tips and may give you the urge to look differently at your data. If so, my bet will be won, and you may significantly improve your Power BI trip.

Exciting, right? πŸ™‚

Introduction

Power BI is a fabulous tool that allows us to bring together the technical world of developers and end-users.

  • For a developer, it allows us to create beautiful visualizations and highlight the data easily.
  • For business users, it simplifies the technical work to focus mainly on the result.

Having worked at several clients, I am amazed by what users initially “non-technical” can achieve!

 

Users quickly become skilled in the tool, but sometimes lack a little methodology.

A habit, unfortunately quite common, is to multiply the models and then duplicate the data.

A question, a report corresponds to a data model. This is not always the case, but it’s quite often what I saw.

Several positive points to this:

  • Speed ​​of implementation
  • The size of the model reduced to the report need (Small size).
  • It covers the need.

But also, problems:

  • Duplicate data
  • Several different models and different logics/relations. Risk of having different results from one model to another.
  • Several models, more maintenance

As a BI developer, where the situation allows, I am a staunch defender of the unique model. With other Microsoft technologies, SSIS – SQL Server – SSAS Tabular / Multidim, we are trying to create what is called a Datawarehouse.

There are several methodologies for modeling this datawarehouse, but I will not dwell on it in this article.

A Datawarehouse is a data model that makes it easy to store, access, and understand your data.

Load Before, Think After

Two worlds existed and still exist.

BI (Developers / IT Service)

Let’s talk about me since that was the case. I juggled from one project to another by managing the business part as management of apple or pear. I did not bring much interest to the job or the nature of the data. Could I reproduce the reports asked? Yes! And I think everyone was happy like that.

Complex SQL queries, performance, calculations, rules implementation were my main concerns. I was the perfect example of developer 1.0.

 

Self BI (Users / Business)

The users I met have very different technical knowledge.

  • They have no particular interests or are panicked by the sources and structure of the data at their disposal.
  • They are obviously in a hurry to provide the reports fairly quickly

For all the reasons mentioned, when creating a new report, users are eager to load the data as is.

For both “worlds” the imperatives are:

  • Load the source data.
  • Provide a table or calculation as needed.

Methodologies – Quick Win

Without going into the details of the modeling of a Datawarehouse and, more precisely, the Kimball method, I want to dwell on two tips.

  • Denormalization

  • Brainstorming

The next two topics require the first change in your habits.

Do not keep the source tables as they are in your model

When you load your data into Power BI, you can make several changes in the Query Builder.

In addition to data typing, conditions, it is essential to consolidate the data.

This is the perfect introduction for Denormalization.

Denormalization

Normalization / Denormalization ?

In computer science, we tend to standardize information.

An example will be more concrete than a definition:

A product is available with a color.

A Color entity will be created and will contain all available colors.

In the source system, a drop-down list allow the user to pick up a color.

Often the main table retains a key (foreign key) that references the other table.

This way of exploding the model into multiple tables is called “normalization”.

For our reporting needs, we have to backtrack and consolidate information into fewer tables.

If we denormalize, our example could looks like this simple table.

  • Simple for your users
  • Simple for your model
  • Simple for your DAX measures

 

Concrete example

In this example, Bill tooks all the tables and files from his source system. He decided to load the table as is, without any changes.

It was easy for him and he knows very well his model. But his users came back to him with a ton of question regarding interactions. Which tables will be impacted if they filter one specific attribute. They were not really sure.

Since they have the “Sales Person” in their model, they wanted to know if Gender or Marital Status were link to “Sales Person” or “Client”. (So bill, renamed it to make it clear)

One day Bill wakes up with a giant smile! Is it because his favorite TV show is scheduled today? Not only! He has an idea… And a straightforward one.
He decided to group his data into fewer tables. (When there is no many to many relationships).
His work life changes to better!

  • Less complex DAX expression
  • More understandable datasets
  • Less support and more time to watch his TV show. (Yes, both are compatible!)

Brainstorming

The title should perhaps have been: Do not keep the focus on the technical problems but think of yourself.

We all tend to want technical challenges, it’s addictive, and it gives us the impression of moving forward! In our Professional world, Power BI is the equivalent of Candy Crush! But I have more pleasure in aligning beautiful DAX measures in a table than sweets, do not you? For this point, I invite you to step back and step against our technical world.

 

Ask yourself the following questions: (or ask them to your users)

My advice: Take a pen, paper, coffee, soft music. Disconnect from your computers, technical “worries”. Make sure your explanations can be understood by your wife, your husband, your friends, your sports coach, your children … and yourself …

  • What is the nature of your work? Describe it to me.
  • What are you doing within the company?
  • What indicators and reports do you consult?

Take note of the information in the form of keywords and mention the frequency. Some words will stand out and can be likened to what we will call later “Dimensions”.

    Your notes could look like this word cloud.

    See if some words do not relate to each other through an idea or logic.

    For example, [Customer Code] and [Customer Name] could be grouped under the same “Customer” dimension. (While these are potentially in two different tables or source files)

    The watchword here is: DO NOT THINK TO THE TECHNICAL ASPECTS. Without knowing it, you imagine the data model dreamed for you and your users. It fits your needs, your business, and does not care (yet) about the complexity needed for the data transformation!

    In this model, an end-user will find it much more comfortable and can even build their reports. Attributes will be displayed, grouped by logical ideas, and will facilitate reporting.

    The attractiveness of Power BI and BI Self-Service, in general, will not only be available to you, but it will also be accessible to end-users.

    Now that you’ve sketched out your dimensions, you can link them together.

    You certainly have information that expresses an event with measurable data. (Additive data)

    For example:

    • A sale with a price, a quantity.
    • An inscription with a volume, a frequency.

    The relationships mentioned above correspond to Facts.

    A fact table is defined by its grain, which is itself determined by the list of dimensions attached to it.

    The sale is made:

    • By a customer
    • For a product
    • In a store
    • On a given date
    • With a means of payment

    The inscription bears:

    • On a magazine
    • By a subscriber
    • For delivery to a specific address
    • With a subscription end date

    A brainstorming session can quickly give you a good overview of your next dataset.

    Your fact table is in the middle and dimensions around.

    Where is the technical complexity for your users? For your DAX measures? Your users can now keep the focus on their results and to the more complicated question: Do I really avoid to use a Pie Chart πŸ˜‰

    You can now look in detail how you will load your dimensions. Dimension by dimension asks you the question: Which is the grain of my dimension? Household, client, client history?

    And now start your candy crush session, load your data, and play a lot in the query Builder / SQL / … more …
    And do not forget to enjoy it, our work is exciting!

    How to put all this into music

    Power BI gives you the ability to consolidate your data into the Query Builder. (Power Query for the intimate)

    Do not minimize the time spent in this step, it will save you a lot later.

    You will be able:

    • Group your data (multiple source tables can be grouped) into a single table.
    • Clean up your data. (Filter unnecessary data, edit poorly formatted data) This step will provide more convenience to your users.
    • Add a type to your data.

    The interface is quite complete and allows you to perform all the desired fantasies. For performance reasons or more flexibility, you can achieve your work on data with SQL queries.

    I do not have doubts about your technical abilities to perform this task.

    The final word

    Take a step back on your technical problems, and nothing stops you! Take the time to rediscover your craft with an outside look! Power BI offers you the technical means to achieve this. And with a little methodology, you’re entering the big family of Datawarehouse’s happy modelers.

    Give me your feedback or your comments.

    I would be thrilled.

    Arnaud

     

    P.S. This article is a part of my session Power BI and Data modeling – Go to the Stars!

    Read more:

    https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

    https://en.wikipedia.org/wiki/Dimensional_modeling

    https://en.wikipedia.org/wiki/Data_warehouse#Dimensional_versus_normalized_approach_for_storage_of_data

    https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

    https://radacad.com/power-bi-basics-of-modeling-star-schema-and-how-to-build-it

     

     

    Management Studio – Faster with multiple select

    Management Studio – Faster with multiple select

    In SQL Server Management Studio (SSMS for close friends) but also in a multitude of text editors (such as Notepad++ for example), you can make multiple selections.

    I thought it was something everyone knew, but I realize that I often look like a magician every time I do it.

    I’m delighted to pretend to be Harry Potter, but I think it’s time for this little game to stop!

    Look by yourself how simple it is!

    How?

    You should press the keys [ALT] + [Shift] simultaneously and move your cursor [Up] and / or [Down] to select your text.

    One useful case

    Sometimes we have to surround our text with single quotes. (for example: to add multiple codes to an IN clause in a test query)

    No need to add them one by one and make sure you do not have space at the end.
    Here is the method:

    Like mentioned above, this tip is not an exclusivity in SSMS, you can also do the same in many different text editor.

    SSIS – Create Environment from Packages variables

    SSIS – Create Environment from Packages variables

    I created this script to automate some “not funny” tasks with our SSIS Catalog. If you have several SSIS projects configured in project mode (with a project.params file), when deploy them on your servers you unfortunately have to manually create the different environments.

    After deploying SSIS packages, you can run the following query and use the generated SQL code.

    the generated SQL script:

    • Create the different environment (Based on the name of the projects)
    • Create variables with default values (value available in packages)
    • Assigning Environments to Projects
    • Assign the environment variables to the project variables.

    The following code is not clean, but it does the work! πŸ™‚

      [TSQL]
      USE SSISDB;

      SET NOCOUNT ON;

      ——————-
      — Configuration
      ——————-
      DECLARE @FolderToExport VARCHAR(50) = ‘BI’

      DECLARE
      @folder_id INT
      ,@name VARCHAR(150)
      ,@project_id INT
      ,@sql VARCHAR(MAX) = ”
      ,@sqlTemp VARCHAR(MAX) = ”
      ,@cr CHAR(1) = CHAR(10)
      ,@tab CHAR(4) = SPACE(4)
      ,@ProjectName VARCHAR(50)
      ,@VariableName VARCHAR(150)
      ,@Value VARCHAR(600)

      SET @sql = ”;

      SET @sql += ‘DECLARE @ReturnCode INT=0, @folder_id bigint’ + @cr + @cr;
      SET @sql += ‘– ———————————————‘ + @cr;
      SET @sql += ‘–‘+ @tab + ‘Variable declarations, make any changes here’ + @cr;
      SET @sql += ‘– ———————————————‘ + @cr;
      SET @sql += ‘DECLARE ‘+ @cr;
      SET @sql += @tab + @tab + ‘ @folder sysname’ + @cr;
      SET @sql += @tab + @tab + ‘,@env sysname’ + @cr;
      SET @sql += @tab + @tab + ‘,@project sysname;’ + @cr;

      — Get Folder ID
      SELECT @folder_id = folder_id FROM [internal].[folders] WHERE name = @FolderToExport

      PRINT @SQL;
      SET @SQL = ”;

      ———————————————
      — Export Current package Configuration
      ———————————————
      DECLARE db_cursor_variable CURSOR FOR
      SELECT DISTINCT
      CONVERT(NVARCHAR(50), projects.name ) AS ProjectName
      ,CONVERT(NVARCHAR(150), projects.name +’__’+ parameter_name) AS VariableName
      ,CONVERT(NVARCHAR(600), ISNULL(design_default_value, ”) ) AS Value
      FROM internal.object_parameters main
      INNER JOIN internal.projects ON main.project_id = projects.project_id
      WHERE project_version_lsn = (SELECT MAX(project_version_lsn) FROM [SSISDB].[internal].[object_parameters] s WHERE s.project_id = main.project_id)
      AND parameter_name NOT LIKE ‘CM.%’
      AND projects.folder_id = @folder_id
      ORDER BY CONVERT(NVARCHAR(50), projects.name )
      ,CONVERT(NVARCHAR(150), projects.name +’__’+ parameter_name)
      ,CONVERT(NVARCHAR(600), ISNULL(design_default_value, ”))

      OPEN db_cursor_variable
      FETCH NEXT FROM db_cursor_variable
      INTO
      @ProjectName
      ,@VariableName
      ,@Value
      WHILE @@FETCH_STATUS = 0
      BEGIN

      PRINT ‘DECLARE @’+ @VariableName +’ NVARCHAR(600) = N”’+ @Value +”’;’;

      FETCH NEXT FROM db_cursor_variable
      INTO
      @ProjectName
      ,@VariableName
      ,@Value
      END

      CLOSE db_cursor_variable
      DEALLOCATE db_cursor_variable

      SET @SQL += ‘SET @folder = ”’+ @FolderToExport +””+ @cr + @cr+ @cr;

      — Begin transaction
      SET @sql += ‘;’ + @cr + ‘/* Starting the transaction */’ + @cr;
      SET @sql += ‘BEGIN TRANSACTION’ + @cr + @cr + @cr;

      —-
      —- Test if folder exist -> If not create it
      –SET @sql += @tab + ‘IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = @folder)’ + @cr;
      –SET @sql += @tab + ‘BEGIN’ + @cr;
      –SET @sql += @tab + @tab + ‘RAISERROR(”Creating folder: %s …”, 10, 1, @folder) WITH NOWAIT;’ + @cr;
      –SET @sql += @tab + @tab + ‘EXEC @ReturnCode = [SSISDB].[catalog].[create_folder] @folder_name=@folder, @folder_id=@folder_id OUTPUT’ + @cr;
      –SET @sql += @tab + @tab + ‘IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;’ + @cr;
      –SET @sql += @tab + ‘END’ + @cr + @cr;

      ———————————————
      — Create environment + variables
      ———————————————–
      DECLARE db_cursor CURSOR FOR
      SELECT [project_id], [name] FROM [internal].[projects] WHERE folder_id = @folder_id

      OPEN db_cursor
      FETCH NEXT FROM db_cursor
      INTO @project_id, @name

      WHILE @@FETCH_STATUS = 0
      BEGIN

      SET @sql += @tab + ‘SET @env = ”’+ @name +”” + @cr;

      SET @sql += @tab + ‘IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environments] WHERE name = @env)’ + @cr;
      SET @sql += @tab + ‘BEGIN’ + @cr;

      SET @sql += @tab + @tab +’RAISERROR(”Creating Environment: %s”, 10, 1, @env) WITH NOWAIT;’ + @cr;
      SET @sql += @tab + @tab +’EXEC @ReturnCode = [SSISDB].[catalog].[create_environment] @folder_name=@folder, @environment_name=@env’ + @cr;
      SET @sql += @tab + @tab +’IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;’ + @cr + @cr;

      SET @sql += @tab + ‘END’ + @cr;

      print @sql;
      SET @sql = ”;

      — ———————————————
      — Generate the variable creation
      — ———————————————
      SET @sql += ‘ — ProjectId => ‘+ CONVERT(NVARCHAR(50), @project_id) + @cr;
      SELECT DISTINCT [cmd] = @tab + ‘RAISERROR(” Creating variable: ‘ + parameter_name + ‘ …”, 10, 1) WITH NOWAIT;’ + @cr
      + @tab + ‘EXEC @ReturnCode = [SSISDB].[catalog].[create_environment_variable]’ + @cr
      + @tab + @tab + ‘ @variable_name=N”’ + parameter_name + ”” + @cr
      + @tab + @tab + ‘, @sensitive=’ + CONVERT(varchar(2), sensitive) + @cr
      + @tab + @tab + ‘, @description=N”’ + [description] + ”” + @cr
      + @tab + @tab + ‘, @environment_name=@env’ + @cr
      + @tab + @tab + ‘, @folder_name=@folder’ + @cr
      + @tab + @tab + ‘, @value=@’ + @name +’__’+ parameter_name + @cr
      + @tab + @tab + ‘, @data_type=N”’ + parameter_data_type + ”” + @cr
      + @tab + ‘IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;’ + @cr
      , [name] = parameter_name
      INTO #cmd
      FROM [SSISDB].[internal].[object_parameters]
      WHERE project_id = @project_id
      AND project_version_lsn = (SELECT MAX(project_version_lsn) FROM [SSISDB].[internal].[object_parameters] WHERE project_id = @project_id)
      AND parameter_name NOT LIKE ‘CM.%’

      WHILE EXISTS (SELECT TOP 1 1 FROM #cmd)
      BEGIN
      SELECT TOP 1 @sql = cmd, @name = name FROM #cmd ORDER BY name;
      PRINT @sql;

      DELETE FROM #cmd WHERE name = @name;
      END;

      DROP TABLE #cmd;
      SET @sql = ”;

      FETCH NEXT FROM db_cursor INTO @project_id, @name
      END

      CLOSE db_cursor
      DEALLOCATE db_cursor

      ———————————————
      — Set Environment on each Project
      — + Set Environment variables on each Project Variables
      ———————————————
      DECLARE db_cursor CURSOR FOR
      SELECT [project_id], [name] FROM [internal].[projects] WHERE folder_id = @folder_id

      OPEN db_cursor
      FETCH NEXT FROM db_cursor
      INTO @project_id, @name

      SET @sql = ‘DECLARE @reference_id AS BIGINT’+ @cr;
      PRINT @sql;

      WHILE @@FETCH_STATUS = 0
      BEGIN
      SET @sql = @tab + ‘SET @project = ”’+ @name +”’;’+ @cr;
      SET @sql += @tab + ‘SET @env = ”’+ @name +”” + @cr;
      SET @sql += ‘RAISERROR(”Configure Project: %s …”, 10, 1, @project) WITH NOWAIT;’ + @cr + @cr

      SET @sql += ‘– —————————————— ‘+ @cr
      SET @sql += ‘– Set Environment to Project ‘ + @cr
      SET @sql += ‘– —————————————— ‘+ @cr
      SET @sql += ‘RAISERROR(” Set Environment (%s) as Reference …”, 10, 1, @env) WITH NOWAIT;’ + @cr
      SET @sql += ‘EXEC SSISDB.catalog.create_environment_reference’+ @cr
      SET @sql += @tab + @tab + ‘@folder_name = @folder, — Folder (SSIDB >> “FolderName”)’+ @cr
      SET @sql += @tab + @tab + ‘@environment_name = @env, — Environment Name ‘+ @cr
      SET @sql += @tab + @tab + ‘@project_name = @project, — Project Name ‘+ @cr
      SET @sql += @tab + @tab + ‘@reference_type = ”R”,’+ @cr
      SET @sql += @tab + @tab + ‘@reference_id = @reference_id OUTPUT’+ @cr

      PRINT @SQL

      — ——————————————
      — Set Environment Variable to Project Variables
      — ——————————————

      IF OBJECT_ID(‘tempdb..#cmd2’) IS NOT NULL DROP TABLE #cmd2

      SET @sql = ‘– —————————————— ‘+ @cr
      SET @sql += ‘– Set Environment Variable to Project Variables ‘+ @cr
      SET @sql += ‘– —————————————— ‘+ @cr
      SET @sql = @tab + ‘SET @project = ”’+ @name +”’;’+ @cr;
      SET @sql += @tab + ‘SET @env = ”’+ @name +”” + @cr;
      PRINT @sql

      SELECT
      [cmd] =
      ‘RAISERROR(” Set Project variable (‘+ parameter_name +’) …”, 10, 1) WITH NOWAIT;’ + @cr
      + ‘EXECUTE [SSISDB].[catalog].set_object_parameter_value ‘+ @cr
      + @tab + @tab + ‘@object_type = ‘+ CONVERT(NVARCHAR(10), object_type) +’,’ + @cr
      + @tab + @tab + ‘@folder_name = @folder, — Folder (SSIDB >> “FolderName”)’+ @cr
      + @tab + @tab + ‘@project_name = @project, — Environment Name’+ @cr
      + @tab + @tab + ‘@parameter_name = N”’+ parameter_name +”’, — Project Variable Name’+ @cr
      + @tab + @tab + ‘@parameter_value = N”’+ parameter_name +”’, — Environment Variable Name’+ @cr
      + @tab + @tab + ‘@object_name = N”’+ object_name +”’,’+ @cr
      + @tab + @tab + ‘@value_type = ”R”’+ @cr
      ,[name] = parameter_name
      INTO #cmd2
      FROM [SSISDB].[internal].[object_parameters]
      WHERE project_id = @project_id
      AND project_version_lsn = (SELECT MAX(project_version_lsn) FROM [SSISDB].[internal].[object_parameters] WHERE project_id = @project_id)
      AND parameter_name NOT LIKE ‘CM.%’

      /*Print out the variable creation procs */
      WHILE EXISTS (SELECT TOP 1 1 FROM #cmd2)
      BEGIN
      SELECT TOP 1 @sql = cmd, @name = name FROM #cmd2 ORDER BY name;
      PRINT @sql;

      DELETE FROM #cmd2 WHERE name = @name;
      END;

      FETCH NEXT FROM db_cursor INTO @project_id, @name
      END

      CLOSE db_cursor
      DEALLOCATE db_cursor

      ———————————————
      — END!
      ———————————————
      SET @sql = ”;

      /* finsih the transaction handling */
      SET @sql += ‘COMMIT TRANSACTION’ + @cr;
      –SET @sql += ‘RAISERROR(N”Complete!”, 10, 1) WITH NOWAIT;’ + @cr;

      SET @sql += ‘RAISERROR(N” _____ _ _ _ _ ”, 10, 1) WITH NOWAIT;’ + @cr;
      SET @sql += ‘RAISERROR(N”/ __ \ | | | | | | |”, 10, 1) WITH NOWAIT;’ + @cr;
      SET @sql += ‘RAISERROR(N”| / \/ ___ _ __ ___ _ __ | | ___| |_ ___ __| | |”, 10, 1) WITH NOWAIT;’ + @cr;
      SET @sql += ‘RAISERROR(N”| | / _ \| ””_ ` _ \| ””_ \| |/ _ \ __/ _ \/ _` | |”, 10, 1) WITH NOWAIT;’ + @cr;
      SET @sql += ‘RAISERROR(N”| \__/\ (_) | | | | | | |_) | | __/ || __/ (_| |_|”, 10, 1) WITH NOWAIT;’ + @cr;
      SET @sql += ‘RAISERROR(N” \____/\___/|_| |_| |_| .__/|_|\___|\__\___|\__,_(_)”, 10, 1) WITH NOWAIT;’ + @cr;
      SET @sql += ‘RAISERROR(N” | | ”, 10, 1) WITH NOWAIT;’ + @cr;
      SET @sql += ‘RAISERROR(N” |_| ”, 10, 1) WITH NOWAIT;’ + @cr;

      SET @sql += ‘GOTO EndSave’ + @cr + @cr;
      SET @sql += ‘QuitWithRollback:’ + @cr;
      SET @sql += ‘IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION’ + @cr;
      SET @sql += ‘RAISERROR(N”Variable creation failed”, 16,1) WITH NOWAIT;’ + @cr + @cr;
      SET @sql += ‘EndSave:’ + @cr;
      SET @sql += ‘GO’;

      PRINT @sql;
      [/TSQL]

      SSMS – Query Shortcuts : Feel like a superman developer

      SSMS – Query Shortcuts : Feel like a superman developer

      SSMS Query Shortcut

      Dear BI Developer,

      I’m pretty sure you would be happy to improve your productivity. If not, you should at least read this article to look like a superman (superwoman) developer.

      When I start a new mission, the first thing I do is to set up SSMS (SQL Server Management Studio). And because I’m the kind of guy who acts like a Microsoft BI evangelist (and also for running), I replicate my configuration on my colleague’s machines.

      Example

      In this example – CTRL + 4 – COUNT

      Select statement you want to execute, Press CTRL and 4

      SSMS will give you Nb impacted rows.

      In BI (and not only!), it’s very important to test if we have unexpected behavior with our joins.

      Does our INNER JOIN filter too much data? Or worst, does our join multiply our result set?

      A quick and easy CTRL + 4 will ensure you to respect your grain.

      How to configure Management Studio

      Open Management Studio, Go to Tools > Option…

      Under Environment > Keyboard > Query Shortcuts

      You have a list of existing shortcuts. (I don’t change them, but I neither use them too).
      You should now fill each text box with a query.

      (See image and table below)

      Queries are available on the next section

      Query Shortcuts

      Do not forget to add a space after each query.

      Tips

      CTRL + 3

      1000 First Rows

      SELECT TOP 1000 * FROM

      CTRL + 4

      Nb Rows

      SELECT COUNT(1) AS Nb FROM

      CTRL + 5

      All Rows

      SELECT * FROM

      CTRL + 6

      Describe Table

      EXEC sp_executesql N' SELECT schemas.name ,tables.name ,columns.name ,types.name ,columns.max_length ,columns.is_nullable ,columns.is_identity FROM sys.tables tables INNER JOIN sys.schemas schemas ON schemas.schema_id = tables.schema_id INNER JOIN sys.all_columns columns ON columns.object_id = tables.object_id INNER JOIN sys.types types ON types.system_type_id = columns.system_type_id WHERE UPPER(RTRIM(LTRIM(tables.name))) = UPPER(RTRIM(LTRIM(REPLACE(REPLACE(@objname, '']'', ''''), ''['', '''')))) ORDER BY tables.object_id, columns.column_id',N'@objname nvarchar(776)', @objname =

      Select a table and show a quick description of attributes (Name, Datatype, size, nullable, identity)

      CTRL + 0

      All Running queries

       

      a better SP_Who! List all running queries (Process ID, Status (blocked or running), users, …)

      For more informations check this article

      SELECT SPID = er.session_id ,BlkBy = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END ,ElapsedMS = er.total_elapsed_time ,CPU = er.cpu_time ,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes ,Executions = ec.execution_count ,CommandType = er.command ,LastWaitType = er.last_wait_type ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,SQLStatement = qt.text ,STATUS = ses.STATUS ,[Login] = ses.login_name ,Host = ses.host_name ,DBName = DB_Name(er.database_id) ,StartTime = er.start_time ,Protocol = con.net_transport ,transaction_isolation = CASE ses.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'Read Uncommitted' WHEN 2 THEN 'Read Committed' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END ,ConnectionWrites = con.num_writes ,ConnectionReads = con.num_reads ,ClientAddress = con.client_net_address ,Authentication = con.auth_scheme ,DatetimeSnapshot = GETDATE() FROM sys.dm_exec_requests er LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt OUTER APPLY ( SELECT execution_count = MAX(cp.usecounts) FROM sys.dm_exec_cached_plans cp WHERE cp.plan_handle = er.plan_handle ) ec OUTER APPLY ( SELECT lead_blocker = 1 FROM master.dbo.sysprocesses sp WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses) AND sp.blocked = 0 AND sp.spid = er.session_id ) lb WHERE er.sql_handle IS NOT NULL AND er.session_id != @@SPID ORDER BY er.blocking_session_id DESC, er.logical_reads + er.reads DESC, er.session_id

      My discoveries: Useful links and articles regarding Microsoft Data Plaform

      My discoveries: Useful links and articles regarding Microsoft Data Plaform

      My discoveries

      I always wanted to start a blog post with my discoveries on the internet.
      It’s often the case, and I hope you use it a lot, we are searching for information on a search engine. 2 months later (sometime less), we face the same issue!
      Excepted if you are pretty smart (which it seems to not be my case) in 80% of case you lost your beautiful and useful article.
      First of all, this is why I’m listing my discoveries, but in the meantime if I can provide you some useful information my goal will be reached!

      Power BI

      [TSQL]
      let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
      let
      DayCount = Duration.Days(Duration.From(EndDate – StartDate)),
      Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
      TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
      ChangedType = Table.TransformColumnTypes(TableFromList,{{“Column1”, type date}}),
      RenamedColumns = Table.RenameColumns(ChangedType,{{“Column1”, “Date”}}),
      InsertYear = Table.AddColumn(RenamedColumns, “Year”, each Date.Year([Date])),
      InsertQuarter = Table.AddColumn(InsertYear, “QuarterOfYear”, each Date.QuarterOfYear([Date])),
      InsertMonth = Table.AddColumn(InsertQuarter, “MonthOfYear”, each Date.Month([Date])),
      InsertDay = Table.AddColumn(InsertMonth, “DayOfMonth”, each Date.Day([Date])),
      InsertDayInt = Table.AddColumn(InsertDay, “DateInt”, each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
      InsertMonthName = Table.AddColumn(InsertDayInt, “MonthName”, each Date.ToText([Date], “MMMM”, Culture), type text),
      InsertCalendarMonth = Table.AddColumn(InsertMonthName, “MonthInCalendar”, each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & ” ” & Number.ToText([Year])),
      InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, “QuarterInCalendar”, each “Q” & Number.ToText([QuarterOfYear]) & ” ” & Number.ToText([Year])),
      InsertDayWeek = Table.AddColumn(InsertCalendarQtr, “DayInWeek”, each Date.DayOfWeek([Date])),
      InsertDayName = Table.AddColumn(InsertDayWeek, “DayOfWeekName”, each Date.ToText([Date], “dddd”, Culture), type text),
      InsertWeekEnding = Table.AddColumn(InsertDayName, “WeekEnding”, each Date.EndOfWeek([Date]), type date)
      in
      InsertWeekEnding
      in
      CreateDateTable
      [/TSQL]

      Connect Power Shell to Power BI Desktop

      Β https://audministrator.wordpress.com/2018/11/18/powershell-accessing-power-bi-desktop-data-and-more/

      Evaluate the Column size in your model

      select * from $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS ORDER BY Dictionary_size DESC

       

      SQL Server

      [TSQL]
      SELECT
      SPID = er.session_id
      ,BlkBy = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END
      ,ElapsedMS = er.total_elapsed_time
      ,CPU = er.cpu_time
      ,IOReads = er.logical_reads + er.reads
      ,IOWrites = er.writes
      ,Executions = ec.execution_count
      ,CommandType = er.command
      ,LastWaitType = er.last_wait_type
      ,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid)
      ,SQLStatement = qt.text
      –,SQLStatement =
      — SUBSTRING
      — (
      — qt.text,
      — er.statement_start_offset/2,
      — (CASE WHEN er.statement_end_offset = -1
      — THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
      — ELSE er.statement_end_offset
      — END – er.statement_start_offset)/2
      — )
      ,STATUS = ses.STATUS
      ,[Login] = ses.login_name
      ,Host = ses.host_name
      ,DBName = DB_Name(er.database_id)
      ,StartTime = er.start_time
      ,Protocol = con.net_transport
      ,transaction_isolation =
      CASE ses.transaction_isolation_level
      WHEN 0 THEN ‘Unspecified’
      WHEN 1 THEN ‘Read Uncommitted’
      WHEN 2 THEN ‘Read Committed’
      WHEN 3 THEN ‘Repeatable’
      WHEN 4 THEN ‘Serializable’
      WHEN 5 THEN ‘Snapshot’
      END
      ,ConnectionWrites = con.num_writes
      ,ConnectionReads = con.num_reads
      ,ClientAddress = con.client_net_address
      ,Authentication = con.auth_scheme
      ,DatetimeSnapshot = GETDATE()
      FROM sys.dm_exec_requests er
      LEFT JOIN sys.dm_exec_sessions ses
      ON ses.session_id = er.session_id
      LEFT JOIN sys.dm_exec_connections con
      ON con.session_id = ses.session_id
      OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
      OUTER APPLY
      (
      SELECT execution_count = MAX(cp.usecounts)
      FROM sys.dm_exec_cached_plans cp
      WHERE cp.plan_handle = er.plan_handle
      ) ec
      OUTER APPLY
      (
      SELECT
      lead_blocker = 1
      FROM master.dbo.sysprocesses sp
      WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses)
      AND sp.blocked = 0
      AND sp.spid = er.session_id
      ) lb
      WHERE er.sql_handle IS NOT NULL
      AND er.session_id != @@SPID
      ORDER BY
      er.blocking_session_id DESC,
      er.logical_reads + er.reads DESC,
      er.session_id
      [/TSQL]

      SSAS Tabular

      List running queries – MDQ Query

      [TSQL]
      SELECT
      session_spid
      ,session_user_name
      ,session_last_command
      ,session_current_database
      ,session_cpu_time_ms
      ,session_elapsed_time_ms
      ,session_start_time
      ,session_last_command_start_time
      ,session_last_command_end_time
      FROM $system.discover_sessions
      WHERE session_status = 1
      ORDER BY session_start_time desc
      [/TSQL]

      Kill a running query – XMLA Query

      <cancel xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
      <spid>84895</spid>
      </cancel>

       

      SSIS – Log Execution time

      SSIS – Log Execution time

      This query helps you to have a better view of your SSIS Packages executions.

      When you run a “master job / package”, you don’t clearly see the execution time for your child packages in SSISDB Reports.

      With this query, now it’s possible πŸ™‚

      USE SSISDB
      GO
      
      SELECT
      	 package_name
      	,execution_id
      	,executable_id
      	,StartTime
      	,EndTime
      	,LastStatus
      	,execution_result
      	,RowIndex
      	,DATEDIFF(minute, StartTime, EndTime) AS 'execution_time[min]'
      FROM
      (
      	SELECT DISTINCT
      		 executables.package_name AS package_name
      		,executables.execution_id AS execution_id
      		,executables.executable_id AS executable_id
      		,FIRST_VALUE(start_time) OVER(PARTITION BY executables.package_name, executable_statistics.execution_id ORDER BY start_time ASC) AS StartTime
      		,FIRST_VALUE(end_time) OVER(PARTITION BY executables.package_name, executable_statistics.execution_id ORDER BY end_time DESC) AS EndTime
      		,FIRST_VALUE(execution_result) OVER(PARTITION BY executables.package_name ORDER BY end_time DESC) AS LastStatus
      		,execution_result AS execution_result
      		,ROW_NUMBER() OVER(partition by executables.package_name ORDER BY end_time DESC) AS RowIndex
        	  FROM    [catalog].[executables]
      	LEFT JOIN [catalog].[executable_statistics] ON
      														executable_statistics.execution_id = executables.execution_id
      													AND executable_statistics.executable_id = executables.executable_id
      
      	--WHERE executables.package_name = 'YourPackage.dtsx'
      ) A
       WHERE a.RowIndex = 1
       ORDER BY StartTime ASC

      Pin It on Pinterest