Data Warehousing is a large and often misunderstood concept. Marketers, Authors, and IT Departments all have differing spins and opinions on what is involved and just how to go about building one. In this session we will discuss some of the more common architectures and practices. We will cover various aspects of what goes in to Data Warehouse including the systems, modeling, data storage, data integration and data access.

Level:
200 (Intermediate)

Start Time:
Thursday, August 18, 2011 1:00 PM US Central Time (August 18, 2011 6:00 PM GMT)

End Time:
Thursday, August 18, 2011 2:00 PM US Central Time (August 18, 2011 7:00 PM GMT)

Live Meeting Link:

Thanks again to everyone that came out to my session at Desert Code Camp back in April. I am a huge fan of these types of community events. For anyone out there looking to tap in to the technology field you can’t beat the experience and networking opportunities.

For everyone that did attend, here is the link to the slides and code examples. If you didn’t attend or would like to see it again you can check it out now thanks to DevExpress.

If you happen to be in the Phoenix metro area this weekend make sure to come out to Desert Code Camp. I will be speaking on SQL Server Development focusing on Stored Procedures, Function and Common Language Runtime (CLR).

This will be the first time I have attended/presented at Desert Code Camp myself and I am extremely excited. After speaking at the first SQL Saturday #47 back in February I got the buzz supporting the local tech community and taking advantage of the opportunity to share and network with other professionals that work close to me. Twitter, Facebook and all of the other social options are great but nothing compares to the in person interaction. The best part of all is that these events are free to the public allowing anyone to come and learn no matter the level of experience.

Desert Code Camp is of particular interest as it offers a wide variety of sessions in all sorts of technologies giving others that would like to learn SQL a chance to branch out and also so that I can attend sessions and meet people in other areas of IT that I have not experienced.

I hope to see you there. If you do come you can hit me up on twitter (@speedracer) or just drop by my session at 11:30 in room IRN-124.

In the past I was never one to make resolutions. Sure I might have had the thought of being healthier, quit smoking or working harder to manage my money. The reality though is that it always seemed self defeating. No one ever keeps a New Year’s resolution past about the second of January, right?

Even without any true resolutions I found other forms of motivation to get me where I am and that made it all seem good enough. Over the last decade I have to say I was pretty successful. I completed a bachelors and masters degree. I moved from working as a telephone representative into the role of a database professional. I married my awesome wife, had a beautiful little girl and am expecting a son in the coming months. I even quit smoking almost four years ago and will be running in my first half marathon this weekend. All based on the things I dreamt about that drove me to get where I am today and without ever making a resolution in the conventional sense.

So in that same manner, why start making resolutions now? In the past I had a vision and took many steps. Setbacks came up but I had a great vision and for the most part I made that vision a reality. But once I got to that point it was like climbing a mountain and coming to a shear drop off. No place to go, no plan and no new motivation to push me further.

Back to my question of why? Well, I decided to take a baby step (yes, pun intended) and try out setting a New Year’s resolution. It is to kick off the next journey in my life right by broadening my vision and continually setting goals for myself. Not just for 2011 but on for the rest of the time that I have.

To start down this path I am following the sage advice of so many in the SQL community. I purchased the book “Getting Things Done” by David Allen. So far this book has been nothing but inspiring. The idea of setting a 50,000 foot view that is the rest of my life is scary and overwhelming to say the least but I need (for lack of a better term)…a code to live by. The idea of top down goals always made sense in the business setting and thinking about them in a more personal setting seems to make sense as well.

This vision is still evolving and one that I am not yet fully ready to share. But as a challenge to myself I will be posting it out here in the very near future for all to see. Call it a challenge to make a vision and a life that is realistic, attainable and just might give a little inspiration to others to do the same.

I once was told by my first development lead that his goal was to work himself out of a job. Being a bit younger and quite a bit less experienced, I thought it was an interesting perspective and a way to say there would always be something to do. But perspectives have a way of changing over the years. Now, with a family, broader exposure and a more experience, I have a different thought about his statement.

