Thanks so so so so much to Idera!  For sponsoring these lucky winners!

To all who submitted!  You showed initiative in taking charge of your career!  Kudos!

To our speakers!  Thanks for putting on these training events and enriching the lives of other professionals.

To Midlands PASS (Bobby Dimmick (web | twitter) and Brian Kelley (web | twitter)) for picking the winners–tough choices I know!

The winners are:

Scott Murray for No More Guessing with Adam Machanic

Alex Salyer for A Day of SSIS with Andy Leonard and Matt Masson

Vanessa Miller for Learn Reporting Services in a Day with Jessica Moss

Congratulations!

AtlantaMDF and Idera are teaming up to find a few good people.  If you are:

• A student looking to work in the database or business intelligence fields
• A database professional who is between jobs or wants a better one
• A developer looking to step up to something new
• On a limited budget and find it hard to afford professional SQL Server training
• Able to attend training from 9 to 5 on April 13, 2012 in Atlanta.

AtlantaMDF is presenting 3 Pre-Conference Sessions (pre-cons) for SQL Saturday #111!  And thanks to Idera’s sponsorship, we can offer one free ticket to each of these sessions to eligible candidates! That means one scholarship per Pre-Con!

One Recipient Each will Attend:

No More Guessing with Adam Machanic  http://NoMoreGuessing.eventbrite.com

A Day of SSIS with Andy Leonard and Matt Masson http://DayofSSIS.eventbrite.com

Learn Reporting Services in a Day with Jessica Moss  http://SSRSinaDay.eventbrite.com

If you are interested in attending these pre-cons send an email by March 13th to MDFPreconsScholarship@gmail.com  and tell us:

• Why you are a good candidate to receive this scholarship
• Which sessions you’d like to attend, and why (list multiple sessions in order of preference)
• What the session will teach you and how it will help you achieve your goals

The emails will be evaluated by the good folks at Midlands PASS in Columbia, SC.

The recipients will be notified by email and announcements made on March 28^th.

You can find more information about the pre-cons at http://www.sqlsaturday.com/111/eventhome.aspx.

P.S. – Don’t forget that SQLSaturday #111 offers free* training in addition to the pre-cons!

You can view the schedule at http://www.sqlsaturday.com/111/schedule.aspx

and register for them at http://www.sqlsaturday.com/111/register.aspx

* Registration charges a $10 fee to cover lunch expenses.

Thanks for attending tonight. Here is the link to the slidedeck.

Atlanta MDF presents:

SQL Saturday #111 Pre-Conference Sessions

Attend a session presented by Microsoft SQL Server MVPs (and one Microsoft Guy):

Andy Leonard and Matt Masson– A Day of SSIS – http://dayofssis.eventbrite.com/

A Day of SSIS was developed by Andy Leonard to train technology professionals in the fine art of using SQL Server Integration Services (SSIS) to build data integration and Extract-Transform-Load (ETL) solutions.  The training is focused around lectures and emphasizes a practical approach. The target audience for this training is database professionals, application developers, and business intelligence practitioners interested in acquiring or expanding their existing SSIS skill set. No experience with SQL Server or SQL Server Integration Services is required before attending this training.  It is helpful (but not required) that students possess some knowledge of and experience with relational databases. SQL Server knowledge / experience will be more helpful than experience and knowledge with other technologies.

Adam Machanic – No More Guessing! An Enlightened Approach to Performance Troubleshooting – http://nomoreguessing.eventbrite.com/

Scratching your head, you stare at the screen. Should you rebuild an index? Create a new one? Reboot the server? Why is this query so slow?!? Figuring out performance problems can sometimes feel like fumbling your way through a dark room. Maybe you’ll get lucky and find the right solution―or maybe you’ll stub your toe. Either way, it’s a slow, potentially painful process. Yet finding the root cause of most performance issues is a simple exercise, once you understand where to look and when. In this full day seminar, you will learn a proven methodology that can be used to approach virtually any performance problem. Created and refined by Adam Machanic over several years, this strategy leverages core SQL Server performance tools (including dynamic management views, Extended Events, and WMI counters), applying them to various performance troubleshooting techniques (such as waits and queues analysis, baselining, and real-time activity monitoring). Each of these tools and techniques has a unique role, and you will learn to use them cooperatively to quickly and adaptively find the actual cause of performance issues. All of this will be illustrated through complete demonstrations that will teach you how to drill from high-level problem detection all the way to specific spot in a query plan or deeper―pinpointing the exact problem and helping you to quickly solve it. Attend this seminar to take full control of your databases—and never again stumble blindly through the dimly lit world of performance troubleshooting.

Jessica Moss – Learn SSRS in a Day – http://ssrsinaday.eventbrite.com/

SQL Server MVP, Jessica M. Moss, presents an exciting, introductory, full day training session on SQL Server Reporting Services 2008 R2. In the three-part class, Jessica will teach you how to build reports from the ground up. In Part 1, learn the basics of report development, including picking a report development tool and creating your first report. Part 2 delves into visualizations, groupings, and drill-down functionality. Finally, Part 3 highlights core administration tasks in Reporting Services. In addition, Jessica will point out industry-wide best practices for report development and show numerous live demos using a variety of data sources.

Friday, April 13, 2012

Georgia State University – Alpharetta

3775 Brookside Pkwy

Alpharetta, GA

Register for pre-conference sessions by March 15 for $99!

($109 after March 15)

For registration to the Saturday Event and more information visit  http://www.sqlsaturday.com/111/eventhome.aspx.

First, I want to tell you what this post is NOT about. It is not about the PASS BoD. It is not about Sri Sridharan. It’s not about my opinion of the decisions made by the PASS BoD in regards to the two open board positions. Okay, glad we got that out of the way.

Here’s what it is about. Community. Family. Even when we don’t agree with each other.

Let me tell you a story about myself… When I was 13, I played softball. My team took first place in our league, and that meant that we got to compete in the District Tournament. Now, our coach had the option to select 4 players from other teams in our league to supplement our team at the tournament. One of his selections was a first baseman. I was the first baseman for our team. I got benched. I was furious! I thought it was unfair that I lost my spot, when I helped our team win the league. I thought I should be on the field. I even went so far as to write a very strongly worded letter to my coach. (Yeah, I was THAT kid) I was complaining to my mom about the ordeal, and said that I didn’t like the girl who took my position.

