New Power BI features shown at the Worldwide Partner Conference 2014 (WPC)

A few days back, Microsoft held the yearly Worldwide Partner Conference and they demonstrated a few new features of Power BI.

Watch the demo via:
http://www.digitalwpc.com/Videos/Pages/Videos.aspx

A real-time Salesforce dashboard was generated:
power-bi-partner-conference-2014-real-time-salesforce-demo

A new donut chart (bottom-left):
power-bi-partner-conference-2014-donut-chart

A Tree Map (bottom-left):
power-bi-partner-conference-2014-tree-map

Chris Webb discusses the new features in his new blog post:
http://cwebbbi.wordpress.com/2014/07/14/new-power-bi-features-shown-at-wpc/



Join us at LinkedIn!
Don’t forget to join the ‘Microsoft Power BI‘ group on LinkedIn and stay updated!:
linkedin-microsoft-power-bi-group








Note to myself, DO NOT USE a ‘Data Conversion’ transformation component in SSIS…:S

When it seems too easy with SSIS, you’ll probably need to fix it afterwards 🙂
Today in a blurry moment I didn’t think twice about using a ‘Data Conversion‘ transformation component and look what happened during processing:

ssis-2012-data-conversion-data-value-cannot-be-converted-for-reasons

The data value cannot be converted for reasons other than sign mismatch or data overflow.


A very vague error message that doesn’t really help. Googling doesn’t help either:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7307def1-79d3-4203-bec0-ba8d7311c30c/ssis-convertion-function-from-integer-to-string
http://stackoverflow.com/questions/11990178/date-casting-issue-in-data-conversion-task

One suggestion, delete the ‘Data Conversion’-component and drag a ‘Devired Column’-component onto your data flow plane. That makes life easier.
Most common things are blanc/white spaces or dot (‘.’) versus comma (‘,’) issues:

(DT_UI4)LTRIM(RTRIM())
(DT_NUMERIC,19,4)REPLACE(,".",",")








SSIS: Delete old files with PowerShell (via an ‘Execute Process Task’) #FileRetentionPeriod

Today I wanted to delete old files from certain folders based on a given retention period of 60 days. In my case the folders ‘Processed’ and ‘Incorrect’.
A while a go I wrote a PowerShell maintenance script that just loops through a list of pre-defined paths/folders and deletes files which are older than the given number of days…but I realised today, why not execute that similair PowerShell-script within SSIS?
Well…here’s how you could do that and in fact it’s easier that you might think!

In my example I made a Squence Container at the end of my Control Flow in SSIS which holds all the Process Tasks that delete the so called ‘old files’:

Delete-old-files-ssis-process-task-powershell-sequence-container

First step to do is to create two variables:

———————————————————————————————
@[User::ProcessedFolder] (or any other name) = "D:\DWH\Import\"
@[User::PowerShellProcessedArguments] = "ls -Path '"+ @[User::ProcessedFolder] +"' -Filter '*.csv' |? {$_.LastWriteTime -lt (get-date).AddDays(-60)} | Remove-Item"
———————————————————————————————
IMPORTANT: Make sure to modify the extention-property above in case you want to delete other types of files.

Sub-folders and files:
In case you would like to have it to also look at sub-folders, add ‘-Recurse‘ just after the extention-argument, for example:
"ls -Path '"+ @[User::ProcessedFolder] +"' -Filter '*.csv' -Recurse |? {$_.LastWriteTime -lt (get-date).AddDays(-60)} | Remove-Item"

Test run (-WhatIf):
In case you would like to verify the syntax, just add ‘-WhatIf‘ at the end, for example:
"ls -Path '"+ @[User::ProcessedFolder] +"' -Filter '*.csv' -Recurse |? {$_.LastWriteTime -lt (get-date).AddDays(-60)} | Remove-Item" -WhatIf"


Next, drag an ‘Execute Process Task‘ to your Control Flow. Now double click on it to open the editor and go to ‘Process‘. Set these properties:

Execute Process Task Editor -> Process properties:
———————————————————————————————
Executable = C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Arguments = (blank)
You can leave this one blank (will be filled by an expression during execution, see below).

WindowStyle = Hidden
Otherwise it will pop-up a PowerShell-window everytime it executes one of these. It will now pop-up a single command-window for less than a second.
———————————————————————————————


It should now look like (perhaps except the Arguments-property):
Delete-old-files-ssis-process-task-powershell-editor

Now go to ‘Expressions‘ and make sure that the ‘Arguments‘-property will be given the value of @[User::PowerShellProcessedArguments]:
Delete-old-files-ssis-process-task-powershell-expressions

That’s it!
Now run it a couple of times 🙂


