Author: vaniecastro

2015 Book 1: The Art of Thinking Clearly by Rolf Dobelli

by vaniecastro, posted in Book Review

IMG_3985

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.

Human-Centered Design and The Wallet Project

by vaniecastro, posted in Others, Technology
https://www.flickr.com/photos/55674398@N06/15956779184/
*leather wallets from Chinatown Singapore, photo taken by yours truly

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” →

2015 Book 4: All the light we cannot see by Anthony Doerr

by vaniecastro, posted in Book Review

IMG_4002

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.

Will I recommend this book to a friend? Yes.

How to “downgrade” SQL Server Integration Services 2014 packages to 2012

by vaniecastro, posted in SQL Server, SSIS

The Problem

A few months back, I built an SSIS 2014 project using Visual Studio 2013 that is supposed to serve as a template for future data warehouse projects. Our servers are still in SQL Server 2012, but the upgrade has been approved and so it made sense to create the template project in VS 2013. Until one day, the template was needed for a new project, with the promised SQL Server 2014 upgrade not yet in sight. Since SSDT-BI 2013 is not backward-compatible with SQL Server 2012, I then had to downgrade the SSIS 2014 project so that people can use it using Visual Studio 2012 and deploy it to SQL Server 2012. There are no tools from Microsoft, no StackOverflow forum that gave the perfect answer and mattmasson has kept quiet about the topic. 😦

The Workaround (Summary)

Based on my observation (only  Microsoft can verify), excluding the upgraded components, there are three changes between the XML files of SSDT-BI 2012 and SSDT-BI 2014 dtsx packages.

  1. PackageFormatVersion

SSDT-BI 2012 VS 2012: DTS:Name=”PackageFormatVersion”>6

SSDT-BI 2014 VS 2013: DTS:Name=”PackageFormatVersion”>8

  1. The values used in ExecutableType properties

SSDT-BI 2012 VS 2012: DTS:ExecutableType=”SSIS.Package.3″

SSDT-BI 2014 VS 2013: DTS:ExecutableType=”Microsoft.Package”

  1. The values used in componentClassID attributes

SSDT-BI 2012 VS 2012: componentClassID=”{49928E82-9C4E-49F0-AABE-3812B82707EC}”

SSDT-BI 2014 VS 2013: componentClassID=”Microsoft.DerivedColumn”

Have you guessed the fix already? You got it! It’s only a matter of find (all) and replace! Open the dtsx file in your favorite text editor (mine is  Notepad++) and replace the values used in the DTSX2 Version 2014/01 schema with the ones in DTSX  Version 2012/01 schema. 🙂 Now, there is some “manual labor” required, because you have to manually search for the componentClassIDs and ExecutableTypes in the DTSX  Version 2012/01 schema. Here are the ExecutableTypes and componentClassIDs I encountered so far. Click Replace.xls for an Excel version.

replace

 The Workaround (Step by Step)

  1. If you haven’t done so, open DTSX Version 2012/01 schema
  2. Open the SSDT-BI VS 2013 DTSX package in a text editor
  3. Search for PackageFormatVersion. Change the value from 8 to 6
  4. Replace all instances of Microsoft.Package with SSIS.Package.3
  5. Replace all instances of Microsoft.Pipeline with SSIS.Pipeline.3
  6. Search for all instances of DTS:ExecutableType=”
    unnamed
  7. For each ExecutableType value (Microsoft.ExecuteSQLTask, etc.), go to the DTSX Version 2012/01 schema and search under 
    <xs:simpleType name="AnyNonPackageExecutableTypeExecutableTypeEnum">

    for the correct ExecutableType. For example, Microsoft.ExecuteSQLTask in SSIS 2014 should be Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc9 in SSIS 2012.

  8. Replace ALL instances (including CreationName attributes) of the ExecutableType value with the one you got from Step 7
  9. Search for all instances of componentClassID=”Microsoft
    componentClassID
  10. For each componentClassID value (Microsoft.DerivedColumn, Microsoft.Lookup, etc.), go to the DTSX Version 2012/01 schema and search under 
    <xs:simpleType name="PipelineComponentComponentClassIDEnum">

    for the corresponding GUID.

  11. Replace ALL instances (including CreationName attributes) of the componentClassID value with the one from Step 10.
  12. Now open the package in Visual Studio 2012. If your package does not have upgraded components, then it should open by now. Otherwise, read further.

The Exception

Some components, such as the Script Component in the Data Flow Task, have been upgraded from SSDT-BI 2012 to SSDT-BI 2014. In this case, it’s not only the property / attribute value that is different, but also the structure of the XML node. In this case, replace the Version 2014/01 schema value (e.g. componentClassID=”Microsoft.ManagedComponentHost”)  with any valid DTSX Version 2012/01 componentClassID. Then open the package in VS 2012. The component will be marked as with error, that’s OK. If you have followed all the steps above, your package should at least load the designer by now. Now remove this erroneous component and re-do it in VS 2012 itself.