Here’s what she said to me: “Honey, let me ask you something. What’s more important to you? That you’re the one on the field or that your team wins? She’s taller than you (important in a first baseman) and has a better bat. Are you going to blame her for your coach’s decision? You need to decide right now who you are. Are you the person who thinks of your team first or yourself first? I expect you to put the team first, and you had better be the loudest, most supportive person on that bench, and be ready to step up whenever you’re needed”.

My mom was never one to mince words. It was a hard lesson to learn as a kid, but a good one. Even though I disagreed with the coach, I stayed on the team. I cheered every play, and was ready to sub in as needed. Guess what? We won the District Tournament. I would have missed out had I quit.

What does this have to do with our latest PASS drama? Well, besides giving me an opportunity to tell you a story about myself, there is a point here. We’re a community, and a family. While we don’t always agree, and sometimes disagree vehemently, we’re still part of the same team.

And here’s my point. We might not all agree with the decisions made by our PASS BoD, but let’s cheer for our players. Let’s give Kendal van Dyke (Blog | Twitter) and James Rowland-Jones (Blog | Twitter) the best possible chance to be successful and effective as appointed board members. Will we hold them to a high standard? Of course. Don’t we always? Will we ask them to do a (mostly) thankless job for no pay? Yes. Will we tell them when we think they’ve made a bad decision? Yes, we’re pretty good at that. But, let’s give them a chance. They didn’t ask to be put in the middle of a controversy. They stepped up and accepted leadership roles within our community. That counts for something. Now, I don’t know James Rowland-Jones, so I can’t speak from experience about him.  But I can only assume that he cares about this community based on what he wrote here. I know Kendal personally, and I know that he’s a kind, hard-working person with honorable motivations.

Should we blindly follow the Board of Directors?  No, of course not.  Should we have an opportunity to vote on the by-laws?  Yes. Should we raise the red flag when we disagree?  Hell yes.  However, should we blame and publicly vilify two community volunteers who got stuck in the middle? Absolutely not.

Let’s all take a deep breath. I’ve done it, and here’s what I asked myself. If I were in Kendal or James’ shoes right now, what’s the one thing I would ask for? The answer I came up with is, “Give me a fair chance to show everyone that I can do right by the community”.

So that’s what I’m doing. Kendal and James – I’m cheering you on. I hope that you work hard and lead us well.

Wait… One more note before I go… Let’s be kind to each other.  The world is tough enough as it is without mean people in it. 

–Audrey

Hey there! I hope you’ve had a great Friday. Today, we’re wrapping up Data Modeling Week here at SQL University. Many thanks to Jorge Segarra (Blog|Twitter) for giving me the opportunity to cover these topics. Data Modeling is my first love, and I’m thrilled that I’ve had a chance to write about it. There is no way to cover anything but some of the big ideas in three posts, but there is a lot of information out there for you to learn from. I provided some resources in the last post, Entity-Relationship Modeling (Part I), and I think they bear repeating. Jump to the bottom of this post for recommendations on further learning.

Any-who… On Wednesday, we talked about the basics of an Entity-Relationship Diagram (ERD). Entities, Relationships, and Attributes were discussed, and we got the beginnings of an ERD about Country down on paper. If you haven’t read that post yet, you might want to jump over to it and go through that first. (Link in paragraph above) What we’ll talk about here will probably make more sense. Or not. All up to you, my friend.

So what are we talking about today? Refinement! No, not finishing school refinement. Wait… maybe we are. What we’re going to do is polish up the first cut of the ERD by walking through the entities, relationships, and attributes and identifying areas that don’t quite work. Then, we’ll talk about how to tweak those areas to make them walk with their shoulders back and their eyes up, like the prim and proper ERD elements they are.

<Soapbox 1> You will never get any model perfectly right on the first try. It’s okay. The fear of imperfection can create inertia, and that’s why a lot of modeling projects stall and eventually fail. Recognize that you don’t know everything up front, and that your data isn’t perfect (and neither are you). Just do what you can with what you’ve got. Something is almost always better than nothing. </Soapbox 1>

Between Wednesday and today, I went through the entities for the Country model and added them to the ERD. I added attributes and even some inherited information between the entities.

A note on attribute notation. I used underlines and italics to note some different things about the attributes. This isn’t formalized, but it helps me see what’s what. If I’m using a real modeling tool (which I’m not here), a lot of this metadata about the attributes will be handled by the modeling tool GUI.

But, for today, here’s the notation I’m using. I kind of like it, because I’m not dependent on a particular piece of software, but it tells me what’s going on. There’s a not-so-subtle message here: Care less about formal notation and more about consistency and readability. Do what works for you and your organization.

Here’s what I ended up with:

I’ll let you read through the model since you totally rock at reading ERD’s now. Next up, we’ll start addressing issues we see.

MANY-TO-MANY RELATIONSHIPS

The first issue we’re going to address is Many-to-Many relationships. In the real world, these kinds of relationships happen all the time. For example, COFFEE is drunk by many PEOPLE, and PEOPLE drink many cups of COFFEE. However, in a database, Many-to-Many relationships can create all kinds of problems. Trying to query data like this can be a bit of a nightmare, so we resolve the problem with what is called an Intersect Entity. It is just what it sounds like: An entity that intersects the relationship between two entities. We take that Many-to-Many relationship and turn it into two One-to-Many relationships. You know what? It’ll make more sense if I just show you:

This is actually very simple:

1) Create a new Entity, and give it a name that is the concatenation of the names of the entities you’re intersecting

2) Split up that Many-to-Many relationship into two One-to-Many relationships, with the Many side being on the Intersect Entity. Now I have two sets of verb phrases:

“LANGUAGE may be the communication method for one or more COUNTRY LANGUAGE, and COUNTRY LANGUAGE must be a type of LANGUAGE”

“COUNTRY must communicate via one or more COUNTRY LANGUAGE, and COUNTRY LANGUAGE must be the communication method for a COUNTRY”

One thing that you’ll want to make sure you adjust in the relationships: The optionality of the intersect entity needs to be mandatory, because it’s all inherited information.

3) Allow the Intersect Entity to inherit the key from the two entities. The combination of these two becomes the key for the Intersect Entity. (Note: A key made up of more than one attribute is known as a composite key)

