Governance For All

I was thinking about SharePoint governance today and its effect on the success or failure of SharePoint. Fundamentally, SharePoint governance is a document that forms an agreement between the people who support SharePoint and rest of the organization. It then dawned on me that I could substitute SharePoint for any other system. However governance is an umbrella covering many things such as policies, rules, roles, and responsibilities. Within that umbrella of governance, I would like to think of these four areas as represented by the four legs of a chair, the chair being the system. Failure of any one leg may not make the chair crash, but it will make it less stable. Failure of more than one leg can cause the chair to collapse.

The same is true of SharePoint governance. Without adequate policies, rules, roles, and responsibilities, your SharePoint implementation may be on the verge of collapse. The hidden problem at many organizations is that even if you initially create a sound governance policy, if it is not followed and enforced, it may be as bad as not having a governance policy at all. What may begin innocently enough as a minor exception to the rules or policies here and there, can quickly slide down the slippery slope to total chaos. Stay with me a minute.

While governance may seem to be filled with a lot of rigid policies and rules, governance, whether for SharePoint or for any other major system in your organization, must be flexible. It must be almost an organic thing. By that I mean that it must be adaptable to the changing needs of the organization. A change in needs might be something major such as the addition of outward facing web sites to your existing farm of collaboration sites, or it may be something as simple as a font change used for the body of content. The thing to remember is that all change sets a precedence. The question you should ask is whether that is a good precedence that you want to set or a bad one?

Once policies and rules are established and made part of the governance, then changes or exceptions to these policies need to be very carefully vetted and then added to the governance so it does not open the door to other changes that may be less desirable. For example, if the initial governance called for Arial fonts, exceptions might be made for other similar fonts such as Calibri. However, as soon as you begin allowing Times Roman or Impact, you are on the path to allowing Broadway, Playbill, and even Comic Sans Serif. But governance can stop that that by specifying that any font from a limited list of fonts can be used, but only one font can be used per page or per subsite. The same can be said for colors, page layouts (themes), and other visual effects. In addition, the governance should define a method to request changes to policies and rules. Perhaps those requests get funneled through a single person, but more likely they should go to a committee that decides on any governance changes.

However, even if you have all the policies, rules, roles and responsibilities well defined and you have a committee to review and approve or reject any changes to the governance, if you do not have upper management’s full support of the governance, your efforts are in vain. Like a dragon without fiery breath or at least large sharp teeth, users will soon realize that they can get away with anything with no more consequences than your pitiful cry to never do that again. You must have management’s support to enforce the governance.

The problem is that maybe management just came back from a conference, or perhaps it was from a free seminar provided by an alternative vendor who provided a lunch ‘n learn at the local steak restaurant. While the product demoed does basically the same thing as your current product, marketing demos are very good (or they should be) at highlighting the best features of the product making it look like the new and most desirable product on the market. Rarely does the demo go into how much time or skill was needed to set up the demo. Of course demos are meant to look flawless and so simply that your dog could do it with one paw tied behind its back. It is your job to be able to tell the difference between a well-crafted demo and a system that is truly easy to use before your organization starts spending money on something it really does not need.

But here is another big ugly secret. No system can be successful without governance and top management support. Individuals resist change. They will refuse to learn how to use a system. They will claim a system is too hard to learn or to use. They will blame their own mistakes on the system or on the restrictive governance policies or the lack of training that fits their exact circumstance. Management must lead from the top down with strong reasons why the policies and rules will serve the organization as well as the users better. Governance gives them a way to make that happen by formalizing the policies, rules, roles, and responsibilities of any major undertaking. Governance is leadership by getting out in front of everyone and saying, ‘Follow me and be successful.’

Well I’m off to another SQL Saturday this weekend again, this time in South Florida. How does this fit into today’s mini topic? At SQL Saturday there will be hundreds of individuals who as DBAs or developers use SQL Server and have policies and rules they follow when using SQL Server. They have specific roles defining what they can and what they cannot do. They have certain responsibilities to perform specific tasks using SQL Server. What makes SQL Server work or not work within their organizations is largely dependent on how well they embrace the governance around creating, using and maintaining databases and the processes that access those databases within their organizations.

You see it is not just about SharePoint or SQL Server or any other specific product. The need for governance applies across all products and activities in the organization as does the need for management support of that activity. No organization would arbitrarily switch from using SQL Server to Hadoop to Informix to ingres to Oracle to Postgres to Access or any other database system every couple of years. Rather, they would choose a product that best fits their needs and wring out all the value they can from it by insuring that their staff is trained and supported in its use. Don’t expect a new product to somehow produce magical results. If anyone is responsible for magically results, it is an organization’s people. What does your governance say about how you use technology and your staff?

C’ya next time.

Some Additional Word Comments

Last week I showed you how to use a Word template as a form in a SharePoint library. I kept the form fairly basic, and did not have the time to cover some additional considerations when choosing to use Word to create your forms. This week I will cover a few of those considerations.