Conclusion

I  was able downgrade SSIS 2014 to SSIS 2012 packages by modifying the XML file manually. I downgraded 15 packages in 4 hours including finding the workaround described above. It should be faster if you already know what to do. This approach seems more reasonable than re-creating all the packages I designed and developed for weeks with all their DFTs and expressions. No, thank you. Let me know if you encounter any other exceptions or if any of the steps above is not clear.

2015 Book 3: Quiet By Susan Cain

by vaniecastro, posted in Book Review

IMG_3995

Quiet is a book for introverts by an introvert. Susan Cain, a former Wall Street lawyer, started the book by describing the physiological aspect of introversion. Using research after research, she tells stories of how a natural “introversion” can be nurtured by an environment for the good or the bad. She tries to explain how introverts, often referred to as “shy people” (which in psychology is not necessarily the same), sees and reacts to the world. She wrote of circumstances when an introvert leader may be better than an extrovert one and vice versa, how a manager can extract the most potential from an introvert subordinate and even how a parent may raise an introvert child.

My self diagnosis is that I am NOT an introvert. But you don’t have to be one to enjoy the book. If you have loved ones, colleagues or anybody close you think falls in this personality category, I would recommend that you read this book. It brings forth the perspective of one third to one half of the world population. People who end up becoming scientists, musicians, artists, educators, engineers and many other professions that require solitude to perform.

Quiet

Will I recommend this book to a friend? Yes.

Deploy a SQL Server Analysis Services Project using PowerShell

by vaniecastro, posted in Automation, Continuous Integration, Deployment, SSAS

Step 1: Build SQL Server Analysis Services solution using devenv /build, for example

“<path>\devenv.com” AdventureWorksSSAS.sln /build Development

In the bin folder of your solution, you should find <projName>.asdatabase, <projName>.deploymentoptions, <projName>.deploymenttargets and <projName>.configsettings XML files

Step 2: Generate XMLA script using Microsoft.AnalysisServices.Deployment.exe

Microsoft.AnalysisServices.Deployment.exe <path>\AdventureWorksSSAS.asdatabase /s:GenerateXmla.log /o:SSASDatabase.xmla

Step 3. Execute XMLA on target server using PowerShell script.

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices.AdomdClient”)

# Read the target server from .deploymenttargets file generated in Step 1

$DeploymentTargetsFile = <path to <projName>.deploymenttargets>

$deploymentTargets = [xml](Get-Content $DeploymentTargetsFile)

# Connect to the server
$serverName = “Data Source=” + $deploymentTargets.DeploymentTarget.Server
$conn = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection $serverName
$conn.Open()

# Read XMLA
$xmla = Get-Content $XmlaPath

# Execute XMLA
$cmd = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdCommand $xmla, $conn
return $cmd.ExecuteNonQuery();

And that’s it 🙂

All You Need To Know To Start Learning SQL Server Analysis Services

by vaniecastro, posted in SSAS

I have this rare opportunity to provide basic SSAS training to developers from another team. Being busy with my own projects, my manager and their manager agreed to have the training for only 3 hours. I met the would be trainees and their manager to understand their expectations, the developer profiles and the modeling technique their project use.

The developers have intermediate to advanced T-SQL skills and they expect that at the end of the 3-hour training, they would be able to comfortably navigate around the existing SSAS solution, understand basic concepts of dimensional modeling, create new dimensions and measures, modify the existing cubes and have the basic knowledge they need to dig deeper into SSAS themselves. Whew! Investigating further, I found their current solution uses multidimensional approach with SQL Server 2012. They have calculations and perspectives but do not (or maybe not yet) define partitions, aggregations and translations.

With those constraints in mind, I prepared the outline of my training using XMind.

Continue reading “All You Need To Know To Start Learning SQL Server Analysis Services” →

[SSIS] Lookup Transformation – Full Cache option

by vaniecastro, posted in SSIS

Level: Intermediate

Lookup transformation is an essential ingredient of a Data Flow task accessing multiple database servers. I always use the Full Cache option when the reference table is small enough to be stored in memory. However, this caching option needs more than setting up your Lookup task to work. Here are few things to check when your Lookup transformation fails to detect a match you are expecting it to detect:

Case sensitivity – the columns being compared must have exactly the same case. Otherwise, the match will fail.

Solution: Use UPPER() OR LOWER() on BOTH the Lookup AND the pipeline columns

Continue reading “[SSIS] Lookup Transformation – Full Cache option” →