4) Finally, add non-key attributes to the Intersect Entity. Now, this is very, very cool. Remember on Wednesday when we talked about Attribute rules? One of them was “Attributes must be atomic”. The example provided was about the percentage of people who speak a language within a country. An attribute like “English-60%” is not valid. However, we can now take advantage of that Intersect Entity, and we have a perfect place to put that information. Percentage is only one thing (atomic), and it helps to define the combination of COUNTRY and LANGUAGE.

We have one other Many-to-Many to resolve: CONTINENT and COUNTRY. Trivia Question: What countries are transcontinental? Answer: Egypt, Russia, Turkey, Kazakhstan, Azerbaijan, and Georgia. Keep that one in your back pocket for your next team trivia night.

INHERITED ATTRIBUTES

Classic Inheritance

When there is a relationship between two entities, the child entity (the one on the Many side of a One-to-Many relationship) will inherit the key of the parent entity (the one on the One side of a One-to-Many relationship). If you’re familiar with physical database implementation, these are your foreign keys. Thinking in the context of an ERD, there is a very important principle at play:

An Entity owns its data.

An entity is the one and only place for information about that particular thing. I don’t want information in more than one place. Let’s look at COUNTRY and GOVERNMENT TYPE:

I have an inherited key of Government Type Name. I also have an attribute in COUNTRY of Government Type. Because I’ve inherited the key of the GOVERNMENT TYPE entity, I don’t need the Government Type attribute any more. The advantage of allowing entities to inherit attributes like this is that I have a single place to store Government Type information, and will ensure that I don’t get variations on the descriptors later on. We’ll remove the Government Type attribute from COUNTRY.

The Aggregation Question

Another attribute that we should look at is Population. In my original ERD, I have Population in multiple entities: CONTINENT, COUNTRY, STATE/PROVINCE, and CITY. This is a tough one, and is going to require a decision on your part. Here’s what you need to ask yourself: Can the Population of a CITY aggregate up to a STATE/PROVINCE Population, then up to COUNTRY, then up to CONTINENT? Ideally, yes. In practice? Maybe not. Not only do you need to think about the principles of data modeling here, you need to think about your data quality and your business.

From a purist standpoint, we should store this attribute at the lowest level we can (CITY), and depend on the relationships between the entities to allow us to calculate Population at a higher level (STATE/PROVINCE, COUNTRY, or CONTINENT). From a pragmatic standpoint, we don’t really know if the entire population of a continent lives in defined cities. Also, are we looking for estimates or hard numbers?

The downside to storing an attribute in multiple places is that now I’m going to have to maintain that attribute multiple times. Let’s say I increase the Population of Atlanta by 5,000. Now I have to go through and increase the Population of Georgia by 5,000, the Population of the United States by 5,000, and the Population of North America by 5,000. That’s a bit of a nightmare. Conversely, what happens if I get an update to the population of North America? How do I push that data down to the lower levels? Allocation rules? Let me tell you something about allocation rules… they aren’t fun. I still get the shakes when I think of a system I worked on that had to manage financial allocations.

So, all that being said, be a purist and approach this question optimistically. If you’re not sure, assume you can define Population at the CITY level and aggregate up as needed. If someone proves you wrong, ask yourself if you really need Population at every level. Remember, you can always change it later, and use physical implementation to manage your data. Let the structure of the ERD work for you and keep things as simple as possible for as long as possible. We’re going to remove Population from STATE/PROVINCE, COUNTRY, and CONTINENT.

I could make the same argument about Area (as in the measurement width X height). But looking at the business rules, how often does the area of a continent change? Especially the “official” measurement. Same goes for COUNTRY and STATE/PROVINCE. While I could theoretically aggregate this data, I don’t see a lot of value in it. Entity-Relationship Modeling isn’t all about following checklists and rules. Sometimes you’re going to have to make judgment calls. Don’t panic. First, you’re pretty smart. Second, you can always change the model if you chose wrong.

Let’s take a quick look at the current state of our model now that we’ve made some tweaks:

Wow, she’s looking pretty good so far. Let’s see what else we can to do polish her up.

ATTRIBUTE GRANULARITY

We touched on attribute granularity a little when talking about Population and Area. We also have another, less obvious, issue. Take a look at STATE/PROVINCE. We have two attributes, Capital City and Largest City. You can definitely make the argument that these attributes help define a STATE/PROVINCE. But, sitting back and thinking about it for a while, we also have a CITY entity. We could also define a CITY as being the Capital City or the Largest City.

Plus, remember that principle we talked about earlier? Entities own their data. Which entity owns information about cities? CITY does. STATE/PROVINCE has a relationship to CITY, but it shouldn’t be in the business of defining information about cities. CITY is quite capable of doing that herself, thankyouverymuch. I want attributes to define business keys at the lowest level possible. Plus, we’ve got a potential data integrity issue here. What if someone fat-fingers the value for Capital City and types “Sacremento” instead of “Sacramento”? And what if I want to know the population of the capital of California? Now I can’t get that information from CITY, because the misspelled name doesn’t exist in my table. We’re going to move Capital City and Largest City out of STATE/PROVINCE and into CITY, and instead of having to store the name of the city again, now we just need some sort of indicator.

IS MY ATTRIBUTE REALLY AN ATTRIBUTE?

The final area of refinement we’re going to discuss is the one you’re most likely to miss. Again, don’t panic. If you miss it, remember that you can always clean it up later. What we want to do is look at each attribute and ask the question, “Is this really an attribute?”

Let’s take the Currency attribute in COUNTRY. At first glance, yeah, a country has a currency. Makes sense. But, let’s dig in a bit more.

1) Does this attribute have meaningful information I’d like to store about it?

Remember the initial definition of an Entity? A thing/object that is significant enough that we need to know information about it. What might we want to know about currency? Well, we might care about exchange rate and stability. I can’t throw Exchange Rate into COUNTRY, because it doesn’t describe the COUNTRY, it describes the CURRENCY. (Attributes should not describe other attributes) Oh, look at that… CURRENCY just got upgraded to ALL CAPS. It’s an entity now.

2) Will the values in this attribute repeat?

