Using Biml with SQL Server 2000; There IS a Way.

by Audrey and Julie

Summary: The BimlScript methods of Business Intelligence Markup Language (Biml) will only work (ie access SQL metadata) with SQL Server versions 2005 and higher. This article briefly tells the story of how Innovative Architects worked around this limitation for one of our projects and successfully tricked Biml by creating system views in SQL Server 2000. They ddl script for the views is found in a link at the end of the post.

————————————- Continue reading Using Biml with SQL Server 2000; There IS a Way.

Power Query Functions–Some Scenarios

By Julie Smith

I was happy to co-present a session at this week’s Atlanta BI User Group with Rob Volk (@SQL_R) meeting entitled “Harvesting XML Data from the Web with Power Query and Curl.” The demo gods were not with me on my grand finale demo that night however. I had spent the demo building a Power Query Function and when I tried to invoke it against a list of values, I got a failure which I couldn’t resolve that night. Of course, as soon as I opened the spreadsheet the next day I immediately saw the problem, which I will share here, as I think it is probably going to be something people encounter frequently as they start to work with Power Query.

What the Function Did:

Here’s the setup: www.SQLSaturday.com contains a page for every SQL Saturday, and if it’s available, the schedule for the event. Atlanta’s last event was this month and was SQL Saturday #285-hence, its schedule is located at  http://sqlsaturday.com/285/schedule.aspx. Any other SQL Saturday event number works in the same manner. If I want to use Power Query to view this data as a page, I would enter that url as the source in Power Query:

After choosing Table 1 from the choices on the Navigator pane: Continue reading Power Query Functions–Some Scenarios

Another NULL Handling SSIS Expression

by Julie Smith

So two days ago I posted this. It’s a way to generate an SSIS Expression for use in an Incremental Load’s Conditional Split. A friend had pointed out that this pattern was not the best– as NULL handling is not always as easy as replacing the NULL with what you might consider a safe value. I also got a very thoughtful comment on the post from a lovely gentlemen expressing the same concern. So obsessed, I went back to tinkering.  I came up with ANOTHER expression (and consequently another T-SQL generator for it). I like this one a little better as it seems to me that it performs what is asked without introducing risk of replacing NULL values. So folks please read this, use it, bash it up and let me know what you think.

Here’s the new (to me, sure someone had already figured this out) NULL Handling expression for DELTA rows, using the column Color as an example:

This does NOT break the Conditional Split if there are NULLS. There can be NULLS in either the source or the destination or both and it does not break the pipeline. I love that.

How to read it from the left:

The whole expression will evaluate as TRUE and the row split into the Delta path when either the yellow portion or the Green and blue portion together evaluate to True.

The yellow highlighted expression asks: is either side NULL while the other is not? If yes, then evaluate to TRUE.

The Green highlighted section: Are both sides NOT NULL ? If yes, Then Blue highlighted section asks: are they unequal? If yes,  then evaluate to True.

If both sides are non null, yet equal, or if both sides are NULL, then the condition is not met and the row is ignored. Just like we want it to be.

Now the SQL to generate the whole expression can be datatype agnostic. I love that too. Here is the SQL to generate the whole concatenated shebang for all of your columns:

Click on the SQL below to get a copy/past version :)

Click on this to get copy/paste version.

Continue reading Another NULL Handling SSIS Expression

Using SQL Metadata to Create NULL Handling SSIS Expressions

By Julie Smith

Happy 2014! Happy to report that Audrey and I were both renewed as SQL Server MVPs today!  To celebrate I’m publishing a really long blog post.

This is a MONSTER long post. The main point of this post was to give you guys some T-SQL code which can be used against the information schema view of a SQL Server table destination to spit out a complex SSIS Expression. While that was the point of the post, I felt like I also needed to provide context of what I was trying to do or what you might be doing when the need for such SQL would arise. 

also, I had Audrey read it over and she scolded me for a sad lack of chuckles. So I’m adding in some random Chuckle-y interludes. Enjoy them. Or skip them completely. Chuckle interludes are indicated by the face Chuck Norris doesn't sleep. He waits. of Chuck Norris for easy recognition.

One of the most common scenarios encountered while ETL’in is an Incremental Load –that is determining if a source row already exists in the target database (often your warehouse), and if it does, has it changed since the last time it was loaded? The pseudo code goes like this: Continue reading Using SQL Metadata to Create NULL Handling SSIS Expressions

On Blogs – Think like a Journalist

Today, I was reading a blog post.  The article was published by a group that I would consider reliable and reputable.  It was on a topic that I have passing familiarity with, and would like to be better at.  I’m not going to name the post nor the topic, because that’s not the point.  Here’s what went down…

1130 Hours: Read post.  Surprised at the absolutes declared in the article.

1135 Hours: Read it again.  Think that either I’ve missed some really basic lessons on this topic, or that maybe the article has provided some less than ideal guidance.

1209 Hours: E-mail a friend who I know is an expert on the subject.  Ask him to read it and let me know if he thinks it is right.

