Tuesday, February 19, 2019

BI Maturity Model

Our IS team had a big meeting with senior management at the end of June to demo a new application that is a centralized business intelligence platform.  It showcases tiles/cards on a web page that have KPI/metrics, exceptions, tasks to be done, news, etc.  The web application was built to be a one-stop-shop for everything needed for those who work in the field.

In preparing for this meeting, I helped develop a pyramid that explains the different levels of maturity in Business Intelligence overall, and where we are.  It will help showcase what we have developed so far and show what we want to accomplish as we reach further up the pyramid.  The model came out so beautifully, I had to share it.  This was a team effort, not just me; my co-worker had a lot of specific thoughts about it, I added to it and of course, and made it pretty.

If you have any questions, please let me know.  I will be more than happy to give you my opinion of what we talked about with this.  If you want to use the picture, please make sure to link back to this post.


Write comment (0 Comments)

SQL Foundations - Day 2 2013

This post is a continuation of an earlier post that you can find here, on Day 1 of the SQL Foundations course I attended.  The session was presented by: Allan Hirt (blog | twitter), and Ben Debow (blog | twitter) .


Day 2: Thursday, August 29th

Session 4: Indexing Fundamentals

Ben was the sole presenter on this topic. He gave many ideas to think about as well as explaining about indexing. Indexing is more than just putting an index on a table, it requires more planning. Creating an overall indexing strategy will help find that balance between SQL, performance and hardware that is the true desired goal. To start off with, focus on the highly reference objects first. Ben gave a demo which showed a great tool to use, SQL Sentry Plan Explorer, to help dive into more depth. He also showed how Excel can be used to filter and sort the index usage statistics to gain much more insight into what to tackle, and when.

He also gave multiple tips, including making sure to know when the SQL server instance has been restarted. All of the metadata which is helpful to review is cleared out when the instance is restarted.


Session 5: Administration Essentials

I was really looking forward to this session, and was not disappointed! Both Ben and Allan started this session with a pie chart that showed all the high level tasks a DBA is responsible for: Backups/Restores; Monitoring; Security and Auditing; Performance; Maintenance; Inventory and configuration. For each of these topics, they explained many things that are useful to watch out for and the tools to use to do so, also metrics/thresholds to use as guidelines. I took a LOT of notes in this session and was pleasantly surprised to learn specific things I can utilize right now.

At the end of the session, they made sure to mention that all the tools in the world are great, but dont forget about YOU. Keeping up with your training and learning is important as well. You can use classes, webcasts, local user groups, conferences, brown bag learning lunches, etc., to stay current. You never know when that knowledge will be handy!


Session 6: Troubleshooting 101

As with Tuesday, I was pulled away from this session by work (ohh gotta love it - huh?). I did get to listen to the first few minutes though. They started with some sage advice: Listen fully to the problem first, dont just start clicking half way through what the person talking is saying. Also, develop and use a formalized troubleshooting approach to increase effectiveness, this will help in times of emergency when someone is literally watching over your shoulder.

I will watch the recorded version of the session at a later time and finish this update.

Write comment (0 Comments)

SQL Foundations - Day 1 2013

The entire SQL Foundations course is geared towards people who are new to SQL Server or who needed a refresher.  The course consists of 6 sessions, each about an hour long, over a 2 day period.  Over the course of 2 blog posts, I will be giving a very brief summary of each session.  If they sound interesting to you, you can make sure to follow either of the authors, Allan Hirt (blog | twitter), or Ben Debow (blog | twitter) to learn when the next offering will be.  I have made a new post for Day 2, which can be found here.


Day 1: Tuesday, August 27th

Overall, I am glad I attended this first day's sessions.  I appreciate the fact that both presenters have a way of connecting with their audience and explained the concepts in an easy to understand format.


Session 1: Hardware, Infrastructure, and Operating System Concepts for the DBA

This was a great session - both Allan and Ben discussed multiple topics, such as, Hyper-threading, Memory, Networking, Supportability, Security in general, Standardizing deployments, and the importance of Testing.

Overall, this session seemed to explain the importance of how knowing the hardware and technology that SQL Server sits on, will help a DBA immensely.  Sometimes an issue can appear as though it is within SQL Server, when really it is due to underlying factors.

Allan finalized the session talking about the importance of testing.  A lot of mistakes can be avoided by testing before making a change in production.  Whether it is as big as an upgrade, or a normal patch, or a simple configuration change, testing the change allows you to see what issues may arise.


