Adding Dimensions to your Cube

Last time when we built our Contoso cube, we included the Product table as a dimension off the Fact Sales table, but we failed to included the product category and product subcategory tables.  Therefore, it is not possible to aggregate sales values by product category or subcategory.  You might wonder whether it is possible to rebuild the cube adding these two dimensions without having to start over with a new cube.  Depending on the amount of formatting we did, this could cause quite a concern.

Fortunately, there is a way for us to go back and not only add these two tables, but also define the product – subcategory – category hierarchy while preserving all the other work we may have done.  Let start by opening the Contoso Retail DW project from last time and displaying the Data Source View as shown in the following figure.

We need to add the Product Category and the Product SubCategory tables to our existing data source view.  To do this, right click anywhere on the background of the view diagram.  A popup menu appear and in that menu, select Add/Remove Tables.  The assumption is that you want to add a table from the same data source connection as the rest of the view, so when you click on this option the following dialog appears.

Note that the data source is preselected.  I can then click on the tables I want to add to the current view.  I can either click on the table to select it and then click the move button with the greater than sign indicating a move from the Available Objects list to the Included List or I can double click on the table name.  I will move both the DimProductCategory and DimProductSubcategory tables to the Included objects list.  With my tables selected, I can click OK to close the Add/Remove Tables dialog.  This action adds the tables to the view diagram.  However, objects may appear to overlay each other or overlay the connecting lines between tables.  To clean up the diagram, click in an open area of the diagram again and this time select Arrange Tables.  Even after choosing this option, you may still want to arrange manually the tables.

I now have the tables in my view, but I still need to create dimensions for them.  I could create separate dimensions for each of these tables, but I know that they form a hierarchy with Dim Product.  Therefore, open Dim Product.  In the Data Source View, right click in an open area of the background and select Show Tables from the dialog that appears.  In this dialog, select both the DimProductCategory and DimProductSubcategory tables.  You can do this by clicking first on either one and then press and hold the Ctrl key while you click on the second table.  Then click the OK button to complete the selection.

Arrange the tables manually in the Data Source View panel or use the Arrange Tables option in the popup menu that appears when you right-click on the background area.  You should have something that looks like the following figure.

Click on the fields from these tables that you want to include as attributes in the dimension.  For example, let’s include Manufacturer, ColorName, Size, UnitCost, UnitPrice, Status, ProductCategoryKey, and ProductSubCategorykey.

Next select ProductKey in the Attributes panel and then in the Properties window, locate the property NameColumn, click in the value column to display the button with the ellipsis, click on the button to display a list of available columns from the DimProduct table and select ProductName.  This selection serves as an alias.  When I build my cube and drag Product Key into either the rows or columns, ProductName will appear rather than the value stored in ProductKey.  Therefore, I would not want to include ProductName as one of my attributes for this dimension.  While in the property dialog, find the OrderBy property and change it to Name.

In a similar fashion, I assigned the field ProductCategoryName to the NameColumn property of Product Category Key and ProductSubcategoryName to the Name Column property of Product Subcategory Key.

Next, drag product Category Key over to the Hierarchies panel.  This field will form the top level of a new hierarchy.  Right click on the header of this new hierarchy and change the label from Hierarchy to Product.  Now drag Product Subcategory Key beneath Product Category Key.  This indicates that categories consist of subcategories.  Finally drag Product Key to a position beneath Product Subcategory Key.  Save your changes. Then click on the Process button in the Dimension Structure toolbar.

You should next see the Process Dimension dialog.  Click Run.  This step could take a few second.  When the step completes successfully, click the Close button to close the Process Progress dialog and click the Close button again to close the Process Dimension dialog.

Now open the cube definition again by double clicking on it.  Right click on the project name in the Solution Explorer and click on Deploy.  When the Deploy completes successfully, open the Browser tab.  Drag one of the measures from the Fact Sales Measure Group to the data area (such as Sales Amount).  Then drag the hierarchy Product from the Dim Product dimension to the row fields area.  Notice that the Product Category names appear with a small box containing a plus sign to the left of each value.  Click the plus sign in one of the boxes to drill down to the next level, subcategories.  Again you see a list of all the subcategories in the chosen category and each has another small box with a plus sign to the left of its name.  Click one of these plus signs to drill down to the next level, product.

You can see that the hierarchy automatically appears exactly as we would want it.  There is no need to ask the user to define the hierarchy manually by dragging the individual product dimension attributes into the cube.  Furthermore, it guarantees that the user does not accidentally try to build the hierarchy beginning with subcategories, then categories, and finally products.

One last point, after building the hierarchy that you want to use, you can and probably should remove the individual attributes (Product Key, Product Subcategory Key, and Product Category Key) from the DimProduct dimension.

