Friday, April 20, 2018

Data Warehouse Indexes

A datawarehouse environment is used differently than a transactional environment, so architecture and indexing strategies should also be different. In this presentation, we review what the ultimate goal of an index really is, what the point of a data warehouse is, and how they relate to each other. You will also learn common practices for indexing the different types of warehouse tables, specifically dimension tables, fact tables, and staging tables. We will review cases when it is useful to have summary level fact tables, in addition to detail level fact tables. Then, lastly, we will look at the benefits, and gotchas, of using columnstore indexes.

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

The script referenced in the slide deck can be downloaded by clicking here.

Also, there have been a number of requests to be able to get the cute frog pics, so here they are!

This session is also available via a recording, which can be found by clicking here.  It was presented to the Data Architecture Virtual PASS chapter (although it has been updated since then, the majority of the presentation is the same).

Presented at:

  • August 2, 2014 - SQL Saturday Baton Rouge (Abstract)
  • May 6, 2014 - Data Architecture Virtual Chapter ** recording available (YouTube Recording)
  • March 12, 2014 - Baton Rouge SQL Server User Group
Write comment (0 Comments)

SQL Saturday - Baton Rouge # 324 - 2014 (I'm Presenting)

I will be presenting on Datawarehouse Indexes on August 2, 2014, along with some other wonderful speakers!  Check out the schedule here: http://sqlsaturday.com/324/eventhome.aspx

Here is the session I am going to be presenting on: Data Warehouse Indexes

I have my schedule printed out and am really looking forward to meeting several people.  What sessions are you going to see?  See you there!

Write comment (0 Comments)

ETL - Index Maintenance - specific table

First off, this script is still a work in progress.  It is something that I created while working with data warehouse indexes, and added to it when I was getting ready for my presentation.  It helps me specifically in the ETL process - I do NOT use this for overall index maintenance.

What is nice about this script is that I am able to either disable or rebuild by using the same stored procedure.  That way, when I want to update the process, I only have 1 place to change it and all SSIS packages that use it will be affected.

The process I am using it in is as follows:

  1. Disable Clustered Index (this also disables all non clustered indexes and saves me from having to specifically do them manually)
  2. Rebuild the Clustered Index
  3. Data Manipulation (insert, edit, delete, etc)
  4. Rebuild all of the NonClustered Indexes

I am working on a full indexing strategy for the entire ETL process, but for now, I have specific needs and this takes care of all the problem children (tables) allowing the ETL process to continue largely unaffected, but these specific uses run much faster than without it.

By accessing or using this code, you agree to be bound by the following: license

Click here for the SCRIPT

If you have feedback, or are interested in contributing, please email: This email address is being protected from spambots. You need JavaScript enabled to view it.

 

Write comment (0 Comments)

Export all msdb SSIS packages to files

This is going to be a post with more pictures than text, but it is something that is useful.  :)  I did not set this package up, it was done before I started working here.  But, it has been asked about, so I am blogging about it.

When I release changes to the production, I always want a rollback plan (I would assume most people do).  In order to do that for SSIS, I use a package to export all of the SSIS packages to files. Then, save those in a zip folder.  That way, I can get to them quickly as needed.  Here is the package info for those who can also use it. I will say that this only looks at 1 of the folders within the msdb structure - the main BI ETL folder (which has sub-folders and the SSIS packages in those sub-folders). The way this package works, I would have to have it in each of the SSIS solutions I use. I want to re-do this to make it more generic and re-useable for multiple solutions.

Read more: Export all msdb SSIS packages to files

Write comment (0 Comments)

TSQL Tuesdays # 50

This month's topic is: Automating Tasks and is presented by SQLChow (blog | twitter).

Automation - its a blessing and a curse. The curse part is that you want to do it, but who has the time? Of course, the blessing is it will save you time in the long run, but you have to put in the time up-front in order to gain that extra time. :)

I use SSIS daily for my job. I work with the ETL, so automating the data warehouse steps comes naturally. I try to ask each time I do something for someone, whether this is a 1 time thing, or if it will be repeated. I would rather automate something if I can.

Although I am sure this T-SQL Tuesday topic is about administrative type of processes that are automated, I think the best thing we have automated at my job recently, are a set of "checks" at a certain time to make sure everything is as it should be. We are only checking some things, but it has led me to do more. Right now, the checks make sure there is data in certain tables where it should be and it checks certain data in the warehouse to make sure it is within a 5-10% range of yesterday's totals. This automated check has really saved our bacon a couple of times. Before those checks were automated we had to look at a report daily to make sure the data looked ok.

So, back at the main topic. I have recently been learning PowerShell. It is a great tool to automate multiple servers in a single script. My latest automation is to update the DEV database from a copy of the PROD backup file. I get the latest backup filename and location, move the file from the production server to the development server, then restore that with different final data file locations, and finally update users and permissions. It was my first PowerShell script and thank you to Sean @ MidnightDBA for the help!

I know that this year (2014), will be spent learning more about database administration, and more automation scripts will come to be. It is exciting to know that I will feel more in control of the overall SQL environment when I have automated more of it.

Write comment (0 Comments)

DBA and Project Management?

What do Database Management and Project Management have in common?  I am finishing up the piece of paper that some would call a Bachelor's degree (I only say it that way because I havent learned much during the several years it has taken me to pay for it on my own - mostly because I have learned everything myself).

One of the last classes I have to take is Project Management.  I am excited about this class, but at the same time a little non-chalant about it.  I hope to glean some wonderful information about the topic, but this is also something where I have learned quite a few things along the way.  I have had some great teachers in the form of co-workers, who have helped me get the knowledge I have today.  I have also gone through some trial and error situations to learn what works best for me. 

It took a long time to understand that I have to have something written down for me to remember it - and I had to change my processes to actually write everything down in the same place!  Now, though, I have a system that works well for me.  I am flexible with the software I use to keep my projects list, but it is the consistency that I cherish.  While this is more along the lines of Time Management, I am glad that I have these skills before this class.

I will be posting in the Project Management category while I am learning so that I can actually keep the knowledge in my brain, well hopefully!  I will also probably come up with a presentation on this exact topic - What a DBA needs to know about Project Management - or maybe How Project Management skills help a DBA. :)

Write comment (0 Comments)

RIP Cursor - took you down from 90 mins to 5 mins

I finished something recently, that I am very proud of - I literally had to hold on to my chair at my desk to not jump up and shout Wooo-Hooo! I tore into a cursor and obliterated it!! :)  I wanted to talk about the process of the transformation - just thought I would record my thoughts so that hopefully they will help someone.

I work in the health care industry, and the cursor is used to calculate the number of active patients for each day.The dataset has an admit date and a discharge date, along with some other things. Due to the way the transactional database is set up, there are important conditions to make sure you get the correct insurance, etc. for past dates and such. The total number of active patients can change due to late data entry and/or mistakes, therefore the entire summary table is re-built each night.

Read more: RIP Cursor - took you down from 90 mins to 5 mins

Write comment (0 Comments)

Archives

Powered by mod LCA