Session 2: Virtualization and the Cloud for DBAs

This session talked about the cloud and about virtualization technology in general.  Again, both Ben and Allan talked about the terminology that is used when thinking or working in the Cloud.  It was refreshing to not have to know what the acronyms meant - they were explained beforehand (halleluiah!).  The session talked about what success looks like for the private or public cloud.  It also gave key concepts to think about, such as possible limitations and it's overall level of maturity.

The summary included a note that when Microsoft has a change, it is pushed to the cloud first.  That reality may give a big clue about how permanent Microsoft views the cloud.  It seems to be that the cloud is something to learn about so that when you do encounter it - you are prepared!


Session 3: SQL Server High Availability

As it goes, I was taking the sessions in the middle of a work day.  I had an emergency that I had to look into, and didn't get to catch most of this session.  I know that Allan was the sole presenter, he started out mentioning that this was a topic "near to his heart."  By catching bits and pieces, I know he explained what the difference was between High Availability and Disaster Recovery.  He also talked about what it would take to deliver an aggressive level of uptime.  I know he performed demonstrations during the presentation, and I wished I had been able to participate.  I am hoping the recorded session will be made available at a later time.  If so, I will expand upon this brief summary.


Write comment (0 Comments)

TSQL Tuesdays # 45

It is time for another TSQL Tuesday - Mickey Stuewe (blog | twitter) is hosting this month and the topic is "Invitation–Follow the Yellow Brick Road". Well, here it goes..

I worked for a medical device company that needed to audit quite a bit of data. I was lucky enough to work with some wonderful and talented people. One of those persons was the DBA. I took what he did and said as database gospel. He was the type of person who would help you understand things if you asked - and if he didn't know he would tell you, but get back to you later with an answer. He probably got sick of seeing me as often as he did. :) When I started at that company, I knew that I had enough understanding to be dangerous, but I also knew full well and good that I had a LOT to learn. I took the opportunity to learn from him.

He set up a system of auditing using triggers on MANY tables in the transactional database. I started learning everything I could about using the different types of triggers (insert, update, delete) and the way to use the system tables to put the data here and there, and to record what happened and by whom. I ended up learning a lot of "magic behind the curtain" about SQL and triggers along the way.  When I was hired, the database used was SQL 2000 (we later went through a couple of upgrades - the validation was a lot of work). I never even thought about other options for auditing - this solution worked well, why wouldn't everyone use it. :)

When I started at my current job, I realized my passion for SQL. I knew I liked it before, but this job has really given me an opportunity to get my hands dirty much more. That is when I was introduced to the ETL and the data warehouse. I had been introduced to the concept of a warehouse before, so that wasn't new, but I hadn't been involved in the manipulations and loading of data.

In doing research about ETL in general, I learned about Change Data Capture (CDC) and Change Tracking. I have to admit, I was initially a little bummed. I had learned something that was working very well, but here was this new functionality that had the same end result, just worked differently. I eventually learned quite a bit more about it and was thrilled with the possibilities. I wanted to start using CDC with the ETL to help with incremental loading. I haven't gotten my chance and might not be able to since we are phasing out the only data source that we house internally - all other data comes from outside vendors by either files or database backups. Therefore, I put the thought of auditing on the back burner.

Recently, however, we had a need on the application development side, to audit who made certain changes. BINGO - perfect timing - I had already done the research previously, so I knew how to implement things to get what we needed up and running pretty quickly. Of course, as everything else, other priorities have slipped in, but I am almost done with the implementation. Setting up the CDC portion was a snap, getting the data where I want it so I can use it is what is taking more time. I am taking the audited records and putting the changes into an XML string then saving that in a central repository for audit logging and reporting (but yet keeping it dynamic so I can have 1 script no matter what the data structure of the audited table looks like).

In summary, I learned a lot from looking into what made the first auditing process I was exposed to "tick". Then, I learned more about changes in functionality between SQL 2000 and SQL 2008 R2.

I didn't know about the SQL community until a year ago (Thank you - SQL Saturday in Baton Rouge 2012). I have met some wonderful people so far and look forward to meeting even more! All in all, it is really awesome to say that while I don't have the "admin" type of historical knowledge a lot of people do, the auditing topic is something where I can say I understand the before and after.

Thank you Mickey for such a great topic - and talk to you soon!