Next time I’ll look at way to add fact tables and dimension tables to an existing cube.

C’ya later.


Change is Hard Without the Sizzle

I heard the statement, ‘Change is Hard’ many times over my career.  I may have even said it a few times.  Perhaps you have too.  The problem however, may not be as simple as change itself being hard.  Perhaps change is easy, but the people having to deal with that change resist the change and do everything in their power to see that change does not happen.  After all, there is a general feeling that when things are the same today as they were yesterday and will be tomorrow that everything is fine with the universe.  However, sometimes it is just our perspective of time or space that makes it look like things remain the same.

Take for instance the so-called solar constant, the amount of energy that the sun puts out in a given time period.  Most people like to think of the sun as something that does change.  Only relatively recently in the grand scheme of human history has science recognized that the sun undergoes at least something that appears to be an 11-year cycle of solar activity.  While it may be true that the variation in solar output may not be apparent to humans without instruments to measure it, we now know that it exists.  In fact, the temperature on this planet has never been constant but for a few years geologically speaking.

Another good example of static thinking was the belief when I was going to elementary school that the continents have always been the same and will always be the same.  The mere suggestion of continental drift was considered fringe science at best if not outright fiction.  Today we accept continental drift as scientific fact.

Moving to more recent times, the question of global warming has occupied many scientists over the last decade as well as the general public and politicians.    While many theories exist on the cause, which I will not get into here, it is interesting to see how global temperatures have changed over the last hundred years.  The following link will shown you an animated map of the world with changes in global temperature using 10 year increments:

So back to change.  People don’t like change especially when they don’t feel like they have any control over it, and sometimes they don’t.  On the other hand, people who are instruments of change seem to have no problem accepting change, but also trying to accelerate it.  A good example of that effect occurs in Information Technology (IT).  The IT field is characterized as a field that is in constant change.  Since I first started working with computers in the late 70’s, the size of the computers, the languages used to program the computers, and the user interface to those computers, just to name a few areas, have changed dramatically.  However, being in the IT field by choice I tended to embrace those changes always looking for how they might improve my ability to create new solutions to problems.  At the same time, other people were resistant to many of those changes.  Some even feared that computers were getting too powerful.

Then Apple came along and broke through that fear for most people and not only got them to accept change, but to embrace the change.  With products like the iPod which allowed us to carry our music with us and the iPhone which allowed us to not only communicate with each other, but to surf the internet, to download and to play games.  The iTouch was sort of a marriage between the iPod and the iPhone.  Then there was the introduction of the iPad, which has taken much of the world by storm.  Each of these devices was a major change, yet people not only accepted these changes, but they would camp out over night in front of stores to get the next version of these devices.  Why did they accept these changes so easily while still resisting other changes in technology?

I suspect a couple of factors overlap here.  First, people tend to accept change more when it helps them relax, play or be entertained.  On the other hand, they tend to resist changes that ‘others’ push onto them at work to do their job differently.  In the first case, the change is something they choose to do.  In the second case, the change is something someone told them that they have to do.  Maybe that also explains why the IT staff is more enthusiastic about new products and product releases while the rest of the staff looks at the same product upgrades as just something else they now have to learn.

So perhaps the key to getting more people to accept change at work (or anywhere) is to help them to be part of the change.  Group or individual discussions on how the change will make their job easier, not just different.  I remember the first time we tried to get our secretary at an engineering company in Pennsylvania to give up her typewriter and create memos and documents in a word processor on a PC.  She resisted it.  She kept her trusty typewriter next to her desk and set the computer over on a table a few feet away.  However, when she saw how easy it was to make changes to letters and documents that our department head always seemed to change after she finished typing them, she soon moved the typewriter out of the way and placed the computer on her desk without any further suggestions from us.

I suppose the real issue is finding a way to get people to embrace the change.  Maybe that is what is really hard, not the change itself.  Finding a way to get people to stand outside a store all through the night to get a new $400 phone would seem impossible had we not already seen it happen several times.  Sell the sizzle.  Appeal to the customer’s emotional wants.  Telling people to stop driving will not solve global warming because that change is too hard and there is nothing enticing about it.  The challenge will be to find new products, new ways to do things to get people to want to do the behavior that will lead to the changes wanted.  Perhaps one possible solution is the enticement of letting more office workers get their jobs done from home several days a week utilizing ‘live’ meetings over the internet and perhaps even shorter hours if they can get the same work done more efficiently (with less time spent talking around the water cooler).  I’m sure they are many other solutions.  Maybe you have one or two suggestions that could change the world.  The world is waiting.

