SQLHangout #26 – Biml in the Real World

Back in June I did a #SQLHangout with Boris (@BorisHristov) about Biml that I called “Blabbering about Biml“. (Boris is much nicer, he called it “Biml. An Introduction.“)

Yesterday we did another fun #SQLHangout about Biml. This time we were joined by João Lopes (@SQLSniper), Rasmus Reinholdt (@RasmusReinholdt) and Régis Baccaro (@regbac) – great guys that I admire. It was interesting to hear how we use Biml in different ways and what we would like to see happen in 2015. I look forward to seeing all of them again and learn from their experiences!

Five people from Bulgaria, Denmark, Portugal and Norway talking about Biml – isn’t that awesome? :)

SQL Server User Group Norway 2014 Summary

This has been a great year for SQL Server User Group Norway. It has been fun to see the user group come back to life and I’m very excited to share some news about our plans for 2015! But first, a summary of what happened in 2014.

SQL Server User Group Norway 2014 JanuaryAfter getting our new website up and running we kicked off the year on January 23rd with a Hekaton presentation by Erik Flateby, and what we called an “interactive session” by me. I talked about PASS and everything it has to offer, showed our new website and how to join our local chapter, and did a survey to figure out who our members were and and what they expected from the user group. Most of the members worked in BI.

On March 5th we had Erin Stellato (@erinstellato) do a remote session for us about Extended Events, and Erik Flateby talked about Columnstore Indexes. Erin did a great job presenting without seeing her audience, and it was a good session even with a few technical problems on our side. Lesson learned: have two-way video for our next remote session.

Shortly after, on March 31st, Hans Olav Norheim (@norheim) presented Azure SQL Databases. He had traveled from the US to be part of the SQL Server 2014 launch event in Oslo and said yes to present for our user group. We were slightly disappointed that we didn’t have as many attendees as we had hoped for. We thought it was because we didn’t promote the event enough or because our meeting was so close to another SQL Server event where Hans Olav presented a similar topic. Lesson learned: Promoting too much is better than not promoting enough!

SQL Server User Group Norway 2014 MayOn May 12th it was time for my favorite event of the year, the Biml techie meetup with Varigence and Rehfeld :) I thought it was an amazing session, but there weren’t as many attendees as we had hoped for – again. (However, that was when I decided that I wanted to spread the word about Biml and start speaking, so for me personally it was sort of a good thing.) The previous meeting and this meeting were both scheduled on Mondays, and another lesson learned: Schedule meetings later in the week so you can send reminders a day or two before the meeting.

We had a long summer break and started the fall season with a massive success called SQLSaturday #317 Oslo on August 30th. Did you know that we had to say no to a user group meeting with Brian Knight (@brianknight) that following week because it was too close to our SQLSaturday, and we just didn’t have the time and resources to promote and organize a meeting? That was such a strange experience for us, and we all wished we could have said yes. (Brian, if you would like to come back another time, I promise we will have time and resources to plan a proper meeting!)

In September we joined Meetup and moved our meetings to our new “home”, Teknologihuset (House of Communities), two of the best decisions we made in 2014. We reached more people through Meetup, and it is a great tool for organizers with all its features. I do have to admit it is a bit confusing having members spread out over the PASS chapter website, Meetup and LinkedIn. It takes a bit of extra time to schedule meetings and get the correct information out to everyone without feeling like we’re spamming our members, but we’re learning. So far it seems like it’s better to have too much information than not enough. My favorite part about moving to Teknologihuset is that it is such a cool and unique concept, a whole building dedicated to technical communities that we can use for free on evenings!

SQL Server User Group Norway 2014 SeptemberWe were thrilled to have Stacia Misner (@StaciaMisner) kick off the fall season with a presentation about Multidimensional vs. Tabular for us on September 24th. She spent some days in Oslo doing other events and also brought Dean (@mr_stacia) with her, so I named it the Stacia Misner Week :) A new experience for the board members of our user group was that we didn’t have to look for a meeting sponsor, we had some sponsor funds left from SQLSaturday Oslo to cover food and drinks. One of the goals of a SQLSaturday is to be fundraisers for the local user group(s), and I’m happy that we were careful with how we spent our money. It is a great feeling knowing that you can organize user group meeting without worrying about sponsors.

Just a few weeks later we had another familiar face visit our user group. Peter Myers presented Data Mining on October 13th. I couldn’t make it to the meeting, but Peter got great feedback from the attendees. It was great to see many new people sign up for our meetings after the summer, and to see the average attendance rate go up.

On November 20th we welcomed our first new, local speaker! Marius Pedersen (@soylentshell) presented Maintenance Plans. One of our goals in SQL Server User Group Norway is to grow new speakers. We want to give new speakers a chance to practice presentations before submitting to larger events, and of course we want to build a community of sharing knowledge. I was so happy that Marius wanted to present, and I hope to see more new and local speakers in 2015. Try it one time, maybe you’ll get addicted! :)

