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.

The Role of Role Playing Dimensions

Perhaps you have heard the term Role Playing Dimension in regards to PowerPivot and/or Analysis Services cubes. This terms refers to the ability of one dimension to be linked to more than one fact in the fact table. Now strictly speaking, there is still only a single primary link between the dimension and the fact table and that link is used as the default link when aggregating data by that dimension. However, some dimensions, such as date dimension, often can be used against multiple fields in the fact table. For my example, I will use the Adventure Works DW data set that I use in many of my SQL Saturday presentations since it represents a typical sales database. Another good sample database is the Contoso dataset. Using the reseller sales table as my fact table (FactResellerSales), I can proceed to pull into my Excel PowerPivot table this fact table along with the dimensions for Product, Product Category, Product SubCategory, and Date. Notice that there is at least one relationship between each table and one other. This primary relationship is represented by a solid line in the figure below. However, there are three relationships between the FactResellerSales table and the DimDate table. One of the relationships is represented by a solid line and the other two are represented by dotted lines. Back in the source database, these three relationships are represented by regular indexes between these two tables. There is no indication to define which relationship is more important than the other. So how does PowerPivot select which one is the primary relationship between the two tables? The best I can figure out is that it is based on the order of the three different date keys in the fact table. In this case the OrderDateKey appears first and is therefore selected as the primary or Active relationship.

Looking at the raw sales data for a moment, I can see that the dates in the OrderDateKey, DueDateKey, and ShipDateKey are different with the order date occurring first followed by the due date and then the ship date. This would make sense in the real world. Unfortunately, if I did not have the ability to use role playing dimensions for each of these dates, I would either have to deal with only a single relationship between the date dimension table and one of these dates such as the order date or have multiple date dimension tables, one for each relationship. If I only used a single date dimension table, I would have to pick one date in my sales table to relate that dimension. Then any sum I calculated such as the sum of the order amounts, the sum of the ship amounts, or the sum of the due amounts would be associated with a single date such as the order date even though the due date may not occur until the next month or even next year. Unfortunately, this sample data does not show many such cases. However, I will show you one case at the end of the blog.

Our pivot table would then look something like this:

However, that would not be correct since we did not ship the items on the same day as the order nor was the amount due on the same day as the order. Therefore, we need another way to relate these two table when performing a summing calculation. That other way includes the use of the USERRELATIONSHIP() function which lets us define for the purposes of a single calculation which relationship we want to use. For example, to calculate the sum of the amounts ordered, we can sum the Sales Amount field by the OrderDateKey field as shown in the following equation:

When placed in our measure area of the FactResellerSales table, I can see that this calculation returns a value of over 80 million. (I will format as currency this in a moment.)

Similarly, the Shipped Amount can be calculated by summing the Sales Amount using the ShipDateKey as shown in this equation:

Finally the Due Amount can be calculated with the following equation using the DueDateKey field.

After formatting the measures as currency (which saves me time by not having to format this data in the resulting pivot tables separately), the measures appear as this:

Now I can proceed to build the pivot table by clicking on the PivotTable button in the Home ribbon and then selecting

PivotTable from the dropdown.

Excel prompts me to create the PivotTable in either a new worksheet or an existing worksheet. I will select a new worksheet here.

After clicking OK, Excel creates my empty PivotTable and opens the Field List to let me begin defining my PivotTable.

I choose a very simple layout with my calculated measures going across the top of the table and a hierarchy of time coming down the rows of the table.

By default when displaying the names of the months, Excel displays them alphabetically. Typically this will not please your manager. Therefore using a technique I discussed in an earlier blog, I define a column that contains the month number as the column by which I want to sort the column names.

Keep in mind that you only have to do this once. All subsequent PivotTables and PivotCharts will use the sort order assigned to the column EnglishMonthName without my having to do anything else.

Now the months appear in the correct order.

But more importantly for this demonstration, if I drill down into any of the months, I will see the sum of the order amounts, shipped amounts, and due amounts correctly summed by actual dates. Some positions will naturally be empty if there was no order, ship, or due activity for that day.

As promised, the following figure shows an example where orders were taken on two different days within the month but shipping and due dates only occurred once. The point being that the individual sums are correct for the days in which they appear.