The first consideration is that since form text is stored in SharePoint lists, SharePoint 2013 does not support standard text columns of more than 255 characters by default. Sure you can use a single line of text data type or a multiple line of text, both of which default to 255 characters. At first I was surprised by this since I expected the multiple lines of text to automatically support substantially more text than 255 characters because by default, SharePoint 2010 does.

Therefore, when I created the SharePoint metadata in the library where the form would reside, I created the Lesson Learned Description as a multi-line text data type. Granted, I did not read the 2013 properties carefully because I fully expected that I would be able to store long detailed descriptions in that column. After I added the column to the Word form and saved the Word template back to the library, I created my first form record as shown in the following figure.

After entering the data I wanted into the form, I tried to save the data back to the data store, which in this case consists of the metadata columns in the SharePoint document library where I modified the form template. Notice that the value for the Lesson Learned Description column has a dotted red line around it. This means that there is an error with the value. There is nothing to indicate what the error is. However, since the column is a text field, there are very few things that can cause an error. The most likely cause for the error, and in this case the correct cause, is that the value supplied is larger (in total characters) than the program can save back to the data store.

To test this theory, I deleted a few characters from the description and tried to resave the form data. This time, as you can see in the following image, the save succeeded.

But wait just a second, this problem is even easier to solve. Just go back to the library settings and open the column settings for the multi-line column. By default this data type limits values to 255 characters or less. (No it really does not say that.) However, look for the Allow unlimited length in document libraries setting and change the selection to Yes. While you cannot specify an absolute column length in characters, you do have the ability to allow the column to accept as much data as you want to throw at it.  (Note, this was done in SharePoint 2013.  SharePoint 2010 by default allows multi-line text to be larger than 255 characters.)

However, some of the other column types present more of a problem. For example, it is not possible to surface a hyperlink field from SharePoint in a Word form. Perhaps this is because a hyperlink field actually consists of two values, the hyperlink itself and a description. You also cannot display in the form a multi-selection choice or lookup column. While these fields may appear in the Document Properties panel as shown in the following figure, I have had problems saving the result back to the SharePoint library even if the column in the library is defined as a multi-value choice field (or lookup field).

When attempting to define a multiple value choice data type, I do get the following message when creating the column.

Returning to the question of saving the data, let me show you what happens when you save the form. Unlike InfoPath which just saves an XML file containing the data. The XML definition of the form is saved separately and only once. While this greatly reduces the number of bytes saved with each instance of the form added to the library, it does limit your ability to make changes to the form fields without creating a new content type each time. On the other hand, since the entire Word form including the values are saved with each instance of the form in the library just like any other Word document, it is a simple matter to change the template to add, delete, or change any of the columns on the form. All new forms will use the new template when you create new documents. Furthermore, you can still open the old form instances since they are nothing more than regular .DOCX or .DOC files depending on the form template from which you started.

The following figure shows the first part of the Save As dialog in which I can select where I want to save the completed form. By default, the Current Folder points to the SharePoint library where the Word template has been saved. However, there is nothing to stop me from saving the document in a local drive or network drive. (I can also click on SharePoint to the left of this figure which should also show the current folder at the top of the list on the right.)

If I select the Current Folder, Word opens the Save As dialog as it usually does, but notice in the right panel that the default location points to the SharePoint library. Also note that the form is saved as a regular .docx file. The File Name defaults to XXX.docx where XXX is the first line of text in the template. You will probably want to change this as I have in the following figure. Note however that even here I can select any other location where I have permissions to save files including SkyDrive, Google Drive and other locations.

The last thing I want to point out before ending this week is that when you display the contents of the library, text strings, no matter how long their values may be, do not get any preference during display over columns that have smaller values (in terms of characters). In fact, as the number of columns that SharePoint attempts to display increases, the widths of the columns appear to be controlled more by the name of the column than the data included within the column as shown in the following figure.

If this is a problem because you need to display the data in a report, the easiest solution is to export the data to Excel. Just click on the Export to Excel button in the Connect & Export group of the Library ribbon of the library.

A dialog appears that prompts you whether you want to open the .iqy file or save it. Use the Save option only if you want to copy the file to open the Excel spreadsheet to a different computer.

Opening this file first opens Excel and then loads the data from the current library as shown in the following figure.

At this point, a few simple formatting adjustments to the columns in Excel including possibly the turning off of the grid lines, adding a style, removing some unnecessary columns and you have a presentable report.

Wait a second, what happened to the Lesson Learned Title? Remember that when you export data from a list or library to SharePoint, the export only includes the columns and rows you specify. In this case, the default view displayed the FileName so users could click on it to view the data in the form. However, you probably want to hide this field and display the Lesson Learned Title field. Note also that any filtering on rows carries through to the export. Even if you have your SharePoint view set up to only display pages of 30 rows at a time, export ignores this functionality and exports all rows that match the filter criteria, not just the first 30.