I’ve used SQLCMD before to delete old files via a stored procedure, but this is a bit dangerous due to the fact that it requires you to enable SQLCMD:
https://clinthuijbers.wordpress.com/2011/08/04/ssis-sqlcmd-delete-old-txt-files/

Or use a single PowerShell script to delete all files in specific folders during maintenance?
https://clinthuijbers.wordpress.com/2014/07/09/powershell-delete-old-files-based-on-a-given-retention-period-days-maintenance/

More things you could do with PowerShell? Click here.








PowerShell: Delete old files based on a given retention period (days) #maintenance

How to delete old files based on a given retention period (days) with PowerShell?
Well…it’s easy! And you might want to use it to clean-up a few old backup files (or even *.csv *.sql or *.txt).

Basically it consists of two functions, namely ‘DeleteOldFilesWithExtension‘ and ‘DeleteOldFiles‘ (without looking for certain extensions):

PowerShell-delete-old-files

Code:
———————————————————————————————

function global:DeleteOldFilesWithExtension([string]$Path, [int]$Daysback, [string]$Extension)
{ #Begin Function

#$Daysback = "-7"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($Daysback)
#$Path = "D:\PowerShell\SQLScripts"

#Based on Creation Date:
#Get-ChildItem $Path -Include $Extension -Recurse | Where-Object { $_.CreationTime -lt $DatetoDelete } | Remove-Item -Confirm:$false -force -recurse

#Based on Modified Date:
Get-ChildItem $Path -Include $Extension -Recurse | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item -Confirm:$false -force -recurse

} #End Function

function global:DeleteOldFiles([string]$Path, [int]$Daysback)
{ #Begin Function

#$Daysback = "-7"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($Daysback)
#$Path = "D:\PowerShell\SQLScripts"

#Based on Creation Date:
#Get-ChildItem $Path -Recurse | Where-Object { $_.CreationTime -lt $DatetoDelete } | Remove-Item -Confirm:$false -force -recurse

#Based on Modified Date:
Get-ChildItem $Path -Recurse | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item -Confirm:$false -force -recurse

} #End Function

DeleteOldFiles "D:\PowerShell\SQLScripts" -7
DeleteOldFilesWithExtension "D:\PowerShell\BackupScripts" -7 "*.sql"

———————————————————————————————


The bottom two lines are actually calling/executing the functions:
(FunctionName) (Path/Folder) (RetentionPeriodInDays) (ExtentionType – optional)

So save the scripts noted above as ‘DeleteOldFiles.ps1‘ and you’re able to execute it via a SQL Agent Job or from the command line.

How to execute?:
Execute this statement in Command (CMD) or via a SQL Agent Job (PowerShell step):

sql-agent-powershell-step-delete-old-files

PowerShell "E:\PowerShell\DeleteOldFiles.PS1"


Other PowerShell scripts
Script all SQL database objects with PowerShell?
https://clinthuijbers.wordpress.com/2012/09/06/use-powershell-to-script-all-database-objects/

More things you could do with PowerShell? Click here.








Microsoft Research reveals Holograph, brings ‘Star Wars’ Leia hologram to life

Microsoft Research Talks About Holograph, An Interactive, 3-D Data-Visualization Research Platform

Microsoft Research reveals Holograph, brings ‘Star Wars’ Leia hologram to life

Microsoft-holographic-display

All those cool 3D holographic interactive user interfaces that people see in sci-fi movies and TV shows are finally getting closer to reality. Microsoft Research recently showed off a new project called Holograph that was made to render large data, not just in 3D, but in a way that it looks like it is being projected above a display.

The video also has some fun in showing off how Holograph can make the dreams of sci-fi fans come true, such as one scene where an interactive Viper fighter in “Battlestar Galactica” is created in 3D. The final part of the video shows the famous Princess Leia hologram in “Star Wars” (“You’re my only hope.’) recreated in 3D via Holograph. Perhaps the whole 3D chess game in that same movie could be the next project for Microsoft Research.


Holograph enables users to examine various types of dynamic data sets—easily and naturally.

“If you have 3-D data, you could move your head around and look at things as if they were physically there,” Wong suggests. “That’s a natural way of observing something. If you want to reach in and select something that’s interesting, you could find out more information. Sure, you could do that on a flat screen, but on really complex, non-two-dimensional data, it becomes more of a challenge.

“What we’re doing with our Perceptive Pixel display and things like Kinect is tracking where your head is, where your point of view is, and dynamically altering that data. The touch capabilities of the Perceptive Pixel display allow us to understand where you’re touching and then have that touch point connect with a piece of 3-D data represented below the plane of that screen. We’re rendering 3-D data both statically and dynamically in what looks like a virtual box below the glass, but you can see the dimensionality of the data.”