Until then, c’ya next time.

Customizing A Dimension, Part 1

This week I’ll take a look at how to customize a dimension in an SSAS cube.  Several weeks ago, I mentioned my preference for creating my dimensions first, prior to creating the cube in SSAS.  However, is not your only option as I mentioned at that time.  You could create your cube directly after defining the data source view you want to use.  If you remember, I mentioned that while the cube wizard will identify the potential dimensions and add them to the cube, it only adds the key values from each dimension, ignoring all the other attributes.  I may have mentioned that you could go back later and ‘fix’ that omission.  So today, I’ll show you how.

I’m still using the ContosoRetailDW database.  Suppose I had the following cube design in which I created the Contoso Retail DW cube using the wizard and let the wizard create my dimensions.  When I open any of the dimensions in the design surface, you can see that the only attribute included in the dimension is the key value.  On the other hand, you can see in the Data Source View pane that this table has many other potential attributes that a user may want to use in the cube.

Before adding the other attributes, let’s make a few changes to the properties of Datekey.  First, let’s change the name of this attribute to simply Date.  You can do this by right clicking on the attribute Datekey and selecting Rename from the popup menu.  Then using your mouse to position the cursor edit the name by selecting and deleting the characters ‘key’.

When you use this dimension in your cube, if you were to add Date (previously Datekey) you would see a value that included not only the date, but also a time value.  It may be better to display the date in another format.  There are several options from the table including FullDateLabel and DateDescription.  You can tell the cube to display one of these fields in place of the attribute Date when displayed in the cube by going to the properties pane and selecting the NameColumn property as shown in the following figure.  Click in the far right side of the value portion of this property row to open the dialog box shown.  Then simply select the column you want to display in place of Date when the user add the Date attribute to either the cube’s rows or columns.

Next, to display addition attributes in the cube, select the fields from the list of fields in the table DimDate as shown in the Data Source View panel.  You do not have to select all of the fields, only the ones you think the user may want to include in the cube or which you may want to use when building a hierarchy (I will cover this next time).  Note, it is generally considered to be poor practice to include the column you selected in the previous figure as an additional attribute.  Effectively, that would make the same field available using two different names which would probably be confusing.

After selecting the fields you want, simply drag them with the mouse to the Attributes panel so they appear as shown in the following figure.

After making these changes, you should save your solution before processing it.  You can do this either from the Save All option in the File dropdown in the main



Before you can display your cube and use the new date attributes, you must redeploy the cube.  You can do this either through the Build menu or you can right click on the project in the Solution Explorer panel and select Deploy

When the deploy completes successfully, you may be anxious to immediately click on the Browser tab to see you changes.  However, when you attempt to open the Dim Date dimension, you will see instead an error message as shown in the following figure.  This error message occurs because while you deployed a new cube definition (which has been saved in SSAS with the same name as the project).  You need to update the data used by the browser.

An easy way to ‘fix’ this problem to get the updated data is to click the Reconnect button in the tool bar within the Browser Tab.  (Click the Browser Tab to display its custom toolbar.)


Now when you expand the DimDate dimension, you see all the new attributes recently added.  You can then define your cube using any of these attributes such as the Date attribute itself which now displays the FullDateLabel rather than the DateKey value as shown in the following figure.

However, notice that the Sales Amount values are not formatted as currency.  I mentioned a way to format the measure data in the cube previously, but here is a better way.  Click back on the Cube Structure tab (after opening the Cube definition again if you do not have open).  Expand the FactSales section to display all the measures in this measure group (table) as shown in the following figure.  While you could select each field individually, a faster way to format multiple fields with the same format string is to first display the measures in a grid rather than a list by clicking the Show Measures Grid as shown below.

Then click on Unit Cost as the first of the attributes that should be formatted as currency.  When while holding the CTRL key down, click on the other attributes that you want to formatted as currency such as: Unit Price, Return Amount, Discount Amount, Total Cost, and Sales Amount.  Then go to the Properties pane and locate the FormatString property and click the dropdown menu button on the right side of the field value.  This action displays a list of possible pre-defined formats that you can define.  For example, we can simple select ‘Currency’ or we could select one of the other currency type formats such as ‘$#,##0.00;-$#,##0.00’ or you can create your own format such as ‘$#,##0;-$#,##0’ which shows the values with no cents.

In a similar fashion, select the measures Sales Quantity, Return Quantity, Discount Quantity, and Fact Sales Count and then select FormatString in the Properties pane and set the value to ‘#,#;-#,#’ which essentially tells the cube to display these values as integers.