Write comment (0 Comments)

SQL Saturday - Baton Rouge # 234 - 2013

I went to (and spoke at) SQL Saturday #234 in Baton Rouge this past weekend.  It was incredible - yet again!  I wanted to list the sessions I went to and a little blurb about each.

8:40 - 9:40 AM == I attended a class taught by Thomas LeBlanc - "Execution Plan Basics - Beginners".  When I learned about how active and friendly the SQL community was last year, I started a 5 year plan to get my name known and help others.  I finally broke down and joined Twitter.  Tom was the first person to follow me, which had me very excited to meet him.  I walked in with very little knowledge of the execution plans.  I knew they existed and I knew I could use them to find what the SQL Engine does to get and return the data.  His session taught me about several little tricks to see more performance tuning data, and to be able to read an execution plan.  He started by giving an overview of the history of execution plans, including showing us the text that used to be the only way of reading an execution plan - now there is an easier to understand GUI.  He showed many examples and demos and was very easy to understand.  I was really glad I attended.  During his presentation he also gave many pointers on where to look for more information.  Here is the link to the abstract and his slidedeck on the SQL Saturday website.

9:50 - 10:50 AM == The next session I attended was taught by Ryan Adams - "SQL 2012 AlwaysOn Quickstart".  I went into this class not expecting to learn all about it, but hoping to learn enough to be able to research on my own.  I had no doubts in Ryan's teaching abilities, but I have heard a lot of very brief things about AlwaysOn and knew it was a very big topic.  I also knew that it was something I didnt think I had enough overall SQL knowledge to understand quite yet.  I was pleasantly surprised when the session was over, because Ryan explained everything in a simple enough manner that I feel I have a very good understanding of the entire concept of AlwaysOn.  I know I have a lot to learn about the specifics, but overall I was thrilled to be able to have thought of 3 distinct ways it would help us out at my current job.  I saw a presentation of his at last year's SQL Saturday, also in Baton Rouge, and even though he had to play charades a bit due to the projector difficulties, I still walked away with a couple key points I had learned.  This year was even better.  Here is the link to the abstract and his slidedeck on the SQL Saturday website.

11 AM - 2:30 PM (except for the lunch break) == I am going to talk about the next 2 sessions I attended together.  They were taught by Sean McCown - "Beginning PowerShell for DBAs 1.0 and 1.5".  I have to put a disclaimer here that I was SUPER excited about meeting him and his wife this year.  I had first learned about the SQL Community in general when I learned about the SQL Saturday event in Baton Rouge last year.  This was right-up-my-alley and I had a great time.  I met Sean and Jean at sessions last year, but I was a no-body so they wouldnt have known about me.  I found out about their website and weekly webshows - so I started attending the shows regularly.  By the time I got to meet them this year, I knew they would at least recognize my name.  I like the way Sean teaches beginner sessions and since I have been chomping at the bit for learning PowerShell, it was perfect opportunity.  His sessions taught me a lot and now when I go onto the midnightdba.com site to learn more, I have a very good context to pull from.  He stepped through the very basics of PowerShell and emphasized the point that whether you are working with the OS, SQL, Exchange, etc., it is all the same - same cmdlets, same syntax, etc.  He showed the use of PowerShell and went into examples of applying those scripts to multiple servers or DB instances very easily.  All in all, I had high expectations for this class, and as usual, his knowledge and ease of teaching exceeded those expectations.  Here are the links to the abstracts on the SQL Saturday website: link for the 1.0 class; link for the 1.5 class.

2:40 - 3:40 PM == This is the session where I was the speaker (so I kind of HAD to attend).  I taught "The ABCs of SSIS".   Here is the link to the abstract and slidedeck on the SQL Saturday website, you can also access it on this website at this link.  I have given this presentation online before, but this was my first live technical presentation.  I was very impressed with the way everything was handled.  There was plenty of notification before the event via email and on the phone.  I knew that anything I needed would be answered promptly.  They gave me all the information I needed though, and since I had been an attendee before, I had a very good understanding of what to expect during the event.  What I didnt know though, was how much fun the people who run it are!  As an attendee you dont see all the camaraderie that I was privvy to.  It was great to see the hard work that was put into it, but also the way the event leaders, speakers, sponsors, and volunteers interacted.  I sincerely hope I get to speak at the next year's event.  What a wonderful first experience!  I will be constantly comparing other events to this one - it was just that good. :)