Another way to evaluate an attribute is to look ahead to the data that might be stored in it. Looking at Currency again, let’s think about what will end up in this attribute. Many countries have their own currency, so those values wouldn’t repeat. But… Euro-Zone. Most of Europe is now on a single currency. Do I want to store the value “Euro” multiple times in the COUNTRY entity, or do I want to store it one time in a CURRENCY entity and allow the COUNTRY entity to inherit the CURRENCY key? Naysayers will tell you that more entities make a model overly complex. I disagree. Having to manage one value across multiple instances of an entity makes a model complex.

<Soapbox 2> I believe that data modeling is 90% thinking and 10% physical labor. This can be tough for someone who likes Seeing Things Happening to understand. Give yourself time to think. Digest your model, think about it, and then let it stew for a bit. If you’re anything like me, you’ll be in the shower at 6:00 in the morning, and somewhere between shampoo and conditioner, you’ll go, “Hey! Currency isn’t an attribute!” Epiphanies are important, and fight for the time to allow them to happen. </Soapbox 2>

Now we have two good arguments for a CURRENCY entity. Let’s add it to the model:

This has turned into a pretty clean model. We’ve eliminated our Many-to-Many relationships, ensured that attributes live in the correct entities, that attributes really are attributes. I’m pretty happy with where we ended up. As I’ve said a few times, there is so much to learn about Entity-Relationship Modeling that there is no way we could cover it in a single week. But, thank you for sticking it out and reading what I have to say about one of my very favorite topics.

Now get out there and model something! It’s fun, I promise!

–Audrey

As promised: Resources!

Community Members:

• Karen Lopez (B|T)
• Louis Davidson (B|T)
• Thomas LeBlanc (B|T)

A note: Not only do these three people write about data modeling, they also present regularly. Keep an eye on their blogs for great content and information about where you can go watch them teach in person.

User Group:

PASS Data Architecture Virtual Chapter

Books:

Case*Method: Entity Relationship Modelling by Richard Barker

This book is the one I received as course material in my very first database design class in 1997. I still use it. In fact, it’s sitting beside me right now so I can reference it as I work on this post. It’s out of print, but you can still find new and used versions for sale online. First published in 1990, it’s my personal definitive guide to purist modeling. It says Oracle on the cover, and you’re going to see COBOL code inside, but trust me… you’ll learn from this book.

Pro SQL Server 2008 Relational Database Design and Implementation by Louis Davidson, et al

Louis does an amazing job of teaching relational data modeling as well as couching his work in the context of SQL Server. If you’re a SQL Server professional (or want to be), pick up this book.

Data Model Patterns: Conventions of Thought by David C. Hay

Once upon a time, I used to think that all work I did had to be original. Now, I’m older and wiser. Mr. Hay provides an entire book of data model patterns for different industries and types of data. It’s a great reference to see how to do it right, and to get some ideas for your own database.

Hey there! Welcome back! Or just Welcome, whichever applies. This week at SQL University (organized by SQLChicken himself, Jorge Segarra) we’re discussing data modeling. On Monday, we talked Conceptual Modeling. Today, we’re going to talk about Entity-Relationship Modeling. Now, fair warning, this is a BIG topic. People spend their entire careers working with ER Diagrams, and there is a plethora of material out there for you to peruse. Also, today I’m going to focus on OLTP, or transactional, data models. There is an entire discipline focused on OLAP, or dimensional data models (data warehousing), but I firmly believe that you’ve got to understand how to build an OLTP model before you tackle an OLAP model. I’m going to hit the high points, hopefully give you a few things to think about, and and direct you to some materials (and people) that can help round out the topic. Obviously, this isn’t a comprehensive list, but it’s a really good starting point.

Resources

Community Members:

• Karen Lopez (B|T)
• Louis Davidson (B|T)
• Thomas LeBlanc (B|T)

A note: Not only do these three people write about data modeling, they also present regularly. Keep an eye on their blogs for great content and information about where you can go watch them teach in person.

User Group:

PASS Data Architecture Virtual Chapter

Books:

Case*Method: Entity Relationship Modelling by Richard Barker

This book is the one I received as course material in my very first database design class in 1997. I still use it. In fact, it’s sitting beside me right now so I can reference it as I work on this post. It’s out of print, but you can still find new and used versions for sale online. First published in 1990, it’s my personal definitive guide to purist modeling. It says Oracle on the cover, and you’re going to see COBOL code inside, but trust me… you’ll learn from this book.

Pro SQL Server 2008 Relational Database Design and Implementation by Louis Davidson, et al

Louis does an amazing job of teaching relational data modeling as well as couching his work in the context of SQL Server. If you’re a SQL Server professional (or want to be), pick up this book.

Data Model Patterns: Conventions of Thought by David C. Hay

Once upon a time, I used to think that all work I did had to be original. Now, I’m older and wiser. Mr. Hay provides an entire book of data model patterns for different industries and types of data. It’s a great reference to see how to do it right, and to get some ideas for your own database.

INTRODUCTION

So, what is an Entity-Relationship Diagram (ERD)? It is: A visual representation of entities, the relationships between entities, and the attributes that describe them.

At the end of the day, an ERD has three major components:

• Entity – a thing/object that is significant enough that we need to know information about it
• Relationship – an association between two entities
• Attribute – something that describes the entity or the state of the entity

At this point, it would be easy to say, “Oh, I get it… tables, foreign keys, and columns!” Resist that urge. There is a lot of value in getting your mind away from technology and database platform for a while and thinking in terms of an ERD. Why? Well, your focus isn’t really on defining a database yet. I mean, we all know how this story ends (spoiler alert: with a database), but let’s let this plot thicken for a bit before we read the back page. What you’re doing at this point is defining the business. If you do it right, someone should be able to look at your ERD of Widgets, Inc. and with a little understanding of notation be able to say, “Ah, I see what they do for a living.”

Also, Entity-Relationship Diagram <> Database. Yes, what you model with an ERD will probably end up in a database, but I think that unless your database is pretty narrow in scope, you’ve probably got multiple ERD’s in a single database. Or conversely, you might have many databases that reflect a single ERD. It’s why Entity-Relationship Diagrams are referred to as “logical models”. You’re not defining the physical structure of the database; you’re defining the logical structure. The physical structure has a whole other set of factors to consider.

ENTITIES

As I mentioned above, an entity is a thing. Looking at Thesaurus.com, there are a few other synonyms for entity that apply in this context: Individual, Item, Material, Something, Stuff