Of course you will need to Deploy your cube again (right-click the project name in the Solution Explorer and select Deploy).  After your deploy completes successfully, click the cube’s Browser tab.   This time, your cube will still look like it did before without any of the formatting changes you just set.  However, at the bottom of the design area, you should see the information box shown below which tells you that the cube has been updated and that you must reconnect to the data.

You can either click the Reconnect button as I mentioned earlier or you can simply click the hyperlink ‘Reconnect’.  After a few seconds, the cube updates the values displayed to show dollar values like Sales Amount as currency and count values like Sales Quantity as integers as shown in the following figure.

That’s all for this week.  However, one other piece of unabashed self-promotion that I would like to mention is that I have been selected as a speaker at this year’s SQL PASS conference in Redmond.  If any of you will be attending, stop by to say hello.

C’ya next time.

But It’s Already In There

Have you ever had co-workers come back from the latest conference with brochures and a gleam in their eye about the latest new software application, tool, or utility they saw in a demo, presentation or keynote speech?  How about the manager who has no idea about what software is running within his/her company who comes back from a day of golf,… excuse me, a day of consulting with their peers and now has a great idea on how to revolutionize the IT department, save money, and reduce head count while providing more services?  And then there are the constant influx of sales staff who want to come in for a meeting that you never asked for and you don’t have time to attend to show you how their latest product or release will make you famous in your organization and help you get that big bonus or promotion that you know you deserve.

The problem is that all too often the ‘new’ thing they are trying to sell you can already be done using existing software and services you already have but have never used.  A good example of having software that you are not taking full advantage of is the Microsoft Office Suite.  Have you ever had someone try to convince you or your management that you need a custom form generation tool so you can digitalize all your organization’s forms and go paperless saving tons of money by not needing as many printers, printer supplies, paper, storage for the resulting paper, etc.  The goal to go paperless is an admirable goal, but have you really considered the tools you already have and decided that they absolutely could not meet your needs?

For example, how many people know how to create forms using Microsoft Word?  Did you know that you can include data fields in the Word document that include not only text fields, but check boxes, radio (option) buttons, dropdown selections, and more?  Did you know that you could protect the document from inadvertent changes by the user by locking the text but leaving the input fields editable?  Did you know that you could generate PDF forms directly from your Word document?

Let’s take a deeper look at forms.  Have you looked at the programs included in the Microsoft Office suite version purchased by most corporations?  Did you notice a program called InfoPath?  Maybe you have no idea what InfoPath is.  Maybe you think that InfoPath only works with SharePoint.  It does not.  In fact, you can use InfoPath forms from a file share or use them in emails to get responses from uses.

If you took the time to learn the tools you already have, would you need that other software?  I’ve heard the argument that staff members never used these tools because they have not been trained on how to use them.  Serious, you believe that the new software will provide that much better training?

Another example is the use of images.  It is easy today for people to take pictures with their digital cameras or even their smart phones and try to insert those pictures into documents, PowerPoint presentations, and even web pages.  However, did you do any editing of the picture before you used it?  Did you crop the picture down to only the important elements?  Do you really need the sky to take up half the top of the picture or the ground to consume a third of the lower half of the picture?  Did you take the time to remove red-eye effects from the people or do you just like those glowing red eyes in the photos of your boss?  Again if you have the Microsoft Office Suite, there is a tool that can be found in the Start Menu under the Microsoft Office folder à Miccrosoft Office Tools à Microsoft Office Picture Manager that can do much of your image editing.  Furthermore, if you simply add the picture into a Microsoft Office Word 2010 document, you can select the image and access many image editing features directly from within Word.

If you have been reading my blogs for the last year, you probably already know that I am a major proponent of using PowerPivot for Excel to analyze departmental data.  There is no need to buy a fancy and expense business analysis tool to perform basic analysis if you have a good understanding of how to use Excel.  I just heard that the Walgreen drug store chain uses PowerPivot, not a fancy and expensive BI tool, to analyze all their data.  Sure you might want to work with your DBA to build the data infrastructure (data model), but with all the tools in Microsoft Excel 2010, you can custom format your pivot tables, pivot charts, and other data using conditional formatting, Sparklines, and other features into a functional management dashboard.  If your organization also uses SharePoint 2010 Enterprise and upgrades the database to SQL Server 2012, a new included feature called Power View (yes, it is two words) provides more controls to ‘dress up ‘ your analysis results to look as good as most of the commercial business intelligence analysis tools.

Now don’t assume that I mean to imply that you should never consider other products or services.  The IT industry is in constant flux and sometimes new tools are exactly what you need.  However, I do strongly recommend that you look at new tools with both eyes wide open and with a strong understanding of what your existing tools are capable (even if you are not yet using those features).  Also do not be distracted by flashy presentations or ‘if you buy today’ type of high pressure sales offers.  Consider what opportunities the new tool brings to the table that you do not have but that after a reasonable analysis you decide that you and your organization really needs.  And always, always be sure that what you really need does not already exist in a tool that you already own.

