My article “SSIS Design Pattern – Staging Fixed Width Flat Files” is published on SQLServerCentral.com. In the article, I provide a design pattern for loading fixed width flat files into staging tables. I also demonstrate a step-by-step example of creating an SSIS package for this pattern.
Head… Read more
What’s my share? Calculating percent share of the total is a common need. Let’s see how to calculate this in Power BI using DAX.
I’ll start with a table containing population by country as shown below. I’m using Power BI Desktop for this demonstration. You can use the same formula… Read more
Let’s say you have a table that records transactions along with the date of when the transaction took place. Wait, I’m being told that Production.TransactionHistory in AdventureWorks sample database matches this description. So let’s use that for this demonstration.
Here’s the simplified schema of that table.
CREATE TABLE [Production].[TransactionHistory] (…
There’s a saying that birds of the same feather flock together. When you throw your data on a scatter chart, data with similar characteristics stick together and some data points land far away from the bunch. Data points that appear together represents the norm in your data set, and those… Read more
When you start working in ETL (Extract, Transform, and Load) or SSIS projects, you’ll be faced with two basic terms: Full Load (or Destructive Load) and Incremental Load. Learn what these mean with a simple analogy.
Back when I was in school, me and a bunch of friends used to… Read more
About 80% of the world’s population lives in about 13% of the countries. Okay, this statement is not as knee-jerking as I made it sound. Pareto Principle (also known as 80-20 rule, the law of the vital-few) states that, for many events, roughly 80% of effects come from 20% of… Read more
U.S. stock markets have taken a beating this past week fueled by China markets. Take a look at how China and US markets fared this year and learn how to create these chart using Power BI Desktop.
The three line charts in the following image represent weekly data Shanghai Composite… Read more
S&P 500 index is an American stock market index consisting of 500 large companies and is considered to be the one of the best representations of the U.S. stock market, according to Wikipedia. The following dashboard created using Power BI Desktop tracks the index over time.
I’ve also hid… Read more
Blank values are annoying, anywhere and everywhere. If your source system contains them, here’s how to convert blank values to NULL in an SSIS data flow task.
For this demo, I created sample data at the source with blank values in “ColC” column.
You can add a Derived Column transformation… Read more
Power Query makes it easy to pull data from a web page. As easy as deflating a football. Poof. Gone. However, you’d need a few additional steps to iterate through and get data from multiple pages. Let’s take a look.
A use case of this case can be seen when… Read more
Power BI is released to the public on Friday, 07/24. There’re both free and Pro versions. For full details about products, downloads, and pricing, go to https://powerbi.microsoft.com/. This is how the community reacted to the release on Twitter.
An exciting news was announced a day prior to GA. The… Read more
When importing data from a flat file using Power Query, you’d want to combine multiple files and include file name in the resulting table. For example, in Analyzing Baby Names using Power BI I posted recently, the file name contains year that I needed to include in the table. Here’s… Read more
Delimited flat files are commonly used to move data from one system to the other.
In this two part series published on SQLServerCentral.com, you’ll learn:
- A design pattern to load flat files to staging tables
- Automatically create packages to load multiple flat files using this pattern
Both these articles… Read more
A few years ago on road trips I’d happily grab whatever I can find to eat at service stations. That usually includes either McDonald’s, KFC, or Burger King. But today I wouldn’t mind driving a few miles off-route to eat something healthier. If you follow any financial news, you’ll notice… Read more
A few weeks ago, while at SQL Saturday Philadelphia, Chris Bell, SQL Server MVP and co-leader of PASS DC user group graciously sat me down and talked. We talked about me; who I’m, what I do, and what I like. You can listen to the podcast here.… Read more
Me and my wife are contemplating names for our first baby we’re expecting in a couple of months! We don’t know yet if it’s going to be a girl or a boy, and our shortlist of names is very short right now with just one name. Needless to say, we’re… Read more
Here are the slides and links to awesome resources for my presentation, “Team-based Database Development: Playing Nice With Others”
If you need a summary of what’s covered in the session, the abstract is as follows:
As soon as you have more than one person on a development effort,… Read more
When working with SSIS, you’ll often find the need to read the contents of a flat file to a variable, and write the value of a variable to a flat file. A script task can be used to accomplish both of these.
READ CONTENTS OF A FLAT FILE TO A… Read more
I’m passionate about these topics and enjoyed building these new sessions. Hopefully the attendees will… Read more