And an entity doesn’t have to be a physical thing. It can be a concept or idea. It’s a NOUN. Remember that if nothing else sinks in today: entities are nouns.

As with all things, there are some guidelines you should follow. Building on the conceptual model we started with the last post, let’s start looking at an Entity-Relationship Diagram that models Country. (The nations, not the music.)

1) An entity is one thing, and that one thing should be defined by only one entity.

The entity COUNTRY is one thing, a single concept. If I follow this rule, I shouldn’t have a second entity named NATION, because that overlaps with COUNTRY.

2) An entity should have a name that represents the type of thing, not an instance of the thing.

This can be a tough one. Basically, your entity name should be high-level enough that it doesn’t limit how you can describe it later through relationships and attributes. For example, my entity is named COUNTRY, not UNITED STATES. COUNTRY is the type of thing, UNITED STATES is an instance of that thing.

3) An entity must be uniquely identifiable.

If my entity isn’t uniquely identifiable, then it isn’t a single thing. It’s multiple things, and in the world of data modeling, trying to cram two things into one thing is a bad thing. As you’re making your list of entities, ask yourself: Is there something about each occurrence of this entity that differentiates it from every other occurrence? That something is your natural key, or business key.

Aside: Next time you see my fellow Datachix Julie Smith, ask her about business keys. Then go get some popcorn and enjoy the rant. Take your time; she’ll still be going strong when you get back.

4) Entities are singular.

Why? Because they represent one thing! For some reason, there’s a lot of debate about this. Just because your entity has multiple instances of that thing – COUNTRY has United States, Canada, Mexico, etc. – doesn’t mean that you’re talking about Countries. You’re defining an entity that contains instances of COUNTRY. Thus, singular.

Notation

Entities should be represented by a rounded rectangle with the entity name in ALL CAPS inside of it. If it helps, make note of a few instances of the entity. If you know the natural key already, you can note that too.

So, let’s list some entities and occurrences related to this Country ERD:

The next step is to define the relationships between these entities.

VERY IMPORTANT CAVEAT: I’m using this subject area for illustrative purposes. My goal is to keep things as simple as possible, so I know that there are a lot of other things I might do with a Country ERD, and that there are some business rules I’ve oversimplified.

RELATIONSHIPS

What is a relationship? It is: A named, meaningful relationship between two entities.

Each relationship has two sides, and there are characteristics of those sides that need to be defined. If you can’t define the relationship, then you might not have one. It’s okay – there are always other fish in the sea.

1) Name – this is commonly known as a “verb phrase”. Where an entity is a noun, your relationships are verbs. Try to come up with meaningful verb phrases. If you’re stretching and end up with 42 relationships with verb phrases like, “is related to”, then you might be forcing it.

Let’s try one: COUNTRY and STATE/PROVINCE

Verb Phrase: A STATE/PROVINCE is contained within a COUNTRY, and a COUNTRY is made up of STATE/PROVINCE

2) Cardinality (how many) – there are three kinds of relationships: One-to-Many, Many-to-Many, and One-to-One

If we refine our verb phrase, we can add information about cardinality. In this case, we have a One-to-Many relationship between STATE/PROVINCE and COUNTRY

A STATE/PROVINCE is contained within one COUNTRY, and a COUNTRY is made up of one or more STATE/PROVINCE

3) Optionality – this tells us whether there is a mandatory relationship or not. Some entities must be related, others are optional. Let’s refine the verb phrase again:

A STATE/PROVINCE must be contained within one COUNTRY, and a COUNTRY may be made up of one or more STATE/PROVINCE

Notation

There are many different notations, but I prefer what is known as Crow’s Foot notation because it is simple and I’m comfortable with it. But, use what works for you. Just be consistent.

I like this notation because it tells you a lot about the relationship in a very concise way. Reading it can take a little getting used to, but here’s what you do: Starting with the entity and following the relationship line that comes out of it through to the related entity, say…

“[ENTITY NAME] [optionality] [name] [cardinality] [ENTITY NAME]” where optionality is denoted by solid (mandatory) or dashed (optional) line and cardinality is denoted by crow’s foot (many) or a straight-line (one) connector.

Such as, “STATE/PROVINCE must be contained within one COUNTRY”. I know it’s a lot to take in, but practice a little, and it’ll make sense. I promise.

What you’ve defined with a relationship is a business rule. If you’re lucky enough to have business rules in advance, that’ll take you a long way towards defining your relationships.

ATTRIBUTES

Let’s talk attributes. Attributes are a detail that helps to describe the entity or the state of the entity.

If I’m looking at my COUNTRY entity, I might have many ways to describe it and its state

• Name
• Population
• Sovereign?
• Established Date
• Government Type
• Currency
• National Symbol
• Language

These are potential attributes for my entity. Why do I say potential? Well, because until we start filling out this model, we’re not really sure whether they fit as attributes or not. Attributes have rules:

1) Attributes should depend on the key.

So far, I’ve defined my natural (or business) key of Country as Country Code. Each attribute needs to be examined and the question asked: Does this attribute define that key and nothing else?

2) Attributes should be atomic.

This means that an attribute serves as one descriptor of the entity. It can’t represent two things. Say I had an attribute called LanguagePercent that contained information like “English-60%” and “Spanish-40%”. This needs to be handled differently. (Come back on Friday when we talk about ERD refinement to see how)

3) Attributes should not repeat.

Taking the Language attribute, let’s think of this in real-world terms: Some countries have a single, official national language (I’m looking at you, France), but others have multiple recognized languages. Belgium has three: Dutch, French, and German. I don’t want to have to add attributes to my entity like Language1, Language2, Language3. There is a solution: LANGUAGE isn’t an attribute after all. It’s an entity, with a relationship to COUNTRY. How about that? We just refined our list of entities.

What’s really cool about this is that COUNTRY and LANGUAGE actually have a Many-to-Many relationship once you break it out and start working through your verb phrase:

“COUNTRY must communicate via one or more LANGUAGE, and a LANGUAGE may be the communication method for one or more COUNTRY”

Many-to-many relationships are valid business/real world relationships, but they don’t fly within a database. On Friday when we talk ERD refinement, we’re going to have to address this.

4) Attributes should not describe other attributes.