C’ya next time.

Using A Named Query

This week we continue to look at SSAS on a topic related to last week’s look at Named Calculations.  Named calculations are great when you want to use existing fields within a table to create a new field.  You can even define an expression that uses fields from other related tables in the data view to create a new field.  However, sometimes a named calculation just is not enough.

A good example of when named calculations may not be enough is when you want to flatten a hierarchy of dimension tables into a single level.  In the Contoso database we are working with, the Product dimension links to the product subcategory dimension to get the product subcategory name.  Then the subcategory dimension further links to the product category dimension to the product category name.  While you could model the sales cube using this hierarchy of dimensions from product to product category, a better choice in terms of performance is to flatten the model and place the product subcategory name and the product category name in the same table as the product.  The way we will do that this week is with a named query.

I’m going to assume that because you have read some of my previous blog posts, you can build a data connection and a data view against the Contoso Retail database that I’ve been using the last several weeks.  With the diagram for the data view displayed in the central panel of BIDS, right click on any of the background  area of the diagram.  A menu like the one shown below appears.  You can click New Named Query to begin the definition of a new named query.

Defining a named query is very similar to creating a view within SQL Server Management Studio.  When I click the option to create a new named query in the above menu, I am presented with the following dialog to help define the query.  Note that I really must define a unique name for the named query that does not currently exist in the view.  I must also identify the data source that I will use, but the default automatically points to the same data source as the current data source view.  Optionally, I can include a description for the named query.

I then have the option of building the query using the visual tools to add the tables from the current data source that I want to use.  Note that these tables do not have to be already included in the data source view.   If relations already exist between the selected tables, the diagram automatically includes those relationships.  Otherwise, I must define relations between the tables manually.

I can then select the fields from each table that I want to include in my named query by clicking the check box before the name of each field.  Note that as you select fields, they appear in the second panel of the Query Definition and display the other properties of the selected fields.  Also while I am selecting tables, defining relationships, and picking the fields I want to use, the dialog automatically writes the SELECT statement needed to gather the data in the third panel of the Query Definition.

Yes, I could directly enter the SELECT statement if I so desire, but to avoid possible errors including spelling errors and syntax errors in the expression, I find it easier to use the graphical design interface.

Once I have built the SELECT statement for my query, I click OK.  I now see my new table on the design interface of the data source view.  However, it is not by default connected to any of the other tables.  Therefore, the next thing I need to do is to define a relationship between one of the other tables in the view with the new named query.  I can do this by identifying a field in another table that corresponds to a field in the named view, clicking on that field and dragging down to the corresponding field in the named query.  In my example, I would want to connect the ProductKey field in the FactSales table with the ProductKey field in the named query. When I do this, I see the following message because the named query does not have any indexes defined that can serve as the primary key in this relationship.

By clicking Yes in this dialog, I let SQL create a logical primary key for the named query so that it can define the relationship.  At this point, my view dialog looks like the following having both a DimProduct table and a ProductWCategories named query (virtual table).  I really don’t want or need both.

One way to get rid of the old DimProduct table is to right click on its header area to display the menu shown below and select Delete Table from DSV.

While this works, there is another way that I could have both build the new named query and replaced the old DimProduct table as a single operation.  Let’s assume for a moment that I had not yet created  the named query.  I could have instead started this process by right clicking on the header area of DimProduct and selected Replace Table from the menu that appears.  Notice that this menu option has an arrow pointing to the right along the right side of the menu.  This indicates that this menu option has a fly-out of addition options.  When I hover over Replace Table, the fly-out appears with two options, the first of which is to replace the existing table with a named query.

If I had selected this option, I would again be taken to the dialog that allows me to define the named query that we saw previously so I will not repeat it here.  However, when I completed the named query, rather than returning to the view dialog and seeing both DimProducts and the new named query, we would see only the new named query saving me a step.

Note in the above image that the second option of the fly-out also allows me to replace a table with another table rather than a query.

Regardless of which way I defined the named query, I must then deploy my solution before I can open it in the built-in browser.  The figure below shows a simple pivot table created from my cube definition that builds a manual hierarchy by dragging each of the individual fields to the row labels beginning with Product Category Name, then Product Subcategory Name, and finally Product Name (through the Product Key).

Next time, I will look at building the hierarchy for products directly within the dimension so you only have to drag one element onto the pivot table to create the entire hierarchy automatically.

C’ya next time.

It’s Not My Assignment