This brings me to this month's T-SQL Tuesday, presented by Jason Brimhall (twitter|blog). The focus of this 9^th installment is on vacation and what it means to be a database professional looking to get some much needed rest and relaxation. I think the real meaning behind working one's self out of my job means less about all of the work out there and more about making sure the work you have doesn't require you to keep it running. Sure the idea that we are needed and are the best is great. But whether this is true or not, this hero mentality is the bane of the IT professional's existence.

The reality is that organizations don’t need heroes. They need people that can build solutions and that these can be done in such a way that they are easy to maintain and require little to no maintenance. Sure things break and sometimes changes need to be made. This should be the exception to the rule and not the norm. It is my belief that the best in the business will try to build solutions to be only as complex as necessary and in reality should be designed in such a way that anyone with a reasonable amount of skill can figure out later on.

This leads me to my resounding belief that if systems you design or administer require you in the picture to survive, you are doing it wrong. There are far too many tools and techniques in the world to make your life easier. To avoid using these tools and techniques might just indicate that enjoying a vacation or progressing in your career are not high enough in your list of priorities.

As a DBA, Developer or any other IT Professional, focus on designing systems and processes that are as easy as possible to administer. Sure it is not always possible but the key is to keep it simple. Companies will see this as an asset and for you it means you can take on new projects, find time to do your own development, maybe get promoted and ultimately take a real vacation.

Whether you are new to SQL Server or are a well-seasoned DBA/Developer, the one thing that has made SQL Server a knockout lately is the huge community support. SQL has a great following of users, authors, bloggers, and presenters. What you may not know is also SQL is huge on Twitter. Want proof? Take your next troubling question (after you have done a bit of research on your own, of course) and post a tweet with the hash tag #sqlhelp. Yes this means you have to set an account up if you don't have one but it is well worth it. If you have no idea how to use twitter start with this manual from Brent Ozar (blog|twitter):

The Simple Twitter Book

Now assuming you have a twitter account, some base knowledge of tweeting and have posted your question on twitter with the #sqlhelp hash tag, watch your stream on twitter. Now I cannot guarantee you will actually get a response but chances are pretty good. And the people responding are some of the best. So be patient and if you don't get a response within a day feel free to post again. There is a large base of people that monitor the #sqlhelp tweets and try to respond but if someone doesn't know they won't respond (typically). Remember this is all voluntary, though some people are getting jobs in the social media field most of these people are just doing just to help others.

All that being said here are a few points of my own on Twitter and getting help through #sqlhelp

• Try it! - Even if you are a bit skeptical, it's totally free and zero commitment.
• No Pressure – If you are not a big fan of twitter or social networking don't worry. It's there if you want to use it.
• Give back- You may have experience others don't. If you know the answer please share. I have found it a great learning and growth opportunity. No pressure though.
• Use Twitter Tools – There are many desktop and mobile apps that are free to use. Personal favorites are Seesmic and TweetDeck.
• No Marketing – Don't use it to market. Ever. No one is a fan of this and you will be called out.
• Follow Me – Sure this is a shameless plug but if you are new or well versed in twitter feel free to follow me and send me a message @speedracer.

It is time again for T-SQL Tuesday, or how it's referred to in the twitterverse #tsql2sday. This month's topic, hosted by Robert Davis (Blog|Twitter), is all about school. When I think back to school two references always come to musical references come to mind and not neither of them are Lady Gaga. No, two “old school” favorites are Alice Cooper and Pink Floyd. Their songs, “Schools Out” and “Another Brick in the Wall (Part 2)”, though having very different meanings both received their fair share of time in my car stereo back in high school. Back when I listened to these songs I saw school and education as something I just needed to do until I got a bachelors in college then I would be done.

Now with as I look back with Bachelors in hand and also my MBA I find myself making up for lost time. In high school I wanted nothing more than to be done so that I could get out into the world and make a little money. I worked all through school mostly full time and spend every free moment with friends and doing whatever I could to enjoy my time. It was only half way through college that I actually started to figure out what this education thing was all about. In fact I realized just how interested I was in education. I found myself reading books of all forms fiction and non-fiction, looking for ways to improve my skills both technically and personally.