What if I had two attributes, National Symbol and Has National Symbol? Well, the information contained in National Symbol is going to be dependent on whether there is one. We don’t want that. Our attributes should only define the entity.

Given this, we have the start of an Entity-Relationship Diagram for Country.

We’re going to stop here for today. For my homework, I’m going to go through the rest of the entities, define the relationships between them, and then define attributes. If you’re feeling frisky, try fleshing out the model on your own. On Friday, we’ll compare notes then start the very important refinement process. This is where we take a first draft of an ERD, and see how we can improve it. Data Modeling is an iterative process. No one creates a perfect model on the first try – that’s why we’ll spend Friday learning about how to methodically refine our ERD.

Thanks for reading, and I hope to see you again on Friday.

–Audrey

Welcome to SQL University’s Fall 2011 Semester week of Data Modeling.  I’m thrilled to be a part of this great endeavor again, and a giant thank you to the man behind the project, Jorge Segarra (Blog|Twitter).  I hope you enjoyed Julie Smith’s (Blog|Twitter) SSIS Deployment posts last week, and are looking forward to Stacia Misner’s (Blog|Twitter) upcoming week of MDX.  I can’t even tell you how cool it is to be scheduled between these two amazing women.

So… Data Modeling!  Well, not quite yet. I want to take a step back and talk Conceptual  Modeling. Let me explain why. Every good IT person I know is a born problem-solver. It’s one of the reasons we love what we do. We are presented with a problem and get our thrills from finding a solution. But here’s the thing… We’re so enthusiastic about finding a solution that we occasionally jump right past understanding the problem at hand and just start doing stuff. In our world, the results of solutions to undefined problems can vary from the happy accident of an appropriate fix to a snowballing A Simple Plan-esque disaster.

Conceptual Modeling sounds fancy. Kind of like metadata, paradigm, sommelier, or obfuscation.  You’ll sound impressive when you say it, but conceptual modeling really is far simpler than it sounds. It’s just a picture of what’s going on or what you want to see going on. For example, in trying to think of a non-technical example of conceptual modeling, it turned out that my 9-year old self became Exhibit A. When I was a kid, my room was usually a mess. So much so, that it was a little overwhelming when my mom would tell me to clean it up. My solution was to draw a map of my room, complete with furniture placement, break the room into “zones”, and assign each zone a number. Then, I’d clean one zone at a time.

What was I doing? I was modeling my room. It allowed me to see what the problem was (the messy room), the shape of it, and where all of the parts were located. Given this, I was able to break the problem into manageable pieces and begin tackling them one at a time. That’s all conceptual modeling is:

1) What is the environment?

2) What are all of the parts (or subjects, components, etc.)

3) How do they relate to each other?

That’s it. That’s one example of a conceptual model. Is there a formal methodology? I suppose so. (http://en.wikipedia.org/wiki/Conceptual_model) But I want to get you thinking less about notation and vocabulary and more about the purpose of conceptual modeling. Bottom line, do what works for you and how your brain functions. Your primary objective is to draw a simple picture of the environment you’re in and the challenge in front of you. Also, step away from the keyboard. I mean it. I hear they still sell paper at office supply stores. While you’re there, grab a pencil or two. Or better yet, get a whiteboard and a couple of markers.

There are some real advantages to starting a development process with a conceptual model. First, you lay out the problem before you start mucking around with things. Second, you can use the model as a way to organize your thoughts and clear the clutter of available, but possibly useless, information. Finally, (and this is a big one) you can use the model as a way to communicate what you think the design path is. It isn’t hardcore design; think of it as problem definition. You’re not modeling to solve a problem – you’ll do that when you design the solution – you’re modeling to define the problem. The more well-defined your requirements are, the smoother design is going to go.

I usually end up with one of two kinds of models: Workflow (Point A to Point B) or Contents (what’s included in X?).

Conceptual Model #1: Workflow

Just last week, I had a work item that said to add all of the ISO 3166-1 Standard Country Codes to a collection of databases.  Sounds simple enough.  Except there were a few hitches.  Here are the requirements I had to meet:

1) Each database should be standardized – all ISO Codes need to be added to the Country table in each database

2) Not all databases have a Country table already, if there is one, use it.  If not, create it

3) For the databases that have a Country table already, you can’t remove anything that’s already there – existing columns and rows have to go untouched

4) For countries that are already populated in Country tables, you can’t modify any data, you can only add additional data (in the form of columns)

5) There should be one DDL script and one DML script that can run in any environment we have

Not insurmountable, definitely, but it took a little planning.  This is where a workflow-focused conceptual model came in really handy.  I start with Point A and Point B, then start filling in what it takes to get me from one to the other.

Let’s walk through the process…

• What is the end state I want? I know what I want the Country table to look like – I write it down. I know that I need to keep my original Country and CountryName columns, and that I’m going to add CountryThreeLetterCode and CountryNumericCode.
  

  I promise, we'll fill in this whitespace

• What is the current state? I have 3 possible scenarios

• What do I need to do to get from Current to End State? The biggest generalization I can think of is “some DDL and some DML” (I need to modify the structure of the database and then work with the data)

• What kinds of things am I going to do in these DDL and DML scripts? This is where I officially turn my back on formal methodology – I make notes, put in whatever detail makes sense to me, and basically jump all over the place

• Which comes first, DDL or DML? I can’t add any data until the table’s in place, so I’m going to run DDL first, DML second

• Within DDL and DML, what order should things happen in?  DDL has a great example of how the illustration helps us think things through – if I create the table when it doesn’t exist and then look for the existence of the table to alter it, I’m going to try to alter the table I just created, probably generating an error – instead, I’ll address existing tables before I add missing tables

At this point, I have a pretty good picture of what I want to do and what order I want to do it in.  Now, I’ll just go write the code for each of the items in my flow.  Easy-peasy, mac and cheesy. Keep in mind, I’ve made this PowerPoint-pretty for the purposes of the blog, but in real life, these were scribbles on a whiteboard. Informal and quick, it just helped me create a picture of what I needed to do and how to get there.

Conceptual Model #2 – Contents

Next up is a conceptual model that focuses on contents. This could refer to anything from “What does our server infrastructure look like?” to “What functionality is going into this system?” to “What are we going to store in this database?” to “What exactly is in a McRib sandwich?”.  There are all kinds of questions you can answer with a model like this. Knock yourself out coming up with ways to use a content-based conceptual model.