How many times have you heard someone say, “It’s not my job!” and then walk away to do something else.  Maybe you were at work and asked someone for help to solve an issue and they shot back at you, “It’s not my job!”  Maybe you asked the secretary to pick up a package that was just delivered to the mail room and you heard, “It’s not my job!”.  Perhaps you have been in a restaurant and accidentally dropped your fork and asked a passing waiter or waitress (not your original one) for a new fork and you got the response, “It’s not my job (table)!” as they continued to walk past you.

A recent article by Lyle Feisel in The Bent discussed this topic in which he maintains that your job includes everything that affects your customer, client, or whomever you are working for.  On the other hand, your assignments are the specific tasks that have been written down for you to perform.  He maintains that you get evaluated by your boss based on how well you do your assignments, but only you can evaluate how well you perform your job.  Job satisfaction is not based merely on assignments completed.  The problem with assignments is that no one can anticipate everything that you may ever need to do in your job.  There will always be additional tasks or perhaps new tasks that no one thought of before that should or must get done for your organization to be successful.  If you sit around and wait for someone to assign those tasks to you or to someone else, they may never get done before the organization loses sales, clients, or perhaps even goes out of business.

So your job might very well be to do whatever it takes for your organization to be successful.  However, there is a downside to this train of thought.  (Of course there would be or I would not be talking about it.)  Suppose there is a task that no one else in your company is willing to do, but it is a task that is important to the future success of the organization as a whole.  You could easily do the task even though it has not been assigned to you.  Maybe it only takes a few minutes each day.  Perhaps you think that by showing others that you are willing to pitch in to do these tasks, that others may offer to help out perhaps doing the task on alternate days.  Perhaps you think that management will reward you for your initiative to assume these tasks by giving you a bigger raise, bonus, or other perk to recognize your efforts.

But here is where things can and often do go wrong.  When management just assumes that you now own that task along with everything else you do, it can lead to a deadly spiral.  Other employees see this and stop doing other tasks hoping that you will pick up their slack as well so they can play solitaire, talk by the water cooler or take two hour lunches.  Meanwhile you burn the midnight oil getting your work done along with their work.  Management often does not care who does the work as long as it gets done so they don’t see this as a problem.  At least they don’t see it until something happens.  Suppose someone in your immediate family gets sick.  Perhaps it is your spouse, child, parent, sibling or some other close relative and you need to cut back on all of the extra work you have been putting in nights and weekends to care for that family member.  All management sees is that the tasks that you now ‘own’ by performing them when no one else will belong to you and they are not being done and therefore you are to blame.  You have become a ‘slacker’ in their eyes.  You are no longer a team player.  Forget the fact that Bill leaves a hour early each day.  Forget the fact that Doris plays solitaire for hours or shops on the web.  Forget the fact that Dan spends half a day each week working on his fantasy baseball|football|basketball team.  Management has come to expect you to do these tasks and you are not doing them.

This does not happen you say.  Management would never let others get away with playing games at work while you slave 10 to 12 hours each day.  Perhaps you are right.  Perhaps instead your company has been going through downsizing over the past several years during this recession.  As people leave the company, management has expected the remaining staff to pick up all the extra work that the other people did.  Now as the economy improves, they may be thinking that they can make bigger profits as sales improve if they don’t hire back those laid off employees because the existing employees have shown that they can ‘get by’.  In fact, they reward those employees who work the hardest and save the most money with more work and more demands to save additional money.  Soon those employees face a choice of either burnout or move-out.

Does this mean that you should never go the extra mile, or help others who may be going through some tough times?  No.  However, you may need to make it clear that performing these extra tasks does not imply that you now own those tasks.  Make sure everyone understands that you are just providing temporary help until someone else can take over those assignments.  Your job is to help make the organization survive and satisfy the needs of the customers/clients.  However, your life is not just about your job either.  Striking a balance between these two is the only way to enjoy life and your job.  Unfortunately, there is no one right answer other than to say that either extreme seems to be a poor choice.  So next time you see some small way you can help someone else out or perform a task that no one is doing, make a difference and pitch in, but don’t let others think that your willingness to help implies that you want to own every task either.

C’ya next time.

A Calculated Column by Any Other Name

This week I will look a little more into how to manipulate the data going into the cube by showing you how to create a Named Calculation that you can then surface in the pivot table.  While it is called a Named Calculation in SSAS, it is essentially the same thing as a Calculated Column.  So let’s begin.

Named Calculations are defined in the Data Source View.  In this case, I’m opening my Contoso Retail data source view that I’ve used for the last two weeks and want to focus on the FactSales table.