To take this example one step further, you can copy and paste the final Excel spreadsheet into a Word document if it is relatively small. Of course, you could also use Reporting Services to generate a report from the SharePoint library or even use Report Builder with the Excel spreadsheet to create multi-page reports.

Well, that’s it for this time. C’ya next week maybe. I’ll be at the SQL Saturday in Tampa, FL (http://www.sqlsaturday.com/273/eventhome.aspx ). If you are in the neighborhood, stop by to say, ‘Hi!’ I’ll be presenting in the afternoon on building cubes with either PowerPivot or Analysis Services Tabular model (I haven’t decided which way I’ll go yet.)

MDS: The New DQS? Not Really!

First, you need to know that the MDS I’m talking about here is Master Data Services introduced in SQL Server 2008 R2. I’ve already talked about Data Quality Services (DQS) which I believe was only introduced in SQL Server 2012.  So the natural question is what is MDS and why do I need it together with the newer DQS or is DQS just a replacement for MDS and can we forget about learning MDS now that we have DQS?

 

Previously I talked about DQS and showed how you can use it to create individual knowledgebase domains that can then be used to clean data.  The application of DQS on a regular based to clean data going into a production database should improve your overall efficiency compared to manually cleaning the same data.  This is because DQS remember the correction you made previously.  You can also define rules for ‘fixing’ individual domains.  Efficiency improves over time because the knowledge base understands how to handle an increasing number of problems without requiring manual intervention.  However, at the same time, users have to be realistic.  There will probably never come a time when absolute all the possible data issues have been addressed allowing for fully automatic data cleansing.

 

You could think of DQS as the detergent that helps you clean bad data from a data source getting rid of invalid and misspelled data.  It can also help you reduce duplicate records within that individual data source.  It can even output a data file of cleansed information with duplicate records removed or at least marked. However DQS provides no assistance in merging the clean output data file with another data source.  The ability to combine a freshly cleaned data source with a master data file is what Master Data Services can provide.  To some extent, both products have the ability to cleanse and match data from an input data source.  However, the rules in DQS are domain (column) centric while the rules in MDS are typically more record centric.   Also MDS as the name implies helps you import data from new sources into a common master table.  Another way of saying this is that MDS might be the gatekeeping of your enterprise data system that controls what data can or cannot be added to your data warehouse. 

 

Because both DQS and MDS provide the ability to define cleansing rules, users may jump to the conclusion that these two products are mutually exclusive.  However, the purpose of the cleansing rules between these two products needs to be clarified.  The purpose of the cleansing rules in DQS is primarily to correct and validate values added to each domain.  DQS rules can determine which values are allowed or not allo0wed in the domain.  These rules are not record based, but column based.

 

On the other hand, the business rules created in MDS primarily exist to prevent invalid records from being added to a master data table.   In other words, it keeps new records from entering or updating the master set of records which may sit in your enterprise data warehouse. 

 

Based on this description, you should see that DQS and MDS are clearly different products.  Furthermore, you may not always need to use both of them.  If your purpose is merely to clean bad data from a data entry system and possibly remove duplicate records, you may be perfectly satisfied with using only DQS.  Similarly, if your task is to merge clean data from multiple data sources such as sales data from different countries, you may be more interested in the abilities of MDS to combine records based on business rules that prevent record duplication while allowing data to be merged.  Of course if the data sources that you want to merge have not first been cleaned, you may need to first run them through a DQS project with a corresponding knowledgebase to ‘fix’ any domain issues such as different product codes for the same product in different countries or perhaps even differences in the way customer address information is recorded.

 

In future weeks, I will take you an exploration of MDS in between some other technical topics that I want to cover.  Before I end today, I want to remind all my readers of two events coming up very soon where I can meet you.

The first is the SQL Saturday Orlando event coming up on September 14^th.  SQL Saturday events started right here in Orlando back in November 2007 by Andy Warren.  I’ve been fortunate to have been a speaker at every Orlando event.  This year I will be presenting an introduction to DQS that I call, ‘Putting Quality Back Into Your Data’. 

 

The second big event coming up is the 2013 PASS Summit that this year is being held in Charlotte, North Carolina during the week of October 15-18.  This will be my second year speaking at the PASS Summit.  If you have never been to a PASS Summit, you are missing the opportunity to meet, talk with and listen to presentations from SQL professionals from around the world.  I feel very honored to be able to give two presentations this year, one on DQS and the other on using SSAS’s Tabular Model to build Cubes that you can manipulate as Excel PowerPivot models.

If you have the opportunity to get to either of these events, please stop by to say Hello.  I would love to meet you all.

 

C’ya next time.

 

The 2012 PASS Summit, Flying, and More

Last week I was at the 2012 PASS Summit in Seattle WA.  It was everything that others told me it would be.  I enjoyed every minute of it.  Of course, the best part for me was the opportunity to give one of the sessions.  Even though it was the last session on the last day of the conference, it was still very well attended, and I was relieved that at least some people stayed around until the end to hear my talk.  I met lots of interesting people from around the world and hopefully, some of them will be joining me in my blogs or on LinkedIn (http://www.linkedin.com/in/michaelpantonovich) over the next several weeks to see what else I have to say about DAX, PowerPivot, and the Tabular Model.  The bottom line is that I recommend any serious professional try to attend a major conference in his or her field of work at least once a year.  The networking and learning opportunities are well worth it.

Let’s talk about the flight now.  I’m not a fan of sitting in a long metal cylinder (soon to become carbon composites apparently according to the one ad on the flight), crushed between strangers sitting in a seat designed for 8-year olds.  I mean really, did they ever test these seats with people taller than 5’6”?  At 6’3” my knees are wedged into the back of the seat in front of me. Then the lady (and I use that term loosely) in front of me kept trying to push her seat back against my knees attempting to push my femur right through my pelvis and into the back of my seat, it was a painful five hours getting from Chicago to Seattle.  After getting there I almost could not walk and my legs nearly collapsed under me twice just trying to get out of the plane.

And whatever happened to food on cross-country flights?  I probably will date myself, but I remember the days when you got a hot meal with real metal utensils.  Now they cannot even afford to give you a bag of pretzels.  Well actually, Jet Blue still does.  By the way, to the person eating burritos and beans somewhere around me, take note for the next time that menu item is not a good choice for a sealed in environment.

My flight did not even have ‘free’ TV.  Not even network channels.  On the flights west, they had little 5 in screens (or at least that is the size they seemed to be) that folded down from the luggage rack that displayed DirectTV in color.  By color, I mean that each screen going up through the plane was a different hue.  And they were not even in front of each row of seats.  The one in front of me was about 4 rows up.  I would have needed a telescope to watch anything.  Coming back, the plane had 3” TV screens in the back of the seat in front of each person.  But again, they wanted $7.99 just to watch a few shows on DirectTV.  This was a red-eye flight back leaving Seattle at 10:50 PM so I don’t think they made much money on that flight.  Oh, by the way, Jet Blue still lets you watch basic programming for free.

And each flight I was on seemed to be overbooked.  Are there really that many people travelling or have they cut back on the number of flights?  I mean no wonder the airlines are losing money when they offer a bonus of $500.00 for a person to wait for a later flight (about 4 hours later) and get a free upgrade to first class on that later flight.

I guess it could have been worse.  I hear that Spirit charges up to $100 each way for each luggage bag.  When it costs more to send your luggage than yourself, how long will it be until people try to wear 10 changes of clothes on top of each other to avoid the luggage fees and then try to pull those seat belts around their 15 pairs of pants and actually snap the two ends together.

At one time, flying was elegant.  But no longer.  Now between the lines at TSA, boarding lines, and overbooked flights, it feels to me more like cattle on the way to slaughter or maybe waiting in a line for a 3-minute ride at one of our local theme parks.

Finally, I find it amusing when the crew says on your way off the plane.  “Thank you for flying with us and we hope to see you again.”  In the meantime, I am sure that we are all thinking, “Thank God I’m getting out of this box and can breathe real air again that won’t pop my eardrums.

Fortunately, next year the summit is in Charlotte, North Carolina.  If I get the opportunity to go (and I hope I do) It would be a bit of a long road trip, but I’m thinking maybe it would be better to just drive.

C’ya all next time.

PowerPivot Charts and Trendlines

Last time, I defined what a moving average is and why you might need one.  I then showed you how to calculate a moving average value in your Pivot table and then display it in a chart.  I close by asking the question whether there was a easier way to draw the moving average on your chart.  If all you need is to display the moving average on your chart and do not care what the values are, you can simply plot the [SalesAmount] field as a function of the day on the chart and then follow the simple steps that follow here.

If I right click inside the chart’s plot area (where the data is), I see a popup menu.  Near the bottom of this menu I next click the Add Trendline option which displays the following panel.

The Trendline options begin with six different ways you can ask Excel to fit a line through the data points including everything from a straight line through polynomial, exponential, logarithmic, and power functions.  However, the one I am interested in today is the last one, Moving Average.

Notice that after selecting this option, you have to define the number of period to include in the moving average where period is define by the units of the chart’s x-axis.  If we were to select 4, the resulting line would exactly duplicate the line I already displayed using my calculated 4-day average.  However, as I increase the number of days in the moving average, the fluctuations even out.  The following figure shows a 15-day moving average as a green line which now clearly shows the sales rising through the end of July and then dropping off in August.

So if I can create a Moving Average by simply using the Trendline options for a chart, why would I ever go through the process of calculating the moving average numbers manually?  The simple reason is that the Trendline option does not display the moving average values.  On the other hand, I can always display a table of my daily sales along with the moving average values as shown in this figure.

Well, I know this topic was a little short today, but I’m at SQL Saturday Orlando conducting a presentation on the new features in PowerPoint 2012.  If you have never been to a SQL Saturday event, you owe it to yourself to go to http://www.sqlsaturday.com and find out when the next one is in your area.  It is a great way to get a day of free training on SQL Server and related topics (like PowerPivot) and to network with your peers of similar interest.

C’ya next time.

Would You Pay Money to Sell Me Something?

Of course you might say.  Isn’t that the whole point of advertising budgets, promotions, and deals?  Sure it is, but I’m not talking about those kind of things.  I asking whether you would pay a fee to walk in the door and make a pitch for one or more of your products?  Again you say sure, I’ll even take you out to lunch or perhaps bring a couple of pizzas and sodas in for your team to enjoy while I demonstrate our latest and greatest products over lunch so it doesn’t cost you any work time.  Or if it is morning, how about some bagels and coffee while we demo our new system?

Well, if you work for the government, these might be considered bribes.  Yes indeed, the people who make the laws know that government workers are so underpaid that a few slices of pizza or a bagel and coffee would be all it takes for you to sell your soul, or your company’s soul, for that shiny new object the vendor is dangling in front of you in their slide presentation.  Maybe that is the way our politicians operate, but I seriously doubt that most professionals in the business world would be persuaded to make a purchase based on a pepperoni pizza and cup of coke.  I certainly would not.

So how can a vendor of a new product get that product in front of an audience without crossing the line?  Actually, it is easier than you may think.  The way I am going to suggest is that vendors actively seek out user groups and events sponsored by user groups and support them.  I belong to a couple of user groups and I’ll tell you from experience that a user group meeting without food, especially if the group meets after normal work hours in the evening, brings much lower attendance than a user group meeting with food.  But who should pay for the food?  Should the user group collect dues and use that money to buy pizza, sandwiches, or whatever it is they want to have at the meeting so hard working people will come to meeting right after work foregoing their normal evening meal at home?

That’s a possibility or at least it was years ago when you stored your programs on punch cards, but collecting dues from attendees at a user group meeting will probably decrease attendance when most people can find the same information in a white paper or through a free webinar on the Internet.  So how can a user group survive?  My answer is to enlist vendors who are willing to help defray the cost of the meeting location and food.  Depending on the expenses of your group, you may only need one vendor.  Others groups may require two or more vendors if the meeting location or food is expensive due to the number of users attending the meeting.

So what should the vendor get out of this?  The user group should be willing to give the vendor five to ten minutes to pitch their product, talk about their company, or anything else they want to say.  They should not be a main presenter for the meeting unless they present a topic that applies generically without focusing on their own product or service.  For example, a consulting company can come in to present how to improve the efficiency of your T-SQL statements as long as they don’t turn it into a presentation about how their product or service is better than everyone else’s product.  After the presentation during a social/networking time, they can pitch their product to whoever is willing to listen, but not during the main presentation.  The same goes for recruiters.  We had a recruiter at one meeting give a ‘state-of-the-industry’ presentation and talk about what makes a good resume and how to answer questions at an interview.  They did not directly promote their services or their company so it was fine.

You might want to come up with a schedule of how much you want to charge vendors and what they get out of it (a mention during the meeting, a 5 minute pitch about their latest product, or the chance to present the main topic).  Most vendors are fairly reasonable to work with.  And if they violate the rules and go into a sales pitch in the middle of a main topic discussion, ask them politely to hold that topic for the networking time and to continue on with the regular presentation.

So what about those companies that want to charge a vendor to come in the door?  Perhaps the answer is to work through local user groups to sponsor the groups in exchange for the ability to talk to the attendees.  Don’t forget to send special invitations to the user group meeting to those companies to which you want to market.  That will help everyone.  It will bring more attendees to the user groups.  It will give the vendors a chance to get in front of a targeted audience who potentially wants to see and hear about their company, product, or service.  Finally, the employees who attend the user group meetings can extend their network of peers and perhaps learn a thing or two that they can bring back to their workplace.

And the best thing is, none of this is illegal.  …Yet.

In full disclosure, I’ve been a big proponent of user groups since my early days in a group named DPMA back in the mid 80’s.  I continue to be active in SQL Server and SharePoint groups as well as a regular attendee and speaker at SQL Saturdays, Code Camps, and SharePoint Saturdays.  I hope to attend an IT Pro Camp later this summer as well.  In fact, two Saturdays from now on April 28^th I’ll be speaking at the SQL Saturday in Jacksonville, FL talking about using DAX in PowerPivot.  These events provide a full day of free training and usually include breakfast (donuts, bagels and coffee) and lunch.  These events are paid for by the vendor who will also be there with tables to talk with you about their latest products or services.  Many of the vendor also have their own give-aways as well.

So if you are a vendor, support your local user groups.  We are waiting for you.  And if you are an attendee at one of these events, stop to talk with a couple of the vendors to learn what they do.  You never know when you might need their product or service.

Time In A Cube

This is the first of a two part series on building and using time dimensions in SSAS and Excel Pivot Tables.  This week I focus on the SSAS side of the question.  My purpose is to show how to create a time dimension table from date fields in the fact table.  So let’s get started.

Let’s begin with the assumption that you have a table that you want to use in SSAS to build a simple cube.  The data I’m using comes from Texas vaccination data, but any table with dates will do.  The following illustration shows the column schema of my fact table along with the names of the other tables in the database.

Note that the table PatientVisits started as my primary fact table.  It contains links to several other supporting tables such as RaceTable, CountyData and Visit_Vaccinations.  Initially, the VisitDate field was not indexed, but I’ve already created a basic index which will be used as the foreign key into the date dimension table we will create here.

Let’s assume that we want to analyze vaccinations from different counties over time.  We first need to create a date dimension table.  In SSAS, we cannot use the date field in the fact table as a dimension.  Think about how we might use dates, we might also need to group visits based on other date criteria other than individual dates such as month, quarter or even year.  These fields obviously do not exist in the original fact table.  So the first thing you might want to do is to define what date groupings you might need or want.  I tend to use a generic date dimension which includes several of the more popular ways of grouping date data for all of my analysis.  In any specific case, I may add a few additional groupings, but at least it gives me a start.  These groups are shown in the following figure:

Because I frequently build this same table to get started with a project, I have stored the code needed to create my date dimension as the following script.

After running this script, I now have a table that I can populate with my date data, but what dates should I use.  My standard default is to look at the earliest and latest dates in the fact table as my end points for the table.  I usually extend these endpoints to include full years.  Therefore, I take the earliest date year and make the starting date January 1^st of that year.  Similarly, I take the latest date year and extend it to the end of that year, December 31^st.  I store these end point dates in variables in my script using the following equations.

My goal then is to use a script to loop through the dates between my starting and ending dates to add the corresponding date information to my date dimension table defined earlier for each day in that date range. (If I were working with a time dimension, I would use similar logic to define my earliest and latest times and then step through at some time interval for the times between these two points.)  In general, that means I’m looking at something like the following for a basic code block:

The challenge now is how to calculate each of the date (or time) columns that I need.  SQL Server T-SQL provides a useful date and time function,DATEPART() that can help you create almost any column you can imagine.  You may need to combine the results from the DATEPART() function with some additional functions to format the information or combine different parts together.  But it is still quite flexible.  For the columns that I needed here, the functions along with the parameters I used are:

In some cases, I need to change the format of the data returned by these functions to either combine it with other data (such as when combining text with numeric data) or to calculate other information I need for user friendly labels to the date data.  The following script shows my final version of the basic code block that loops through the dates to calculate my date information and to insert it into the date dimension table.

Running this script produces a date dimension table that includes not only all of the dates in my fact table, but also any dates missing from the fact table as well as dates at the beginning and ending of the period to fill out a full year of date data.

At this point, I’m almost ready to load my data into SSAS and start building my cube.  To work with just visits, I could directly continue from this point.  On the other hand, to work with vaccinations given by time period or by county, I also need to flatten the many-to-many relationship between PatientVisits and Vaccinations into a single table which I called Patient_Vaccinations.  This was required because a single patient visit could involve several vaccinations and obviously, each vaccination type was given to multiple patients.  I will discuss flattening (denormalizing) of tables in SSAS in a future article.]

Opening BIDS, I then create a new project for vaccinations.  The first step is to define a data source.  This is easy because I can simply define a connection to the database table as shown in the following figure:

Next, I define a data view.  In this step, I use the data source defined in the last step to select the tables I want to use.  This can be as simple as selecting the tables I want by click the checkbox to the immediate left of the table names.  After selecting the tables, any existing referential links between the selected tables will automatically be used.  If I have tables that are not linked, I must define the link.  I cannot have tables in the data model that are not linked.  In this case, I want to link the VisitDate column in the PatientVisits table with the PK_Date column in DateTable.  Note that the primary key side is DateTable since there is only one record in the table for each date.  The following figure shows the diagram linking all of the tables in my simple patient vaccination model.

Next, right click Dimensions in the Project to define the dimensions.  I need to create 3 dimensions for this example: Race, County, and Date.  When you create the County dimension, it automatically includes the region dimension as a hierarchy level (more about this at a future time as well).  So essentially, your data model should look like the following diagram.  Note that by flatting patient visits and vaccinations into a single table, I can effectively replace 3 tables with a single table.

I am now ready to define the cube itself.  Right click on Cube within the project to create a new cube.  First I must define the fact table.  This will be the Patient_Vaccinations table in my case.  Notice that I do not have to include all of the fields in the table.  I only include those fields that I know I might use a possible measure in the cube.  Keeping my active data small will improve performance.

Next I must select the Dimension tables I want to use.  Note that in this case, my model consists of a single fact table and all of the dimensions that I defined earlier.  This may not always be the case.  I can have a model which includes several cubes using different fact from the same or even different fact tables.  With each fact table, I can define a different set of dimensions needed to support and manipulate the data as the cube dimensions.  But I did say this was going to be a simple model.

When I am done defining the cube, I must perform two additional steps.  I cannot simply jump to the Browser tab to view the cube.  I must first select Build from the xxxxxx menu.  This process is generally fast and should only take a few seconds.  Then I must select Deploy from the same menu to prepare the cube to be viewed by a browser.  (Again I may go into details about what this means at a future time.)

If the cube Builds and Deploys successfully, I should see the following screen image which shows the basic framework of the cube along with a Field list of the available fields that can be used as measures and dimensions.

I generally select the measure I want to display first and add it to the Values box.  Then I begin playing with different dimensions to analyze how the measure changes as a function of the dimensions used.  The following figure shows one possible analysis of the number of vaccines given by quarter within year by county.  Of course by changing the dimensions, I can see many other relationships in your data.  In fact, the more dimensions I have the more possible relationships I can explore.

Obviously, there is a lot more you can do with cubes, but I’ll stop here for this time because next week I want to show how to create the same result using PowerPivot for Excel without needing to create a separate date table as needed here.  Also next Saturday is Orlando Code Camp (www.orlandocodecamp.com).  I will be presenting this topic live at one of the sessions.  Come see me and say, ‘Hi!’

See you then.

To Delegate or Not To Delegate

This week I want a personal road to talk a little about delegation.  Specifically delegation related to managing your team at work. Five years ago, I was given the responsibility of a small team to develop a new portal for our organization using SharePoint.  SharePoint was a new tool for all of us and we spent much of the first year just learning how the platform worked.  To make matters more challenging, much of our team was new not only to SharePoint, but they were also new to our organization.  I had no senior leads to help work with them.  (I still don’t.)  It was very much the blind leading the vision impaired.  Somehow, we made it through the early years deploying our Internet, intranet, school public-facing sites, and most recently collaboration.  While we had some consultant help for the first 6 months, we have been on our own since the summer of 2007 to the point where now we support over 4300 sites.  We did some things right and some things wrong, but overall, I proud of what our team has accomplished.

With that said, the hardest thing for me to learn as I moved into management was to delegate more and more of the work to my team.  I was use to being solely responsible for the tasks given to me.  I don’t think that is anything unusual.  In fact, I suspect that most programmers need to feel in control of the work they are responsible for.  Giving up some of the control is hard.  On the other hand, it is the only way to get major projects done.  So here are some of the ‘secrets’ I’ve learned over the years.

Keep your entire team informed about the direction you are going in, even if that involves multiple projects.  Make sure they see the big picture, not just the code they are working on this week. While you need to be open about the path taken to get to the destination, you can never compromise on the destination itself.

Encourage interaction between the team members.  Just because one team member has been assigned a task should not preempt another team member from making suggestions
or even questioning why a process is being done in a certain way.  This means making sure that all team members are open to talking about their work with other team members. Open discussions should not be limited to a weekly summary at a team meeting but should occur throughout the week if they encounter a problem that they cannot solve on their own.

Find out what each of your team members is good at doing and try to allocate tasks so they get tasks that they enjoy and in which can excel.  With any team, individuals will naturally begin to specialize in slightly different aspects of the projects your team gets assigned.  Some of this might be due to natural abilities, but I believe some is also due to a need to differentiate themselves from their other co-workers so that they can be the expert in at least a few areas where other team members are not as strong.

Provide a feeling of participation to all team members by inviting different members to project meetings based on the topic to be covered and the work that team member is doing. If they are so inclined, let them make part of the presentation at the meeting.  This may take time especially for a team member who prefers to work by him/herself behind the scenes.  Start with little things, but then get them involved more and more until you can trust them to work with clients on their own without your presence.

Encourage your team to learn new things, especially things outside of their area of expertise.  Sometimes a good developer can also be a good trainer, or documentation writer, or a presenter.  They may have a knack for working with clients to come to agreement on needs and wants of projects.

Training money these days is often scarce.  So encourage your team to look for other ways
to improve their skills (or as we say, sharpen their saws).  Local conferences can be a lot cheaper than conferences in another state especially if there is no travel expense and maybe no lodging.  On-line training courses can be significantly cheaper than in-person training.  Webinars, local user groups, books and trade magazines can fill in the gaps as well. The key is to make sure that any of these resources are used and that the training is something that the team member can put into practice immediately.

Does that mean that the manager can delegate everything and let the department run on autopilot?  No. Ultimately, you are still responsible for the work done or not done by your team.  However, the Information Technology manager who is not familiar enough with the technologies used by his/her group so that they can pitch in and help during heavy workloads is quickly perceived as just a boss and not a leader.  This does not mean that they need to be an expert in any of the technologies used by their people.  However they must have at least a fundamental understanding of how the technologies work so they can be a resource when the team is stuck on an issue or is divided over the approach to solving a problem or just has too much to do.  Of course, maybe my perception on this is due to the fact that I am still trying to home-grow that team lead that I never had.

Ultimately, I’ve learned that to some extent, you need to let your team find its own path.  With a clear understanding of what needs to be done and with some help from me, I think we have moved closer to utilizing each team member’s strengths. Are we 100% of the way there yet?  No, but we are getting close.  If that means giving up some of the tasks that I use to like to do, well, I can tell you that there can also be a great deal of satisfaction in seeing your team succeed.

So if I delegate all of the work I use to do to my team, what do they need me for?  Right now I like to think of myself as the SharePoint evangelist within our organization.  I look for and prototype new ways that departments can use SharePoint and some of our other tools.  I’ve talked to departments about using metadata to create document libraries that are easy to navigate without a ‘rats nest’ of folders.  I’ve talked to other departments about digitalizing forms so they might be able to go paperless.  Recently, I’ve been working with a few groups trying to combine SharePoint surveys with Excel Pivot tables to analyze the results to both prove suspected relationships and hopefully to uncover new ones.  While I am doing all of this, I know the rest of my team will handle the rest of the details all because I learned how to delegate more.

Training – Expense, Employee Perk, or Business Necessity

Depending on your point of view, you might see employee training as any of these three.  Taken in isolation, employee training does cost money.  There is the actual cost of the training of course, but there may also be travel expenses and room and board expenses if the training is offered out of the immediate area.  Then there is the ‘lost’ productivity while the employee is away from their regular duties.  Finally, some managers are afraid that by providing training, they just make employees more ‘marketable’ to other companies.

On the other hand, an employee may see the investment that the company makes in their training as an indication of their value to the organization.  Maybe salaries have been frozen and one of the ways that management can say thank-you to a productive employee is to send them to a training event to learn additional skills.  Everyone likes to feel appreciated and when someone invests time or money in you, most people will feel that is a benefit received from the organization that shows that management values them.

Of course a company will only invest in an employee in the hope of getting an employee that performs existing tasks better, faster, or more accurately or they can perform new tasks that will ultimately improve the bottom line of the organization.  Even using training, such as attendance at a conference, with no directly measureable return can help the business if it prevents an employee from looking for employment somewhere else where they think they will be appreciated more.  Losing the skills and knowledge of a good employee costs money to replace.  The cost of recruiting new talent can be as much as 20-30% of the employee’s salary.  And if the company has a high turn-over rate, they may need to offer higher salaries to attract new employees who may see that as an indication of problems with the organization.

In the Information Technology business, the one constant is change.  I’ve gone through several paradigm shifts in my career from programming in a version of mainframe BASIC that only allowed at most two characters to define a variable name, to FORTRAN, a little COBOL, Integer BASIC on an Apple II with a whopping 16 K of RAM, ASP and then ASP.NET, dBASE, FoxBase, FoxPro, SQL Server and most recently SharePoint.  In my experience, companies that did not change and adapt fell to their competition.  Employees who did not change and adapt found themselves working in other careers.

No matter how you look at it, training is a necessity whether you manage your own learning goals or your company supports and helps you obtain the training you need to stay current and relevant.  Trained employees can offer their organizations more value.  In exchange, training employees are generally more happy (read as not looking for a job elsewhere) because their job satisfaction is higher when they feel competent to perform the required tasks.

And if your organization does not provide training and you don’t have a lot of spare cash, there are many good books, webcasts and on-line tutorials.  Don’t overlook the value of local user groups.  Finally take the time to attend a local free day of training at a Code Camp, SQL Saturday, or SharePoint Saturday event near you.

MagicPASS Meeting April 2011

Kendal VanDyke last week asked me if I would do one of my PowerPivot presentations at this month’s MagicPASS meeting on April 20th because the scheduled speaker was not able to attend.  .  I had already prepared my presentation for SQL Saturday Jacksonville on the 30th so I figured, sure, why not.

Despite the fact the Excel has supported pivot tables and pivot charts for some time now, the built-in capabilities of Excel still limited the amount of data that you can use.  It was also not convenient to use multiple data sources.  But with the introduction of Excel 2010, Microsoft created an add-in to Excel called PowerPivot (originally named Gemini) which would use the Analysis Services VertiPaq engine and run in-process in Excel.  It would allow you to import data from many different data sources in building the pivot table and/or chart.  It would also allow you to have tables with millions of records.  It would take all of the data and compress it very highly and store the data in the Excel .xlsx file although not in the same way that it stored the other native Excel data.

From my point of view, PowerPivot is a great addition for business analysts to create different views of their data without having to write a bit of code.  They can choose different dimensions (parameters in a way) to look at, sort, and filter the data to answer many different questions.  Why should a database administrator or analyst care?  Because, why the business analyst can very quickly learn how to manipulate the pivot tables and charts for a given set of data to answer different questions, they probably cannot create the original data set or define the relationships to link the various data sources.  As a DBA or analyst, you can spend just a few hours to build the basic pivot tables files (individual or by building a data warehouse or even an Analysis Services cube) and then show the analyst how they can pick different measures from their fact table and slice and dice that data with dimension data.

If you are in Orlando and have time Wednesday April 20th, come on down to Stetson University in Celebration by around 5 PM.  If you can not make it, I’ll be doing a similar presentation at SQL Saturday Jacksonville on April 30th.