SQL Server User Group Norway 2014 DecemberThe last speaker of the year was Marco Russo (@marcorus) who took us on a tour inside Power Query on December 1st. We had three new attendee records! Most attendees registered, most attendees who showed up, and most first-time attendees. One lesson learned with Meetup is that it is very easy to RSVP yes for anyone, and this can make it difficult to plan meetings. Our meetings have had at least 25% fewer attendees show up than the number of registered attendees, so we always count the actual attendees during meetings.

What a year! I’m so proud of our user group. In May Tone S. Hansson (@ToneHansson) stepped up as the new Chapter Leader, and she has done a phenomenal job. Just wait until you see what happens in 2015! Say thank you the next time you see her, she deserves it :) In November Rune Ovlien Rakeie (@runeo34) joined the board, and we were happy to welcome a proper DBA to the team. Let’s make 2015 an even better year for SQL Server User Group Norway! :D

Giving back to the SQL community in 2015

T-SQL TuesdayWayne Sheffield (@DBAWayne) is hosting the last T-SQL Tuesday of the year, and he wants to know how you plan on giving back to the SQL community in 2015.

My short answer is to continue to do everything I did in 2014 – and some more.

In 2014 we restarted our local PASS chapter, SQL Server User Group Norway, and in 2015 I will continue to be a board member and help organize meetings. It has been great to see our chapter grow this year. We have more members, but more importantly, we are slowly becoming a community. Members have volunteered to help out with events, they want to start speaking, and it’s great to see former coworkers catch up with each other at meetings.

We also organized the first SQLSaturday in Norway in 2014, and we have already started planning our next SQLSaturday. (We’re aiming for August 29th, so save the date!) It was a lot of hard work organizing the event this year, but it was such an amazing learning experience that I can’t wait to start working on the next event. I’m so happy that we can give the Norwegian SQL community a day of high-quality free training!

Volunteering is fun and I’ve become slightly addicted to it. I’m planning to be a helper at SQLBits XIV and I have also told the SQLRally Nordic organizers that I’m happy to volunteer if needed. The same goes for all the other PASS events. I hope I can help out again at PASS Summit 2015 and 24 Hours of PASS, and I also want to join the Program Committee and learn from that.

Next year I want to challenge myself to speak and blog more. I love to do both, but they terrify me because they push me out of my comfort zone. There will always be someone who knows more than me, who has more experience than me, who has presented something before me, but I don’t want that to stop me. I wouldn’t be where I am today if others hadn’t shared their knowledge, and hopefully I will be able to help someone by sharing my knowledge. Also, I can’t think of a better way to learn something than to figure out how to teach it to others, so I’m really just being selfish.

And lastly, I will be hosting a T-SQL Tuesday next year! I thought it was fun to share the news in a T-SQL Tuesday post about giving back to the SQL community :)

Biml Extension Methods: ImportTableNodes and ImportDB

There are two Biml extension methods for importing metadata from a database: ImportTableNodes and ImportDB. They both return an ImportResults object that contains SchemaNodes (schema metadata) and TableNodes (table and view metadata). What is the difference between the two methods and when do you use them?

The main difference between the two methods is that ImportTableNodes is restricted to importing one schema and the tables/views belonging to that schema, while ImportDB can import all schemas, tables and views. If you need to import everything in a database or import multiple schemas you will need to use ImportDB. If you only need to import a single table or import tables belonging to a single schema, you can use both methods.

You choose which objects to import by passing parameters to the methods. The parameters work as filters to get one or more objects.

Methods and parameters
ImportTableNodes requires two parameters: Schema and TableFilter. Schema needs to be the name of a specific schema, it can’t be empty and it is not possible to use wildcards. TableFilter can be empty to import all tables/views in the schema, it can use wildcards to import certain tables/views in the schema, or it can be the name of a specific table/view in the schema. Certain objects like views, keys or indexes can be excluded by passing the third (optional) parameter ImportOptions.

ImportDB does not require any parameters. If no parameters are provided it will import all schemas, tables and views. Empty SchemaFilter and TableFilter parameters is equal to not providing any parameters at all, but can be used with ImportOptions to import all schemas and tables while excluding certain objects like views, keys or indexes. Both SchemaFilter and TableFilter can use wildcards.

MethodDescription
ImportTableNodes(Schema,TableFilter)Import the schema, and tables/views filtered by both Schema and TableFilter.
ImportTableNodes(Schema,TableFilter,ImportOptions)Import the schema, and tables/views filtered by both Schema and TableFilter. Exclude objects by ImportOptions.
ImportDB()Import all schemas, tables and views.
ImportDB(SchemaFilter,TableFilter)Import schemas filtered by SchemaFilter, and tables/views filtered by both SchemaFilter and TableFilter.
ImportDB(SchemaFilter,TableFilter,ImportOptions)Import schemas filtered by SchemaFilter, and tables/views filtered by both SchemaFilter and TableFilter. Exclude objects by ImportOptions.
ParameterDescriptionWildcardsExamples
SchemaSchema to importNo"Person"
"dbo"
SchemaFilterSchema(s) to importYes"Person"
"P%"

