The other day somebody asked me how I could implement Slowly Changing Dimension Type 2 (SCD Type 2) in SSAS. I wasn’t able to give the answer right there and then, so I decided to create a demo video to answer his question and as a note to myself for future uses.
This is my first ever demo video by the way and I agree that it could have been better in many parts. I do hope that despite my amateur recording and editing skills, I am still able to send the message across. 🙂
Where I come from it is taboo to talk about death. Even your closest family and friends wouldn’t want to know how you prefer to die. In fact death is such an unacceptable fact of life that the mere act of talking about it is considered as “bad luck”.
In Being Mortal, Atul Gwande walks us through the reality of mortality – through old age and through terminal illness. As an American of Indian descent, he talks about how America as a society takes care of its aged population. As a surgeon, he shares how patients with terminal illness and their families dealt with their situations.
The book touches on a topic not many others dare to discuss; it raises questions others are unable to ask before. What is a decent way to leave this place, for instance? What is a decent way to live in it? How far would you go to prolong a loved one’s life? Would you rather live a life in constant pain or to let go of both?
As much as Being Mortal is thought-provoking, it is also well written and organized. Perhaps because the author is a man of science that he is careful to distinguish facts from opinion. Unless you are a God or a Demi-God who lives on ambrosia, then this book is worth reading.
Would I recommend this book to a friend? Yes.
Continuing: Automate SSIS 2012 Project Deployment and Configuration Management using PowerShell – Part 2
Download SSISProject containing:
Last and definitely NOT the least, where all the magic happens – SimpleSSISDeploy.ps1.
I put comments where needed, and you can download a zip of all the components, as well as a sample SSIS project at the bottom of this post. 🙂
param ([string]$ConfigurationsPath,
[string]$IspacPath,
[string]$ParametersXml,
[string]$Environment)
# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;
function Main()
{
# Input validation
if (-Not(Test-Path($ParametersXml)))
{
throw New-Object System.ArgumentException "Parameters.xml not found"
return
}
#Get configurations from *.dtproj.user
[xml]$configFile = Get-Content $ConfigurationsPath
$configurationsNode = $configFile.SelectNodes("/DataTransformationsUserConfiguration/Configurations/Configuration")
# Get configuration for $Environment parameter
$configurationsNode | % {
if ($_.Name -eq $Environment)
{
$serverName = $_.Options.ServerName
$pathOnServer = $_.Options.PathOnServer
}
}
if ([string]::IsNullOrEmpty($serverName) -or [string]::IsNullOrEmpty($pathOnServer) -or
$serverName -eq $null -or $pathOnServer -eq $null )
{
throw New-Object System.ArgumentException "Could not connect to Server: $serverName. Does it really exist?"
return
}
# Get catalog, folder and project name from $pathOnServer
$catalogConnectionString = "Data Source=" + $serverName + ";Initial Catalog=master;Integrated Security=SSPI;"
$path = $pathOnServer.Split("/", 4)
$catalogName = $path[1]
$folderName = $path[2]
$projectName = $path[3]
if ([string]::IsNullOrEmpty($catalogName) -or
[string]::IsNullOrEmpty($folderName) -or
[string]::IsNullOrEmpty($projectName))
{
throw New-Object System.ArgumentException "Check that $Environment build configuration is set correctly in the SSIS project."
return
}
# Connect to the SSIS Server
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $catalogConnectionString
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection
if ($integrationServices -eq $null)
{
Write-Host "Unable to connect to Integration Services Catalog."
return
}
# Get the existing catalog if it exists
if ($integrationServices.Catalogs.Contains($catalogName))
{
Write-Host "$catalogName catalog found"
$catalog = $integrationServices.Catalogs[$catalogName]
}
else
{
Write-Host "Could not find "$catalogName" Catalog. Are you sure you have the correct name?"
return
}
# Get catalog folder
if ($catalog.Folders.Contains($folderName))
{
Write-Host "$folderName catalog folder found"
$folder = $catalog.Folders[$folderName]
}
else
{
Write-Host "Could not find $folderName catalog folder. You are almost there."
return
}
# Read the project file, and deploy it to the folder
[byte[]] $projectFile = [System.IO.File]::ReadAllBytes($IspacPath)
$project = $folder.DeployProject($projectName, $projectFile)
# Get project
if ($folder.Projects.Contains($projectName))
{
Write-Host "$projectName project found"
$project = $folder.Projects[$projectName]
} else {
Write-Host "$projectName project not found. Sorry :("
return
}
# Get function parameters from file
Write-Host "Reading from Parameters.xml"
[xml]$file = Get-Content $ParametersXml
Update-Parameters $project $file
}
function Update-Parameters($project, $file)
{
# Update Project Parameters
$projectParameters = $file.SelectNodes("/SSIS/" + $Environment + "/ProjectParameters/Parameter")
$projectParameters | % {
$parameter = $_.Name
if ($parameter -eq $null)
{
continue
}
if ($project.Parameters.Contains($parameter))
{
Write-Host "$parameter project parameter found"
$project.Parameters[$_.Name].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$_.InnerText)
Write-Host "$parameter project parameter value updated"
}
else
{
Write-Host "$parameter project parameter NOT FOUND"
}
}
Write-Host "Updating parameters of" $project.Name "project successful"
# Update Package Parameters
$packages = $file.SelectNodes("/SSIS/" + $Environment + "/Packages/Package")
$packages | % {
$packageName = $_.Name
$parameters = $_.Parameters.ChildNodes
if ($project.Packages.Contains($packageName))
{
Write-Host "$packageName package found"
$ssisPackage = $project.Packages[$packageName]
foreach ($param in $parameters){
$paramName = $param.Name
$paramValue = $param.InnerText
if ($ssisPackage.Parameters.Contains($paramName))
{
Write-Host "$paramName package parameter found"
$ssisPackage.Parameters[$paramName].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$paramValue)
Write-Host "$paramName package parameter value updated"
}
else
{
Write-Host "$paramName package parameter NOT FOUND"
}
}
Write-Host "Updating parameters of $packageName package successful"
$ssisPackage.Alter()
}
else
{
Write-Host "$packageName package NOT FOUND"
}
}
$project.Alter()
}
Main
.\SimpleSSISDeploy.ps1 -ConfigurationsPath "F:\PROJECTS\SSISProject\DeploymentDemo\DeploymentDemo\DeploymentDemo.dtproj.user" -IspacPath "F:\PROJECTS\SSISProject\DeploymentDemo\DeploymentDemo\bin\Development\DeploymentDemo.ispac" -ParametersXml "F:\PROJECTS\SSISProject\DeploymentDemo\DeploymentDemo\Parameters.xml" -Environment "Development"
Continuing Automate SSIS 2012 Project Deployment and Configuration Management using PowerShell – Part 1
Download SSISProject containing:
Before SQL Server 2012, configuration data for SSIS package such as server names, database names, and SMTP servers can be stored in XML files, environment variables, or a separate configuration database. With the new Parameters feature of SSIS catalog, managing configuration data across your environments has never been so intuitive. Instead of updating a database or a cryptic XML file, someone could simply login to the SSIS catalog via SSMS and configure parameter values manually. Although configuring an SSIS project directly in Production may seem convenient and give anyone a demi-God feeling, it is, however, very risky and is prone to human error.
To continuously integrate our SSIS project, we can automate the task of updating parameter values after the *.ispac is successfully deployed. To do that, we will use an input file, Parameters.xml, to tell our PowerShell script what value to set each parameters with depending on which environment it is deploying to. We will add this file within the SSIS Project itself, so that
Enough of the intro, here’s exactly what you need to do:
<SSIS>
<Development>
<ProjectParameters>
<Parameter Name="InitialCatalog">AdventureWorksDWH2012</Parameter>
<Parameter Name="ServerName">SQLSSISDEV</Parameter>
</ProjectParameters>
<Packages>
<Package Name="MainPackage.dtsx">
<Parameters>
<Parameter Name="SourceFolderRelativePath">\\Source\Folder\RelativePath</Parameter>
<Parameter Name="LogFileName">MainPackage.log</Parameter>
</Parameters>
</Package>
</Packages>
</Development>
<Acceptance>
<ProjectParameters>
<Parameter Name="InitialCatalog">AdventureWorksDWH2012</Parameter>
<Parameter Name="ServerName">SQLSSISACC</Parameter>
</ProjectParameters>
<Packages>
<Package Name="MainPackage.dtsx">
<Parameters>
<Parameter Name="SourceFolderRelativePath">\\Source\Folder\RelativePath</Parameter>
<Parameter Name="LogFileName">MainPackage.log</Parameter>
</Parameters>
</Package>
</Packages>
</Acceptance>
<Production>
<ProjectParameters>
<Parameter Name="InitialCatalog">AdventureWorksDWH2012</Parameter>
<Parameter Name="ServerName">SQLSSISPRD</Parameter>
</ProjectParameters>
<Packages>
<Package Name="MainPackage.dtsx">
<Parameters>
<Parameter Name="SourceFolderRelativePath">\\Source\Folder\RelativePath</Parameter>
<Parameter Name="LogFileName">MainPackage.log</Parameter>
</Parameters>
</Package>
</Packages>
</Production>
</SSIS>
Next: Automate SSIS 2012 Project Deployment and Configuration Management using PowerShell – Part 3
Let’s start this by describing the scenario with which the approach described below will be useful to you:
If you nod your way through the 4 points above, then take the time to read my solution below. It’s long, but I promise you will find some points mentioned helpful if not the entire thing.
I assume that you are familiar with the new Project Deployment Model feature of SSIS 2012/2014. You should also understand how Parameters are utilized with an SSIS Project. You should not be afraid of PowerShell and XML, for neither of them bite and both are very easy to learn. Note that I am not going to use the new Environment Variables feature because it assumes that all environments are deployed to a single server – which is not the usual case for enterprises or any medium to large scale company for that matter.
My solution has 3 components:
Download SSISProject containing:
We will store the target SSIS Catalog information in the SSIS Project itself so that our script can find on its own where to deploy the *.ispac file. To do that, open the SSIS project then..
Now, we are ready to fill our build configuration for each environment.
Finally, open the folder containing the SSIS project and locate .dtproj.user file. If you can’t see it, modify Folder Options to see the hidden files. The file should look like this:
<?xml version="1.0" encoding="utf-8"?>
<DataTransformationsUserConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Configurations>
<Configuration>
<Name>Development</Name>
<options>
<ServerName>SQLSSISDEV</ServerName>
<PathOnServer>/SSISDB/LAB/DeploymentDemo</PathOnServer>
<UserIDs />
<UserPasswords />
<OfflineMode>false</OfflineMode>
<ProgressReporting>true</ProgressReporting>
<ParameterConfigurationSensitiveValues />
</Options>
</Configuration>
<Configuration>
<Name>Acceptance</Name>
<options>
<ServerName>SQLSSISACC</ServerName>
<PathOnServer>/SSISDB/LAB/DeploymentDemo</PathOnServer>
<UserIDs />
<UserPasswords />
<OfflineMode>false</OfflineMode>
<ProgressReporting>true</ProgressReporting>
<ParameterConfigurationSensitiveValues />
</Options>
</Configuration>
<Configuration>
<Name>Production</Name>
<options>
<ServerName>SQLSSISPRD</ServerName>
<PathOnServer>/SSISDB/LAB/DeploymentDemo</PathOnServer>
<UserIDs />
<UserPasswords />
<OfflineMode>false</OfflineMode>
<ProgressReporting>true</ProgressReporting>
<ParameterConfigurationSensitiveValues />
</Options>
</Configuration>
</Configurations>
</DataTransformationsUserConfiguration>
Next: Automate SSIS 2012 Project Deployment and Configuration Management using PowerShell – Part 2
by Eric Schmidt and Jonathan Rosenberg with Alan Eagle
For starters, How Google Works is a management book. In fact, I stumbled upon it in the business section of the bookstore. It was written by two leaders reflecting on how their company hires, value its employees, come up with important decisions, builds its portfolio, and define the company culture among others.
You would want to read this book if you are a manager or desire to be one someday. If you are one of the brave and fortunate enough to set up her own business (within the IT industry or not), you will sure to find gems of advise and inspiration among the pages of this book. If you have reached a decision-making position, then this book can give you insights on what may or many not work for a company in the Internet Century. Finally, if you are simply one who enjoys thought-provoking materials then having a peek inside one of the world’s best companies is time well spent.
How Google Works is close to my heart not only because I believe that Google is great a company in many ways I can argue but also that my profession belongs to the same industry. I will then take this review as an opportunity to add anecdotes and dimensions to some points raised in the book based on my own experiences working in Corporate IT. I’m writing from a perspective of someone who has only played technical roles and has worked in several multi-national companies in Singapore and the Philippines, so consider that as my vantage point.
Finally, I tried not to in my previous reviews, but I will drop some quotes from this book for a change. I will indent and italicized them so spoiler-haters like me can opt and easily identify which lines to skip. What I am really trying to say for the past n paragraphs is that this post will be relatively long so go get your popcorn or drop out now, otherwise, let’s get started. 🙂
The book had me at “smart creative”, a term they use to call the kind of people who work for them and the kind they search to join their company. Modesty aside, the section describing a smart creative feels like a song dedicated to professionals like me. I could put a melody on it and sing it to myself over and over again. I could print it out, put in a frame, and hang it on my wall. The point is not to say that Google should hire me (I was given that chance, and obviously I didn’t make it), but that a female with communication skills can also be the same person a team depends on for technical expertise. The section was also a reminder that staying late in office doesn’t always mean inefficiency, but can also show how much time a person is willing to dedicate to her craft. Here are my favorite quotes from that section:
“She is driven to be great, and that doesn’t happen 9-to-5”
“..because no matter how hard you try, you can’t tell people like that how to think. If you can’t tell someone how to think then you have to learn to manage the environment where they think. And make it a place where they want to come every day.”
Google promotes a culture of Fun and Yes. A culture where teams are organized around people who matters and contribute the most. The chapter on culture also introduced me to the word knave – which by definition means “a dishonest or unscrupulous man”. Knaves are people who make their co-workers lives’ miserable and great contributors to why smart creatives leave a company. Knaves are people who we usually call with the A* and the B* words. The problem with the A* and B* words is that I can’t use them with my family, and to simply call a knave “a man or a woman in office” does not seem to justify the amount of evil this person do.
Divas, the lesser evil cousin of knaves are also mentioned in the book, but instead of talking about them, I would like to add a third social persona – Gangs. A gang is a group of knaves, soon-to-be knaves and knaves wannabes within a team. Gang members support each other and only each other and put its members growth first before the rest of the team. Gangs are more toxic than individual knaves because their collective evil is stronger than the individual good of other team members. It is also worth noting that gangs only exist when there is NO meritocracy in the company. This absence is the air gangs breathe. The unfortunate truth is that they exist and even evolve. If you are not in the position to dissolve these gangs, the best thing you can do is stay as far away from them as possible. On the brighter side, I have met leaders who are smart enough to identify and break up gangs; those are the leaders you want to associate yourself with.
Google illustrates in the book how much effort it gives in hiring the best people it can find and how it tries as a company to make them stay. The reality is: identifying good employees through a series of interviews and exams requires an investment of time and creativity not all company is willing to give. And getting good people in is just a beginning. A company needs to define career paths, evaluation criteria, training plans and other devices to manage its worker pool. These responsibilities do not directly generate money and obviously incur costs. A trending workaround to this dilemma is outsourcing. Outsourcing works by getting people work for a company via a third-party without the associated Human Resources cost. There are pros and cons of outsourcing that is out of the scope of this article, but I would like to point out a disadvantage related to talent.
Because of the lack or worse absence of proper evaluation on external personnel, the company may overlook smart creatives already working under them. This is more problematic when the company has gone beyond outsourcing operational personnel into outsourcing investment activities that is supposed to serve as the company’s competitive advantage. A smart creative in her right senses will not settle in an environment where all her efforts go into a black hole. And when she leaves she takes her expertise, efficiency, ideas, and mentoring experiences with her. She will leave footprints too big for those she left behind to fill.
Let me end this topic by a quote below from the book:
“While A’s tend to hire A’s, B’s hire not just B’s, but C’s and D’s too. So if you compromise standards or make a mistake and hire a B, pretty soon you’ll have B’s, C’s, and even D’s in your company.”
The 2nd half of the book focused on decision-making, communication and innovation aspects of a business. Instead of expanding on these topics, I would like to encourage the leader readers of this review and hopefully of the book to take a step back and re-assess his or her own company’s leadership style.
It is easier to be on defensive side and say that Google can afford to work the way described in the book because it is Google. Perhaps it would be more helpful to think that Google is Google because of the way it works.
Will I recommend this book to a friend? I think you already know the answer. 🙂
First of all, contrary to what the title suggests, it is not a methodical book which describes to its readers the actual art of thinking clearly. What it contains are 99 chapters about the fallacies, biases, effects, illusions, tendencies, and misconceptions of the modern day society.
The contents of the book are without a doubt interesting, but the format is what turns me off. Each chapter is a mini essay with a definition, proofs or examples, and conclusion/s all in 3 (roughly a thousand words in total) pages. Although possible, it’s less likely to explore an idea in detail – confirmation bias, for example – in such very short passages. Instead of ending each chapter with references to other chapters of related topic, the author could have gathered his points, organized the pros and cons, and elaborated an idea in longer but unified sections. This approach would have made his conclusions more convincing. There are other points, however, that makes his conclusions less convincing.
Since the book covers an array of topics, the author disclaimed that there are too may references to include in the book. Aside from the explicit quotes of other authors such as Mark Twain and Nassim Taleb, most claims do not include notes or citations. For a book that states “news is irrelevant”, you would expect a reasoning based on facts and studies.
Speaking of Nassim Taleb, well, Nassim Taleb was heavily mentioned in the book. At some point you could think that The Art of Thinking Clearly is a celebration of Nassim Taleb’s works. That thought might be an exaggeration, but do not be surprise if you (like me) end up getting a copy any of Taleb’s books yourself.
In summary, The Art of Thinking Clearly did not meet my perhaps very high expectations. My most important take-away from reading this book was the difficulty of giving a review on something I didn’t particularly enjoy.
Will I recommend this book to a friend? Not really.
What comes to your mind when you hear the word design? Color, shape, packaging? In many industries, design is often misinterpreted as the visual layer of a product. The fact that aesthetics is the most obvious aspect end-users see in a product is perhaps the reason why product teams give it more attention. Indeed, how something looks like is part of design, but the functions, why and how those functions are made available to end-users are equally important aspects that are less discussed in many product design process.
Continue reading “Human-Centered Design and The Wallet Project”
All the light we cannot see is the first fiction book I finished reading in 4 years. Moving to Singapore opened a new path for my professional career, and as I moved from .Net development to business intelligence I realized how much material needs to be studied fast to catch up. From then till early this year, I only read technical, career-related and “self-help” books.
To make sure I have enough in queue to continue my Book Reading habit of 2015, I went to Amazon. This emerald covered hardbound is the #1 fiction of 2014. It tells two stories in parallel – one of a curious boy in Germany and another of a blind girl in Paris – set during the World War 2.
All the light we cannot see lived up to my expectations and more. It is the kind of book you would read until 3 am in the morning, until your eyes can read no more. The pace of the stories are just right. There is enough suspense to make you look forward to the next chapter. The paragraphs are woven with harmony; every sentence a piece of an image of the scene the author wants his reader to see. If you are particularly fond of science, the ocean, and history, then this book has icings for you.
Vanie's View 