Notice in this table that there are quite a few fields related to costs, prices, quantities, etc. for each sale.  One field that is missing is the Total Profit field. If we want to perform analysis on profit as a function of the sales channel, we need to create a new measure for Total Profit.  Of course we could have done this back in the original database.  However, suppose that for some reason, this is not allowed.  Therefore, we need to calculate this field in the SSAS cube project instead.  Using the existing fields from the FactSales table, we know that we could calculated the Total Profit using an expression such as:

(UnitPrice – UnitCost – DiscountAmount) * (SalesQuantity – ReturnQuantity)

Therefore, we can open the Data Source View and right click the FactSales header in the diagram and select the command: New Named Calculation.

This command opens the dialog: Create Named Calculation as shown in the next figure.  In this dialog, you must enter a column name for the new column.  Obviously, it must be a unique name for the table.  You must also specify the expression.  The expression language is determined by the native expression language of the database.  Since my data is in SQL Server, that means T-SQL.  You can also provide a Description.  However, that field is optional.

If you click OK in this dialog and then redeploy the cube, you should on successful deployment be able to open the cube and add Total Profit to the Totals or Detail Fields area of the pivot table.

In the figure below, I also added as a row dimension the Channel Key which I mapped to the Channel Name field to provide a user friendly name.  However, you may remember that we mentioned this problem previously.  The raw formatting of the summed data makes the values hard to read and interpret.

You could specify a format like we did last week by opening the cube and using the Perspectives or Translations tab to change the properties.  Another way to achieve the same result is through the Cube Structure tab (the first one on the left).  If you click on the fact table where you want to format the measure values and then click on the Show Measures Grid icon in the toolbar ( ), you can display the fields in a grid as shown in the following figure.

To change the formatting of a field, select the field by clicking on it as shown in Total Profit above.  (You can also select multiple fields by holding the Ctrl key while clicking on other fields that you want to format the same way.)  Then right click on any of the selected field rows to display the menu of options.  Select Properties from this menu.

In the Properties panel, as shown below, find the property FormatString.  Click anywhere in the right column for this property to display the dropdown arrow.  Click the dropdown arrow to select one of the predefined formats.  (You can also define your own formatting.)  In this example, I selected the predefined format Currency which displays a leading dollar sign and two decimal places with commas separating the thousands.

When you are done, right click on the project in the Solution Explorer to display the project menu and select Deploy to redeploy the cube with your changes.

After the cube deploys, click on the Browser tab of the cube to display the pivot table.  If you have a measure field already selected in the pivot table, the change in formatting will not affect that field until you remove the field and then add it back again to the pivot table.  If all the stars align, you should see a pivot table like the following one with values formatted as you expected.

That’s all for this week since I’m at the Orlando SharePoint Saturday today.  Next time, I will look at how to created named queries to define custom tables for your cube.

C’ya then.

Good Luck – Bad Luck

At church services the other day, we heard a story that made me think.  You see there was this farmer with a small farm.  He had only one son and one horse to help him work the farm.  One day, the horse ran away.  His neighbors stopped by and tried to console him saying, ‘What bad luck.  We are so sorry.’

However, the farmer said, ‘Good Luck – Bad Luck.  How do you know?’

One morning several days later, the farmer had just left his house when he sees galloping toward him his horse leading a small herd of wild horses right to the farm.  When his neighbors heard that the farmer’s horse returned and brought along some wild horses they told the farmer, ‘What good luck.  Now you’ll have enough horses to work the farm without having to work your one horse so hard.’

The farmer said, ‘Good Luck – Bad Luck.  How do you know?’

Several days later, the farmer’s son tried to ride one of the wild horses and fell and broke his leg so he could not help with the farm work for several weeks.  The neighbors stopped by to say, ‘Sorry to hear about your son breaking his leg.  With the harvest time coming soon, that is really bad luck.’

The farmer said, ‘Good Luck – Bad Luck.  How do you know?’

A week later the local militia came through the valley and conscripted all of the young men into fighting a war against a neighboring country.  However, when they got to the farmer’s house, they saw that the farmer’s son had a broken leg.  They told the farmer, ‘We cannot take your son into our militia with his broken leg.  He will have to stay with you.’

Good Luck – Bad Luck.  How do you know?

Ok, the point of this story was simply that life is a complex interwoven matrix of events in which one event influences the next event, sometimes in surprisingly unexpected ways.  Over the last several years while the economy tanked, I have had several friends lose their jobs.  On the surface, most people would say that losing your job would definitely qualify as bad luck.  And in all honesty, it has been for some of them as they struggle to find new employment.  However, a larger number have found other positions, some very quickly after losing their old job.  In most cases these jobs are more rewarding both financially and from a career point of view than their old jobs.