"dbo"
"d[abc]o"
"d[^def]o"
TableFilterTable(s) to importYes"SalesOrderDetail"
"SalesOrderHeader"
"%Order%"

"Store"
"Score"
"S_ore"
ImportOptionsObjects to exclude from importNoImportOptions.None
ImportOptions.ExcludeCheckConstraint
ImportOptions.ExcludeColumnDefault
ImportOptions.ExcludeForeignKey
ImportOptions.ExcludeIdentity
ImportOptions.ExcludeIndex
ImportOptions.ExcludePrimaryKey
ImportOptions.ExcludeUniqueKey
ImportOptions.ExcludeViews

Wildcards in parameters
The SchemaFilter and TableFilter parameters can use wildcards. The wildcard characters allowed are the same as the ones used in the T-SQL LIKE statement:
The percent sign ( % ) for zero or more characters.
Underscore ( _ ) for a single character.
Brackets ( [ ] ) for a single character in a range or set.
Brackets ( [ ^ ] ) for a single character not in a range or set.

Please note: if your object names contain characters that are used as wildcards, for example underscores in table names, you can treat wildcards as literals by enclosing them in brackets. If you have two tables Product_Category and ProductXCategory and you pass the TableFilter parameter “Product_Category”, both tables will be returned because the underscore is treated as a wildcard. You must pass the parameter “Product[_]Category” to treat the underscore as a literal to only return the first table.

Example usage
Below is just one simple example of how to use Biml extension methods to connect to a database and import metadata. It shows how to use GetBiml() on the SchemaNodes and TableNodes to create Schemas and Tables, and how to loop over the nodes to create package names.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 
  <#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement" #>
  <# var conAW2014 = SchemaManager.CreateConnectionNode("AW2014", "Data Source=.;Initial Catalog=AdventureWorks2014;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"); #>
  <# var AW2014DB = conAW2014.ImportDB(); #>
 
  <!-- A few other examples of how to call methods with parameters -->
  <# var AW2014DB = conAW2014.ImportDB("Person", "Person"); #>
  <# var AW2014DB = conAW2014.ImportDB("d[abc]o", ""); #>
  <# var AW2014DB = conAW2014.ImportDB("", "", ImportOptions.ExcludeViews); #>
  <# var AW2014DB = conAW2014.ImportTableNodes("Person", "P%"); #>
  <# var AW2014DB = conAW2014.ImportTableNodes("Sales", "Sales%", ImportOptions.ExcludeCheckConstraint | ImportOptions.ExcludeColumnDefault | ImportOptions.ExcludeForeignKey | ImportOptions.ExcludeIdentity | ImportOptions.ExcludeIndex | ImportOptions.ExcludePrimaryKey | ImportOptions.ExcludeUniqueKey | ImportOptions.ExcludeViews); #>
  <!-------------->
 
  <Connections>
    <OleDbConnection Name="AW2014" ConnectionString="Data Source=.;Initial Catalog=AdventureWorks2014;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
  </Connections>
 
  <Databases>
    <Database Name="AW2014" ConnectionName="AW2014" />
  </Databases>
 
  <Schemas>
    <#=AW2014DB.SchemaNodes.GetBiml()#>
  </Schemas>
 
  <Tables>
    <#=AW2014DB.TableNodes.GetBiml()#>
  </Tables>
 
  <Packages>
    <# foreach (var table in AW2014DB.TableNodes) { #>
      <Package Name="<#=table.Schema#>_<#=table.Name#>" ConstraintMode="Linear" />
    <# } #>
  </Packages>
 
</Biml>

Please note: you may get Biml validation errors if you exclude objects that are referenced by the other included objects, such as importing certain tables with foreign keys to tables that have been excluded.

Conclusion
This post is a basic introduction to the Biml extension methods ImportTableNodes and ImportDB. It shows how to use the methods by providing parameters with or without wildcards. The main difference between the two methods is that ImportTableNodes is restricted to importing one schema, while ImportDB can import everything in a database.

Speaking at Deutsche SQL Server Konferenz 2015

Speaking at SQLKonferenz

I will be speaking at Deutshce SQL Server Konferenz 2015 (#SQLKonferenz). The conference is from 3-5 February in Darmstadt, near Frankfurt. The first day is a PreCon day with Power Workshops, and the two other days are MainCon days with four parallel tracks: Administration, Business Intelligence, Development and Information Management.

My Biml introduction session called “Generate SSIS Packages Automatically with Biml and BimlScript” is scheduled for Thursday, February 5th. The lineup of speakers this year is amazing! I hope I get to attend as many sessions as possible myself.

Hope to see you there! Please say hi if you see me :)

Page 1 of 10 1 2 3 4 5 ...10 ...Last »