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

PowerBI – Bookmarks

PowerBI – Bookmarks

Bookmarks are not perfect, I can easily confirm you. But with them, you can build beautiful and rich reports.

In this example, I will show you how your users can switch from one visual to another one.

Pin It on Pinterest