Does this mean that you should purposely go out and try to lose your current job.  No, not at all.  However, sometimes when one door closes a window of opportunity opens.

We just got back from graduation ceremonies for our daughter who received her PhD in Pharmacology.  The good news is that she has a job for the next year working as a resident at the local Orlando VA.  The bad news is that the Orlando VA is building a new hospital to replace the current clinic and as of today, they have announced that there will be another delay in opening the facility.  What will happen when her residency ends if the new facility is not open?  Will they still be able to keep her on?  Will she have to look somewhere else?  It is way too early to tell.

If you read the stories of successful entrepreneurs, you may have heard stories about how they failed repeatedly before finally succeeding.  Actors and performers often work at their craft for years making barely enough money to live on until suddenly the stars align and they become an overnight success.  Sure, there are entrepreneurs who never succeed and the actors or performers who never make it out of the local town shows.  At the same time, there are those who never seemed to have suffered a downturn in their life and were successful from day one.  The one factor that seems to make a difference between those who eventually succeed and those that fail is that those who really love what they do and keep at it no matter what are in a better position to take advantage of good luck the next time it comes around.  Even when a spell of bad luck seems to follow them around, they continue to look for that next opportunity.

What good luck have you had recently?  Did bad luck precede it or follow it?  If you have had bad luck recently, I know it is hard to get through those times.  However, no matter how bad things seem to get, opportunity may be just around the corner as long as you keep looking for it.  What types of opportunities are looking for you to turn your bad luck into good luck?

C’ya next time.

BTW: I’ll be speaking at SharePoint Saturday in Orlando on June 9th ( and at IT Pro Camp in Jacksonville on June 16th (  Stop by to say, “Hello.”

Formatting Your SSAS Again

Last time we left our cube with a fusion of fonts and styles more to show how to apply change than to create something that looked good.  But the one thing that eluded our efforts was to format the measure as currency.  Therefore, this week I’m going to show you how to not only display the measure as currency, but how to define your own custom format.  But first need to get rid of all those changes from last time and get back to a basic cube like the one shown below:

Next, look at the tabs across the top of the central work area.  I will not go through the features in all these tabs, at least not now, but I do want to call your attention to two tabs; Perspectives and Translations.

While you can use these tabs for many other things, one of the things you can do is to change some of the properties of the measures. Click on either the Perspectives tab or the Translations tab.  It will not matter which tab you open because the measure properties will be the same in both. Notice on the left side a list of the Measure Groups and the Dimensions.  Within the Measure Groups, this example only has a single table, but there could be more.  Expand the table Fact Sales by clicking on the box to the left of the name if it displays a plus sign.  If the table has already been expanded, find and click on the field Sales Amount to select it.

Open the properties window if it is not already open (Click F4 or open the View menu and select Properties Window) and scroll through the properties until you find FormatString.  Initially this property is empty.

We can enter a format for a positive number and a negative number.  For example the format $#,##0.00 does several things:

–          It displays the number with a leading dollar sign

–          It adds commas as thousands separators

–          It displays the number with two decimal places.

–          It displays number less than 1 with a leading zero before the decimal place.

The ‘0’ is a placeholder and returns the number for that position or a zero.

The ‘#’ returns a number of there are numbers.  When used to the left of the decimal point, all digits are returned even if there is only a single ‘#’ in the format string.  If used to the right of the decimal point, the same number of digits are returned as there are ‘#’ symbols with the number being rounded up or down appropriately.

The ‘;’ is used to separate formats for positive, negative and zero values.  If the semi-colon does not appear, the format is applied to all numbers.  If the format contains a single semi-colon, the string to the left is used to format positive numbers and zero while the string to the right formats negative numbers.  If two semi-colons appear, the expression to the left of the first semi-colon formats positive numbers.  The format between the two semi-colons formats negative numbers, and the format to the right of the second semi-colon formats zero values.

There are additional rules for dates and times that allow you to display different date formats as well as things like month names, day names, and more.

For negative values, the format: ($#,##0.00) does all of the above, but encloses negative values in parentheses.  These two formats are separated with a semi-colon.  For our purposes, zero values can be formatted like positive numbers.

If you click on the Browser tab, you might think that you still did not change the format on the previous page or that you did not click save or some such thing.  Actually, you are close.  You need to rebuild and deploy the cube.  Once you do this, you can open the Browser tab and build your cube if you have not done so before or if you already had a cube you may need to remove the current measure if your format changes apply to the currently displayed measure and add that measure back in. You should now see the measure formatted as currency as shown in this figure.

Well, that’s all for today because as I mentioned earlier this week, it is graduation week.  C’ya next time.