Now as I slowly become a bit of a more seasoned professional, in a new job and spending any free time I can find reading and tinkering I find my outlook on education in a complete 180. I see that I did have a lot of fortune to be able to finish what I started and use my drive and determination to get me where I am. Now as I look back I find that my next evolution in education is to strive to be an educator. Not so much a professional teacher (yet?) but as I dig further into the land of databases and technology in general it is a lot like my math classes back in the day. There are great teachers and poor teachers but little in the middle. My hope is that I could be the former and help people learn and grow themselves.

The best thing about where I am in this journey it that I find myself here right in the middle of what I see as one of the most interesting online communities to form. The SQL Server community including the various user groups, PASS and others all function because it is full of individuals that all share, teach and help grow one and other. I look forward to sharing the knowledge I have in future blog posts, user group meetings, SQL Saturdays and hopefully in national conferences like SQLPASS in the future.

Sitting down to do a write up for twitter contest, I spent quite a bit of time coming up with what I see as some of the best features of with Reporting Services 2008 R2. I realized I could easily write the next Reporting Services book just on the new features alone. But then this post is part of T-SQL Tuesday (#tsql2sday) on and time just wouldn't permit. Instead I thought I could start by focusing on some of the best Business Intelligence features of R2. Still a long list, I mean come on isn't Business Intelligence all about reporting in one respect or another.

To start let’s have a quick discussion on what is not in Reporting Services 2008 R2. SQL Server 2008 R2 and the Office 2010 Suite introduce a host of new BI features not built as a part of Reporting Services but they do enhance or consume from it these include the SQL Azure, Parallel Data Warehouse and, though not a SQL Server tool but worth the mention Office2010, SharePoint 2010 and PowerPivot. All of these tools as part of the Microsoft BI Suite and offer compelling ways to store data and to access and render in ways that would have required countless man hours in the past. With that out of the way we can start to focus on what is included in R2.

Data Sources

Reporting Services has done well being open with its data source availability. From the start Reporting Services has been able to connect to most OLE DB and ODBC data sources. Now in R2 the addition of Parallel Data Warehouse and PowerPivot for SharePoint has been added. Along with that, much needed support for accessing SharePoint 2007and 2010 Lists is now possible.

Shared Data Sets

One area reporting often struggles is with consistent data across reports. With the introduction of Shared Datasets it is now possible for many reports to refer to the same Shared Dataset in much the same way that Reporting Services has always allowed with the Shared Data Source. Shared Datasets can be used throughout a report project and are accessible to anyone with security to the Shared Dataset on the Report Server.

Multiple Dataset access in a Data Region

In the past, Reporting Service limited access to one dataset per data region (table, tablix, chart, etc…) and in most cases this was acceptable. However, with the number of data sources supported and the need for quick turnaround, limited space and limited resources the need to combine datasets was a real hindrance. In R2 this has all changed and it is now possible to perform “Lookups” on other datasets. Report Developers can now access data from a SQL Server Query and reference a SharePoint List where reference data or contact information might be contained. Three new functions make this possible to lookup one from a dataset (Lookup), return and array of matching values from dataset (MultiLookup ), return and array of matching values from an expression (LookupSet).

Nested Aggregates

Anyone who has spent anytime reporting can understand why this feature may have been left out. The idea of averaging an average is a classic example of data gone wild. But this function does have its place. Nested Aggregates allow scenarios like the Average Monthly Sales to be calculated over individual days of data back the past three months. In the past this same report would have required a separate query for the detail day’s data and the summary level average by month.

Data Level Visualization

Scorecards and Dashboards are often about the flash. No I am not talking about the kind that a certain fruit slinger despises. I am talking about the bars, lines and stop signs that many executives like to stare at with the morning cup of coffee or during a board meeting. R2 makes it light-years simpler to add three new visualization components. The Sparkline, an in row graph that shows a simple high-level trend line and looks a bit like a fuse. The Data Bar, a single in cell bar graph comparable to all other rows of data creating a horizontal bar graph of sorts in the Tablix region. Finally, the Indicator, made up of various sets of stoplights, stars, colored arrows and other visualizations that are then set based on ranges in the row of data.

Maps

This is by far one of my favorite features in Reporting Services 2008 R2. The Map report object is a multi-layered report control that allows for multiple datasets to be place atop a map. When first designing the map a wizard prompts to design the first layer. Here you can select from built in maps of the USA and the 50 States, you can import you own ESRI compliant Shapefiles (*.shp), or you can go right to the database to query your own spatial data from SQL Server. Once you have finished you selections in the wizard you can begin to add additional layers through the Map’s Map Layer Dialog Box and add other Map Gallery Items, Shapefiles, or spatial queries. You can create thematic maps to show colors based on polygons like states of counties. You can use spatial points to create bubble maps and various other map visualizations. Want an even more rich experience? You can even add a Tile Layer so that you can overlay data on a Bing Map. SQL Server includes access to Bing Maps for no additional cost in R2 making for a very nice addition.

Report Parts

Say you built a sweet performance dashboard and now you have other report writers asking for a copy of the RDL. Now you can break your report down to the next level above Shared Datasets, Report Parts. Report Parts can be deployed from Report Designer/Report Builder 3.0 to the Reporting Services Server and can then be consumed by anyone using Report Builder 3.0 in their own report. There is even a bit of versioning that can be done so that you don’t necessarily overwrite but instead allow others to choose when they want to use the latest version.

Report Builder 3.0

To truly empower the business with Business Intelligence much more than a bunch of charts and maps are needed. This is where Report Builder 3.0 comes in. As the name says this is the third release of the Report Builder application, an end user report design tool. Report Builder 1.0 was introduced as a Click-Once tool that allowed for some basic report authoring using a Report Model data source. Then in 2008 we saw a beefed up much more mature Report Builder 2.0. This tool allowed for almost all of the development features available in designer in a slimmed down Click-Once or Standalone version. R2 introduces the next iteration and when run from the Report Server actually has a few features that even make it more desirable to work with than Report Designer (BIDS). First is the ability to consume report parts. You can search for these through a number of methods including name and creator. By far the nicest separation from Report Designer is the Edit Session feature. This feature caches the report data on the server allowing changes to the design of the report not to require re-querying of the data (limited to 7200 seconds and 5 sessions).

All together all of these features bring Reporting Services to the next level of Business Intelligence. Of all of the features the biggest take away is that as much effort that is being put in to continue to improve and enhance even more is being put in to help the Self-Service Business Intelligence quest. Developers, DBA’s and Analysts the like can all benefit from the features and enhancements.

What's better than storing 100,000 rows of data in Excel? How about a million? 10 million? Heck 100 million? (Insert Dr. Evil reference here). With the introduction of PowerPivot, anyone who has Excel 2010 can now take advantage of some very nice rapid BI development. PowerPivot is an Excel Add In that is available for download from http://www.powerpivot.com/download.aspx.

However, PowerPivot is more than just an Add In, it truly a self-service Business Intelligence tool that is enhanced by SharePoint 2010. Between Excel and SharePoint end users now have access to perform their own rapid development of BI solutions without the learning curve of Analysis Services/MDX using and environment they are already comfortable with.

Data sources of all shapes and sizes can be consumed and users are not limited to a particular source. Users can consume data Fact and Dimension data from one or more Analysis Services Cubes and in the same solution connect to a SSRS2008R2 Report and even a separate MySQL or Oracle Database. Connectivity is only virtually unlimited with access to the Microsoft suite and any other OLE DB/ODBC accessible sources.

Demo Time

For today we will keep things limited to the Excel side which is anyone with Excel 2010 can start to play with. Once PowerPivot it installed click on the PowerPivot tab and then click on the PowerPivot Window.

Once Power Pivot loads, start by selecting a source. In this example we will use the native “From Database” and choose “From SQL Server”. Then set the connection information as shown in the example.

The next step is to choose whether you will pull directly from tables or to write your own query. If you forgo selecting from tables you will have to manually set relationships (or let PowerPivot take its best guess) later on. My choice is to let the database set the design by default to make things consistent and follow the performance design of the underlying database.

From this point we can now start selecting the tables / views that we want to include. One of the nice things about this is that if you have a well-designed structure that uses foreign key relationship constraints you can select just a few core tables. Then by clicking on the “Select Related Tables” the PowerPivot wizard will find any that are related by the database constraints.

By clicking on “Finish” PowerPivot will begin the process of importing data. Depending on row volume this could take a few seconds on up to several minutes depending upon size. For very large, multi-million row tables it could take a bit of time. In any case you will see a nice progress meter for each table.

Once the data is imported you can start to browse the newly populated PowerPivot workbook. This workbook looks very similar to the standard Excel workbook, however, it is really a read-only display of the PowerPivot data that is used for some quick visual data profiling.

Through this view you can really begin to see how PowerPivot shines. If you loaded even multi-million row tables you can quickly see how fast and powerful it can be just by some quick sorts and filters in the interface.

From here the real fun begins. By clicking on the “PivotTable” we can start the process of consuming this data into an interactive Excel report. Select from the options listed.

This will take you to the blank PivotTable within the Excel worksheet with the PowerPivot Field List on the right.

From this point you can begin dragging and dropping fields into the PivotTable and additionally add in Filters that will appear above or beside the PivotTable.

From this point you can create almost limitless forms of analysis. You can include additional charts and other diagrams as well.

Taking things a step further this same technology can be incorporated into SharePoint 2010 to allow for users to Share and collaborate on simple to moderately complex Dashboards that all users can access.

Ground Rules

This all sounds great. So what are the caveats?

Run on 64bit machine. Microsoft recommends is as do I. 32 bit will work but your time and sanity will suffer.

The more memory the better. Sure the data is compressed but with millions of rows of potential data you need to think like a server.

This is not a replacement for Analysis Services/Reporting Services. Though there is considerable overlap, PowerPivot is designed to enhance the functions already performed in Excel. More complex analysis, scalability and security are not possible with PowerPivot.

Data is Disconnected. As data in the source changes this is not readily reflected. In Excel data must be refreshed manually. SharePoint allows this to be scheduled. In both cases this is a complete reload of the underlying PowerPivot Data.

Office 2010. Need I say more? Well if I do then just remember, PowerPivot is only available with Excel and SharePoint 2010.

Last Words

The PowerPivot Add In for Excel 2010 offers the end user a considerable amount of power on a desktop machine to do some fairly detailed Business Intelligence without the need for a Server running Analysis Services, Reporting Services or even a box sitting under someone’s desk running access Access. PowerPivot however lack much of the scale, power and security of these other tools. If you are looking for a quick, cheap and not-so-dirty way to work with data PowerPivot is the way to go.

The idea of something being new and improved always humors me. It’s a fun little marketing tool that is all too commonly slapped on the side of a TV dinner to indicate it will better than the last time we mistake of buying and consuming it.

So what does this have to do with me, this web site or databases for that matter? My original attempt I now chock up to an experiment now almost three years ago at WordPress. When I began my original quest it was simply to share information I had trouble finding elsewhere. Still a great idea and why I am trying again but, needless to say, after a few short posts I found time and motivation to be fleeting.

So here I am again, this time trying a different approach and with new motivation. I have officially been in the database profession for five years now and with over 10 years of IT and database experience. I have had the opportunity to mentor teammates and others outside of my organization. What I have found is that as quickly as I pick up new bits of knowledge I also like to share it with others just as quickly.

Beyond that, new social tools like Twitter have given additional me outlets to help share and openly communicate. On Twitter (@speedracer) I have found many active SQL Server experts that openly and freely share their ideas, perspectives and humor (@andyleonard, @brento, @brianknight, @buckwoody, @paulrandal and @midnightdba to name a few). A growing community that is always willing to help and grow others to do the same. On top of that the community at PASS continues to grow and encourages more and more participation and even free local event like SQL Saturday. Now as I look forward at my own professional development, I realize that as much as I like technology I also would like to share more myself and give back to the SQL Server and IT ecosystems that have made me the professional I am today.

With that I would like to say welcome to the New and Improved SpeedDBA.com. I hope you enjoy it, maybe even learn something and please feel free to leave comments or questions to anything!