Looking at the orders in April of the above figure, you may question the math saying that $2,204,542 plus $82 is $2,204,624, not $2,204,623. This is the result of rounding each individual amounts to whole dollars and is not an indication that Excel cannot perform math. All sums are calculated on the actual dollar amounts and then rounded.

So this is one simple example of how to use role playing dimensions. Dimensions other than dates can serve as role playing dimensions, but most people will encounter the need for role players when working with dates.

C’ya next time.

Only at Participating Locations

Ever wonder about this phrase often found at the bottom of advertisements, coupons and promotions? Did you ever think about what it really means or how it can be used? Or how about the phrase, ‘Participation may vary by location.’ Just what does that mean to you and me? There was a time I assumed that any business chain with multiple locations around the country owned by a single parent company had no choice but to follow all nationally advertised promotions. I also assumed that a business that was franchised had to follow any promotions of the franchiser, or parent company so that all customers would have a common experience no matter where they went. If you live in a small town, you may not notice the difference between how one business location operates compared to another because you may not have that many locations. Furthermore, you may think that if the parent company is spending large sums of money to develop and advertise promotions to drive customers to your door that you would want to take advantage of that business hoping to convert at least a percentage of them into repeat customers.

But apparently, that is not the case. In fact, I’ve notice quite a few businesses who advertise on television and direct email but when you go to their local stores, they do not honor the promotions. Perhaps the local stores feel that people might feel guilty about just getting up and walking out when a local store manager says, ‘Sorry, we are not participating.’ But are they really sorry? If they were so concerned about our feelings they would not trick us into their store only to say that they are not participating. Maybe it is time that we walked out of their store.

Truth be told, I live in tourist town, Orlando, FL. I first notice this effect several years ago. One time when we questioned the policy we were even told that because they are a tourist location, they do not honor the advertised discounts. I suppose they figure that the tourists have no choice. Most cannot make meals in their rooms. Most do not know the area well enough to risk finding another location that may be participating. And most don’t want to take the time away from their vacation just to save a few dollars. So you might think of it as a convenience fee.

Besides food chains, this effect becomes even more obvious with the price of gasoline which seems to almost consistently go up the closer you get to the major tourist attractions or the airport and its surrounding car rental agencies. Yes there are some exceptions and the locals know where they are just like the locals know the best places to eat that are not part of nationwide chains.

But let’s get back to the main issue. I know several of the fast food chains with locations near the tourist attractions that will not honor the promotions of the nationwide chain. Several times I’ve been told point blank that the locations near the attractions can charge more because they are there just for the tourists, not the locals. Were they telling me to go away? I guess I moved to the wrong side of town.

A good example occurred recently at a nationwide chain that was offering a meal deal of an entrée with an appetizer. (I will not go further into the details here.) The promotion was sent directly to my email address because I subscribed a long time ago to their ‘club’. Granted, the promotion added the words, ‘Limited time only.’ The thing is, I received that email just last Thursday. The next day, Friday, we decided to go there for supper to take advantage of the deal. As we sat down and were given menus, I noticed that there was no reference to the ‘special’. So I asked about it thinking that maybe they only gave the promotion menu to those that asked. Instead I was told by the waiter that he had heard about that promotion, but thought that it had ended but would go ask the manager. A few minutes later he came back and told us that his manager said that the promotion indeed had ended. Fortunately I could still bring that email from the previous day up on my phone. Aren’t smartphones great? So I showed him the email and the date on the email. He went back to his manager to ask again. This time when he came back, he said that his manager said, ‘We are not participating in that promotion.’ So we got up from our seats. He asked us if we were leaving. We replied that we were not participating in bait and switch tactics and left.

Really makes you wonder though whether the promotion was only for one day (which it did not say in the email) or whether they just did not want to offer it to their other customers, the majority of whom where tourists.

In any case, I say that it is bad business to advertise any promotion and then decide not to honor it just because your location is near tourists, a more affluent part of town, or any other arbitrary reason. It may be a long while until I go back to that location. Maybe they don’t care. Maybe they get enough business from the tourists. For me, I’d rather find another location where the location manager operates fairly and honors the promotions of the parent organization he is a part of. If you don’t like the promotions, become a store manager of a store that does not have promotions or start your own store if you want to do whatever you want. The bottom line for me is that unless there is a legal reason not to honor a business promotion created for the chain that is designed to send more customers to your door, you should honor the promotion and give those customers the best experience possible so that they might decide to come back again and again.

C’ya next time.