SQL Sat #234I cant imagine all the work that goes into the event each year, but this is yet another year where things look flawless from the end user perspective. :) The coordinators and volunteers didn't show the panic behind the scenes at all - in fact they looked like it was just a barrel of laughs. I look forward to going next year!

Write comment (0 Comments)


This presentation shows the basics of SSIS to help with automating database tasks, such as maintenance, importing and exporting data, or ETL transactions. The discussion will start with an understanding of when SSIS should be used vs. when a database object, such as a view or stored procedure, should be used. You will learn about the concepts of syncronous vs asyncronous and non-, semi- and full- blocking transformations.  You will also see how to create a basic package, and how to use the built-in logging and configurations. Lastly, we will talk about the importance of organizing the overall SSIS structure.  This presentation gives you a great start on how to use SSIS, gives you something to think about and provides resources for where to continue learning or researching.

You can get to the presentation slide-deck by clicking here.  There are helpful notes on most of the slides.

This session is also available via a recording, which can be found by clicking here.  It was presented to the Virtual DBA Fundamentals PASS chapter.  ** Note: Please right click and choose save as, then watch it from your local PC.

Presented At:

  • November 2, 2013 - SQL Saturday - Dallas, TX (Abstract)
  • August 3, 2013 - SQL Saturday - Baton Rouge, LA (Abstract)
  • December 4, 2012 - DBA Fundamentals PASS Virtual Chapter  ** recording available (Meeting Archive)
Write comment (0 Comments)

TSQL Tuesdays # 42

It is time for TSQL Tuesday - Wendy Pastrick (blog | twitter) is hosting this month and the topic is "The Long and Winding Road". Wow, this is a great topic - I cant wait to read many of the other posts over the course of this week! :) This is my first time writing for TSQL Tuesdays, and I cant think of a better way to start.

To start off with, computers have been a part of my life since I was "knee high to a grasshopper". I remember playing on the ATARI and the Commodore. That was back in the days when you put the cartridge for Frogger in, went to have dinner, and then by the time you finished, it was loaded and you could play. I also remember when the game BOMB SQUAD came out and it TALKED!! Holy smokes it was totally AWESOME at the time! :) (I don’t do a good impression of those words, although I try, but they are definitely etched into my brain for all time.) I remember thinking, at that young of an age, how smart and creative you would have to be to come up with stuff like this. I dabbled in basic for a bit, and I am proud to say I wrote my first code at a tender young age - I could print on the screen "Hello Dad, this is cool!" and make it repeat X number of times.

Skip ahead a few years (ok many… but who is counting?) - I was an administrative assistant at an HMO in Florida. I always had a knack for picking up on how software worked by playing around, and as an Admin Asst, it came in handy to be able to help others. I got in good with the IT guys, I mean after all, you should always make friends with IT. That is the first time I was introduced to databases. I worked in the Pharmacy department and we had a vendor that tracked the utilization of medications for our members that we needed to better analyze. So, I used the tools I knew, Microsoft Office and came up with a fabulous solution. I imported the csv file they sent monthly into an Access database (please don’t cringe, the story gets better - I promise :)). Then, scrubbed and manipulated the data, and finally spit it out to Excel. Excel took over and I ran a few macros, and voila - the end result was a formatted report that was used as is. Everything was automated and I LOVED it!

I should have taken the hint even back then, but it took several years and a few jobs in between for me to figure out my passion - data and database management. Right now, I work with SQL 2008 R2, but I have worked with a number of database platforms (MS Access, FoxPro, Visual FoxPro, mySQL, SQL Server 2000/2005/2008/2008 R2 and now delving into 2012). I have loved the intricacies of each, but like working with SQL Server most. I am in a 2.85 person BI team, and while I think what they do on the analysis side is cool, nothing compares to digging my hands into the database directly. I like giving accurate data into the hands of my colleagues and letting them do whatever they wish with it. I also love SSIS! I don’t know what I would do without it at the moment.

While SQL Server is what is taking me to new places right now, I have to be honest. I cherish the memory of when I made my first integrated BI app using an Access database and Excel macros. I don’t know where I would be without that experience. I truly hope to have that kind of moment in SQL Server. I am studying to get my certifications in SQL Server 2012. Hopefully along the way I will have a chance - with such interesting characters in this industry, it is probably almost guaranteed! :)

Write comment (0 Comments)


Powered by mod LCA