Let’s say that you added those country codes to your database, and your manager comes to you and says, “We were thinking… maybe we should add more information about the countries.  You know, country-type stuff”.  This is a perfect opportunity for a model that’s all about the information you want to store; you could stretch a little and call it the country’s contents.  There are a few things you can accomplish with a model like this:

1) Brainstorming
2) Organization
3) Communication
4) Foundation

Since you didn’t get a lot of information from your manager, you’ve got a blank slate.  Let’s brainstorm all of the things we could store about a country.  Don’t be shy; just get down everything you can think of.

There’s some overlap here.  That’s okay, because now we can organize our thoughts a bit.

Now that you’ve got a starting point, you can use this as a communication tool.  Take it back to your manager and say, “Is this what you were thinking?”  If you’ve got a non-technical audience, a conceptual model is great because it’s not intimidating.  Your manager takes a look and says, “Well, I was thinking along the lines of geography, demographics, and government.  I don’t care so much about culture or history or military”.  Cool!  You’ve just improved your design requirements

You have a better idea of what you’re trying to accomplish, you’re on the same page as your boss, and you have a foundation for the next design steps.  You can go a couple of different directions from here.  You can walk away from the conceptual model, or you can begin to drill down.  If you drill down far enough, you’re going to end up with the start of an Entity-Relationship Diagram (ERD).  What I would probably do in this case is take one of the subject areas I want to include and start drilling into it.  Maybe it becomes its own conceptual model.

Now you can cycle through the whole thing again.  Maybe you take this geography model back to your boss and he says, “Oh, by geography, I guess I meant ‘geo-political’.  Thanks for clearing that up. You’re such a genius. Let me give you a raise. And a bonus. Can I get you some coffee?”  You’ve continued to proactively clarify and refine your requirements. People are going to appreciate that. A little up-front conceptual modeling can save you a lot of time in the long run.

However, there are potential downsides.  You can go down the rabbit-hole with conceptual modeling. Resist the urge to use it as a procrastination tool.  It should be a quick process, and you should only model as far as it adds value for you. Documentation for the sake of documenting is a waste of time.  Use the model as a tool to get to the next step in development.

I hope that this has explained what Conceptual Modeling is all about, and has motivated you to try it out for yourself.  I use conceptual models all the time, and I’ve found that they’re a great tool to have at my disposal when I need to organize my thoughts or clear up what it is I’m trying to accomplish.

Homework: 

Find an opportunity to model something. Find some task you have, technical or non-technical that could benefit from a little time invested in laying out a conceptual model. Or, take this country concept and see where it takes you. You could go in a hundred different directions with a topic this broad.  Thanks everyone for reading today, and I’ll be back on Wednesday with more modeling goodness!

Model on, my friends…

–Audrey

Welcome back.  Today we’ll finish up this whole series by  Executing packages with the SSIS Catalog in SSIS 2012

This is the conclusion of my series on Deploying and Executing Projects and Packages in SSIS 2012.  If you are just joining us, you may want to read Lesson One and Lesson Two, Part A.

We’ve deployed the project to a folder in SSIS Catalog, now let’s create Environments and Environment Variables inside the SSIS Catalog:

1. Right Click the Environments folder under your Parent SSIS Catalog folder.  Select create environment.  Give the environment a name. Click OK.  I called my first environment Dev.
2. On the environment you just created, right click properties.  Select the variables tab.  Type a name to create a new variable and select a logical datatype.  I called mine BikeServer
3. Create another environment following the same process. I called mine QA.
4. Create an environment variable in the new environment that has the same name and datatype as the environment variable you created in the other environment.  So my second environment variable would also be called BikeServer.

Diagram 1 – 3. My two Environments– Dev and QA

Diagram 2. My Environment Variable called BikeServer I gave it the value of the BikeServer on the Dev server which is Dev42.  For my QA Environment, I created the same variable, but gave it the value of the QA server, QA42.

So you’ve now got your environments and they each have an environment variable called BikeServer, which has its value properly set.  We are going to use the environment variable to change the server name according to which Environment you use.  You’ll see in a minute.

To configure the project to use the Environments and the Environment Variables:

1. Select the Projects folder.  Right click your Project and select Configure
2. Select the references page in your project configuration dialog box.
3. Click add.  Select an environment from “Browse environments window.  You can add any or all of the environments you’ve created in the project folder.  The diagram shows that I already added Dev and am about to add QA.

Configuring the Project to use the Environments.  Start with the References tab to add the Environments

4. Now go to the parameters page.   I am using the Environment Variable to control my server name, so I’m going to go to the Connection managers tab inside the Parameters page.

On the Parameters tab.  I’m using my BikeServer Environment Variable to change the value of the ServerName in my connection manager.

5. Click the ellipses beside the value you want to configure.  In this case, the ellipses beside the ServerName.

6. Click radio button for use environment variable in the Set Parameter value window which comes up.  “Set Parameter Value”

7. Select the environment variable you will use, in this case BikeServer.  Click OK. Click OK to exit the configure dialog box.

Setting the parameter value.

Configuring the Execution.

Now the ServerName in your connection manager is going to use the value of one of the environment variables you configured.  To choose which one,  you must configure your execution by doing the following:

1.  Right click on your entry point package.  Select Run (this is configuring your execution, not running it yet).

2 . When the Run Package dialog box opens, note that you now have to choose an environment, or explicitly supply a value for ServerName, because in the project it has been configured to use an environment variable.  We will click the check box by Environment and select Dev.  Recall that the value of BikeServer in the Dev Environment was Dev42.

You made me this way, now you have to choose an environment!

3. Click ok.  This will start the manual execution of your project.  The value of the server name in the connection manager will be DEV42 in this execution, because that is the value of the Environment variable inside the Dev Environment in the SSIS catalog.

Using Agent to schedule jobs with SSIS Catalog projects:

It’s nice that we have a more convenient manual way of executing packages; however, most of the time the goal of packages is to automatically run them on schedules.  To achieve this with SQL Server Agent, the SSIS Catalog is now available as a package source when configuring agent jobs.

Agent Package Source

Well kids, thanks for hanging in there through this whole class.  The SSIS Catalog is very nifty, but also very different than what we are used to.  I hope this post will keep someone from falling in the same holes I did while I was learning.