1300 Hours: Get response from generous and patient friend.  His e-mail (which was longer than the blog post) explains in very clear terms that there are problems with not only the guidance that the article provides, but also how some of the fundamental concepts were represented.

1420 Hours: Go find another post on the same topic by another trusted expert.  Read it and confirm for the second time that my original suspicions about the article were correct.

1430 Hours: Pat myself on the back for knowing just enough about the topic to realize that it seemed off in the first place.

1445 Hours: Go back to blog post to write comment that maybe the article could use a second look.  See that someone has already done that.  Decide not to pile on.

Now, I’ve been watching a lot of The Newsroom and House of Cards lately, both of which have characters who are journalists.  I think that reading blog posts, articles, and books is a lot like being a journalist.  One source is not enough.  If you’re hearing something new or something that contradicts what you think you know, don’t take the article at face value.  Go find a second source, and make sure that the second source didn’t use your first source as their source.  (Caveat:  I do have a list of absolutely trusted writers.  But it is my list, and is based on a lot of factors.  Okay, fine.  I’ll share one.  His name rhymes with Tall Candle.)  Also, if you’re finding conflicting advice, don’t be afraid to ask questions.  Any writer worth her salt is willing to accept some peer review.

And yes, if you’re wondering, I felt very MacKenzie McHale for all of 3 seconds. 

??????????????????????????? (photo from: http://www.fanpop.com/clubs/the-newsroom-2012/images/33579445/title/mackenzie-mchale-photo)

I imagined myself, headset on, shouting, “We’re not going live with this until we confirm a second source!”  Then I remembered that no one but me was consuming this information.  Oh well.

If you’re on the other side of the keyboard and are writing an article you plan to send out into the world, here are a few guidelines to live by:

1) Unless you are 110% sure that your guidance applies 100% of the time, don’t speak in absolutes.  “It depends” is a running joke for a reason.

2) Find someone you trust to tech edit for you.  Heck, find two people.

3) Remember that there are a lot of young database professionals out there who are reading your work in order to figure out how to do their jobs.  Don’t take that lightly.

4) If someone comments on your article and says you’re wrong, engage with them.  They’ve taken the time to read and comment on your work.  Granted, there are trolls out there, but a thoughtful comment demands a thoughtful response.  And keep an open mind.

5) If you’re preaching something that goes against conventional thought, take the time to post links to opposing views.  Help your reader make an informed choice about which advice to follow.

6) Encourage your reader to do their own research with the information you’ve provided.

7) If you discover that you’ve presented bad information, correct the article.  Own it.

All that being said, the proliferation of online resources has made us all better.  Don’t be afraid to put your research and opinions out there.  Just research, verify, and test.  And look for that second source before going live with the scoop. 

Blog on, my friends…

–Audrey

Power Query –Where did my query script icon go?!?

by Julie

 

I hadn’t played with Data Explorer Power Query since it became Power Query last week with the announcement of Power BI for Office 365. Since I am presenting on it at the Microsoft Integration Architects group this week, I opened it up to prep the demos. By the way, Power Query is very cool. It’s an Excel Add-in that allows an information worker to perform self service BI. With Power Pivot and Power View, it completes a self service BI trifecta of ETL, compressed storage and presentation. Now that Power BI is available as a cloud service, this not only gives users a complete stack of BI tools, but allows those tools to be more easily accessed via mobile devices.

Since Power Query is designed to be a self service tool, it focuses on ETL by way of drag and drop/gui tools. However, one of my favorite features of Power Query was that you can access and copy the underlying code (which is a language called “M” by the way), make a few slight changes to it and reuse. For instance if I needed data from several tables on the internet on different urls, but they all have the same structure, I could labor through the first page with the gui, then copy that code, paste it into notepad, change the url address of the source only and paste the new code into a new Power Query. and BAM! It’ done.

This was all accomplished via the query script icon, a cute little fella that looks like a scrolled piece of paper.

powerquery

All of the steps on the right hand column above represent pieces of work done via the gui to get the data in this format. By clicking on the query script icon, I get this:

powerqueryM

which I can modify and paste into new queries.

But back to my story of a
change between Data Explorer and Power Query. In Data Explorer, the query script icon was always there. I spent a good ten minutes one night this week in a cold panic, wondering if I had forgotten how to “work” Power Query, because I couldn’t find it after upgrading. It appears that now you have to expressly enable this feature by going to Options under Machine Settings and clicking “Enable Advanced Query Editing.” See my steps 1,2, and 3 below.

enablingScrollyThingie

So there you have it people. Happy Advanced Querying.

Shameless plugs:

Going to be in the Indianapolis area August 9th? Interested in learning the basics of how to create and load a data warehouse? Join Audrey and me for “From Here to BI: Data Warehousing with SQL Server MVPs Audrey Hammonds and Julie Smith.” We’ll also be presenting for SQL Saturday #242 main event on August 10th. Look forward to seeing you there.

Also don’t miss Audrey’s session for 24 Hours of PASS– Design Matters! The Performance Impact of Database Design