For more excellent reading on SSIS 2012, please check out Jamie Thomson’s blog posts on the topic.  Also check out the official SSIS team blog.  Thanks to Matt Masson (T) of that team for his advocacy for SSIS 2012.  Matt rocks!

Thanks to Jorge for starting SQLU, and for inviting me to present on a topic I find really exciting.  Be sure to check out Audrey’s week next week at SQL U, she’ll be covering Data Modeling.

 Lesson Two.  Deploying Projects in SSIS 2012 (Today’s class is longer than I expected, we’ll take a break and finish tomorrow.)

Before we get started, a big thank you to Jorge Segarra (blog|Twitter) for inviting me to write for SQL University .

PreRequisite:  Lesson One

By the end of this part of the lesson you should understand the following processes/terms.

1. Building SSIS Projects
2. Ispac files
3. The SSIS Catalog
4. Deploying ispac files

By the end of the second half of the lesson, the following will be covered:

1. SSIS Catalog Environments
2. SSIS Catalog Environment Variables.
3. SSIS Executions
4. Building Agent jobs with SSIS Catalog projects.

So you’ve got your Project with a Parent package.  (by the way, you should designate Parent Packages as Entry Point packages inside a project by right clicking them and selecting “Entry Point Package”).  You’ve got some child packages.  You’ve got some variables.  You’ve got project level parameters for values which should be available to all packages inside a project (like say, a directory for importing/exporting files, a client to process, a business unit to process, etc. ) and you’ve got package parameters for values which need to be passed from parent packages to child packages.  You’re ready to Build, Deploy and Execute in SSIS 2012 utilizing all of its new toys features.

 

Oooo shiny new SSIS Project—Ready for Deployment

 

I briefly touched on how a “Build” in SSIS now generates an ispac file in Lesson One.  To build the project and create the ispac file , simply Right Click the project and select Build.  Or click on Build from the main menu in SSIS and select Build __ (projectname).   The ispac file will go to the bin/Development directory under your root project directory.

An ispac file.  It contains all the elements in the project.  Packages, parameters, connection managers, everything.

Now you’ve got a file to really deploy.  Good for you.  You’ve hung on this far.  Where are you going to send the little ispac file?

There is a new destination for Project Deployed Projects.  It is called the SSIS Catalog. And it’s kind of a big deal.  Think of it as ETL Ground Control.

The SSIS Catalog is located inside Management Studio, under the typical place most of us spend all of our time:  Database Engine Connections.  (By the way, the Integration Services Service connection is still available.  Remain Calm! )

A new Node.  A new (non-MSDB) Era.  SSIS inside Database Engine connections.

What the SSIS Catalog gives us:

• Integration Services Dashboard– This refers to the built in logging available to all Projects deployed–regardless of logging coded in the package design (old school, still available, remain calm).  There is a suite of reports available to peruse regarding package executions.  See Jamie Thomson’s post here on the reports and logging:  http://t.co/OOoLwgh.  Again, to my veterans, there is a way to integrate old school custom logging with the new logging in the SSIS Catalog.  There is a new system variable, ServerExecutionID, which premiered with RC0, that you can add to your custom logging.  That way, you retain your old logging and are also able to link your old auditing data to the new logging in 2012.  Logging is one feature in SSIS that the old and the new can both be used in one project.

Use a familiar SSRS looking dashboard to snoop on your SSIS.

• A more robust control panel for manually executing packages– especially the ability to manually override connection managers and variable values–something that was trickier than it should have been pre-2012.
• Folders.  I know that “folders” have been around for a long time.  And the folders inside the SSIS Catalog are pretty plain.  They are only one level deep, available for creation under the main node of Integration Services, no customized subfolders.  BUT when you create a folder, you automatically get the Projects folder and the Environments Folder.  Folders give the benefit of logical grouping AND security is configurable at the folder level.

We brought our own subfolders, thanks!

 

• Environments–great idea!  Environments are catalog objects which enable you to pre-set suites of values for configured items in a project.  An example would be to use one each for your Production, QA, UAT, and Development environments.  You could configure the proper server names for the connection managers.
• Environment Variables.  Oh Boy.  These also are a great idea.  But the name is unfortunate.  You know why?  Because in legacy deployment model and configurations, there’s also a beast named Environment Variables (see http://msdn.microsoft.com/en-us/library/ms141682.aspx Direct and Indirect configurations for more info.  )  Anyhoo, Environment variables in the context of the SSIS catalog are variables inside Environments which enable you to substitute variable values for the values of connection managers and or other parameters in the packages you execute.

Let’s use what we’ve learned so far and deploy a Project!

You deploy projects with the Integration Services Deployment Wizard.  Launch the deployment wizard from BIDS or from the SSIS Catalog.  From BIDS, right click the project and select deploy.  From the SSIS Catalog, right click on the projects folder underneath a main folder and select deploy.  The same wizard appears.  You can deploy  a file (ispac) or you can grab a previously deployed project from the SSIS catalog and deploy it to another folder on the same Server, or to a different server.

Wizards.  There’s always a wizard.

Steps of deploying from the Integration Services Deployment Wizard once it is launched:

1. Introduction. There is an optional Introduction page.  Click Next (you can also click the box to never see it again)
2. Select source.  Select your source project.  If deploying in BIDS, this is most likely the ispac file you just built, which will be default value.  You can also navigate to a project on a server which has already been deployed.
3. Select destination.  This is the folder inside your SSIS catalog where you want to deploy the project.  You can choose a folder under your catalog you have already created, or you can create new folder from the deploy wizard.

Destinations.  You can create the new folder here under the catalog if you ‘d like, or choose an existing folder.

 

4.  Review/Results.  You get to watch it spin and watch the steps as they succeed or fail.  Note the changing protection level.  The SSIS Catalog will use its own key to encrypt sensitive items in projects and packages, and you’ll never have to worry about which encryption method to use AGAIN.  What will the certification exams use now as material to confound us?

Most Deployment wizards do not come with screaming Kevin Bacon.  I special ordered mine.

Well kids, we’ve covered a lot and you all look hungry.  You guys run out to the snack bar and get some caffeine, and we’ll push through the rest of the material when you get back.  Will someone bring me back some animal crackers?

We’ll finish this tomorrow.  Thanks Jorge for SQLU!