• Post Categories

  • Browse Blogs

  • Blog Stats

    • 584,901 hits
  • Syndications

    SQLServerPedia Contributor

Afterthoughts: IT Pro Camp Orlando 2012

This past Saturday January 21, 2012 I had the opportunity to speak at my first IT Pro Camp in Orlando, FL. This event was put together by Blain Barton ( Blog | LinkedIn | Twitter ), Microsoft Senior IT Pro Evangelist and Chad Miller ( Blog | LinkedIn | Twitter ), Microsfot MVP and Senior Manager of Database Administration at Raymond James Financial.

Event

The event went very smooth and had a good turnaout. Food was amazing. They reallly outdid themselves with good old southern BBQ.

Presentation

I presented a session titled Introduction to Microsoft Business Intelligence (Slides), focusing on the basic concepts of BI and Datawarehousing, the Microsoft toolset and my Top 5 Rules of Wisdom for succesful BI. I had a great turnaout, about 25 attendees. All of the attendees were very engaged and asked excellent questions. I enjoyed presenting to the Orlando crowd.

Picture credits: Russel Faustino. See more pictures of the event here.

Other IT Pro Camps are being added. Check the IT Pro Camp website here to keep up to date.

Click on the picture below to download my Intorduction to Business Intelligence powerpoint presentation.

 

 

Using Unary Operators to control Analysis Services hierarchy aggregations

Analysis Services hierarchy aggregations can be easily controlled using unary operators. For example, in accounting there are GL accounts that are grouped in major GL account groups and used in different financial statements like Profit & Loss Statement, Income Statement, Trial Balance, and Balance Sheet. These GL accounts may affect the balances of the major GL account groups and  financial documents differently, adding  or subtracting to the balance.

A simpler example, might involve sales quotas for a Sales Department. For example, in some organizations the Sales Department is broken down into sales teams with salespeople assigned to those teams. The overall Sales Department quota is broken down into smaller quotas among these sales teams and the sales team’s quota is then broken down into quotas assigned to the team members. In some organizations, Sales Managers often assign these quotas evenly throughout the sales teams and sales team members. Others, in order to guarantee a fat bonus check decide to raise the bar and assign sales quotas that are higher to the overall department’s sales quota. Figure 1 below shows an organizational structure chart of our sample Sales Department with sales quotas that don’t sum up evenly.

Figure 1. Sales Department organizational structure chart and sales quotas

As can be seen in Figure 1, the total Sales Department quota is $150,000. The Sales Manager in this case decided to push their teams harder and assigned a sales quota of $60,000 to each team. In a perfect scenario, if all sales teams meet their sales quota, the total sales for the Sales Department would be $180,000, which is $30,000 more than the department’s sales quota. Accordingly, each team, in order to impress their Sales Manager and win a free meal and margaritas at the local Mexican joint, decided to exceed the team’s sales quota by assigning higher personal sale quotas.

In our BI solution, we are required to create an Analysis Services cube in which the Sales Manager can track these sales quotas at each level of the Sales Department organizational chart as seen in Figure1. But, we cannot simply sum up the sales quotas, because the totals would not match up. We could approach these several ways, but our requirement is to replicate Figure 1 with a single “SalesQuota” measure with no MDX or additional measures involved.

The Solution

In order to achieve the requirement imposed above we can make use of a special attribute property in Analysis Services called UnaryOperatorColumn to control how level members of our hierarchy contribute to the aggregated value of the level parent.

In a regular scenario, the parent’s sales quota at each level would be equal to the sum of its children’s sales quota. That would have been the case if the Sales Manager in our example had decided to distribute the overall Sales Department quota equally among each sales team and the individual sales team members would have done the same for their personal sales quota. In other words, each sales team’s quota would have been $50,000 each ($150,000 / 3). For Sales Team A, for example, each sales person’s quota would have been $16,66.67 each ($50,000 / 3).

But in our scenario, the parent’s sales quota at each level should not be equal to the sum of its children’s sales quota. The only solution then, is to create an “artificial” child at each level that holds the parent’s sales quota and specify this “artificial” child to be the only child contributing to the parent’s total. This is possible through the UnaryOperatorColumn attribute property in the Dimension designer in BIDS. In Figure 2 you can see the available Unary Operators that can be used in this property along with the resulting behavior as described in Books On Line (http://msdn.microsoft.com/en-us/library/ms175417.aspx).

Figure 2. Unary Operators.

The UnaryOperatorColumn attribute property value, as the name implies, is a pointer to a table column that holds one of the Unary Operators listed in Figure 2. For our Sales Department quotas example, we would need a column that holds the unary operator for each level member in our Sales Department dimension. The Sales Department dimension source table would look as shown in Figure 3.

Figure 3. Sales Department dimension source table

Notice that this table is a naturalized Parent Child table. Also, notice that an “artificial” child was created with the same name as its parent. This “artificial” child is the child that will hold the parent’s sales quota value and is the only value that will be used in the parent aggregation. The way we control this aggregation, is by assigning to this artificial child the ‘”+” Unary Operator and the rest of the children the “~” Unary Operator.

At the lower level in our hierarchy all the way to the right in Figure 3, each sales person is assigned a “~” as its Unary Operator  in the SalesPersonUnaryOperatorColumn and only the “artificial” child receives a “+” as its Unary Operator. The same applies for the Sales Team level in our hierarchy, only the “artificial” child is assigned the “+” Unary Operator.

The FactSalesQuota table that holds the sales quotas would look as shown in Figure 4:

Figure 4 FactSalesQuota fact table

The Analysis Services project

Once we have defined our underlying table structure, we can take a look at the Analysis Services project. Figure 5 shows the basic project definition with the Sales Department Dimension and Sales Quota measure.

Figure 5. Analysis Services project definition

The Sales Quota Measure

The sales quota measure is a straightforward column based measure. It is based on the Fact Sales Quota table and is a simple SUM aggregation. Figure 6 shows the sales quota measure definition.

Figure 6. Sales Quota measure

The Sales Department Dimension

The Sales Department dimension is a simple dimension with the necessary attributes needed to design a drilldown hierarchy. Figure 7 and 8 show the Sales Department dimension definition, user hierarchy and attribute relationships.

Figure 7 Sales Department definition

Figure 8 Sales Department attribute relationships

A very important step in any user hierarchy definition is to specify the key columns at each level. In this case, the only attribute that needs a composite key column definition is the Sales Team attribute. The key column definition is shown in Figure 9.

Figure 9 Sales Team attribute key columns

The deployed cube with the Sales Department definition provided so far would look as shown in Figure 10.

Figure 10. Deployed cube

Notice that the “artificial” children show up and that the children’s sales quotas are being added to the parent’s aggregated amount. To fix the aggregation issue we need to define the UnaryOperatorColumn attribute properties for the children level members. We can hide the “artificial” children by changing the HideMemberIf user hierarchy level property to Parent.

Figure 11 and 12 show the values for the UnaryOperatorColumn property for the Sales Person and Sales Team attributes.

Figure 11. Sales Person UnaryOperatorColumn value

Figure 12. Sales Team UnaryOperatorColumn value

Figure 13 and 14 show the user hierarchy level property HideMemberIf

Figure 13 Sales Person user hierarchy level HideMemberIf property

Figure 14 Sales Team user hierarchy level HideMemberIf property

The Results

Once these properties have been changed the resulting deployed cube should look exactly as required. Figure 15 shows the exact aggregation behavior as in Figure 1.

Figure 15 Final results

Conclusion and Considerations

The solution presented above provides the exact results as dictated by our requirements and is meant as a means to exemplify and understand how unary operator can be used to control aggregation behaviors in user hierarchies.

There are other options to provide the same results, but this solution requires no calculated members or MDX at all. While this solution works, you need to keep in mind that there are some performance consideration regarding unary operators and parent child hierarchies. The key is always to TEST! TEST! TEST!

Sample Files

You can download the project files used in this post here.

Afterthoughts: SQL Saturday #79 South Florida 2011

This past Saturday August 13th I presented two sessions at SQL Saturday #79 hosted by the South Florida SQL Server User Group (SFSSUG) (Webpage | LinkedIn| Twitter). The first presentation was at 11:00am titled “So, What is BI Semantic Model and why should you care?” and the second one was at 3:00pm titled “Designing  and Optimizing Analysis Services Hierarchies.” I had an amazing time chatting with some old friends that made it to South Florida all the way from Charlotte, NC, Atlanta, GA and  several other Florida cities like Jacksonville, Pensacola, Orlando, and Tampa.

The Driving Adventure

It was an adventure to get there as I decided to make the 4-hour drive from Tampa, FL (about 500 miles round-trip) early in the morning. My original plan was to start my drive around 4:30am but due to being jet lagged from my midnight flight the day before, I woke up until I snoozed the alarm for the 3rd time around 6:30am. That gave me a 30 minute window to get there on time for my 11am session.

The Event

The event was well put together by it’s organizers and volunteers. The food was great and included a mix of Mexican goodies. The Nova Southeastern University Carl DeSantis Building was a great venue. I really liked the central atrium where everybody converged in between sessions and was more than ideal for having lunch and event wrap-up. Great choice of venue & food!

The Attendees

One Mountain Dew + a 5-hour energy bottle and a speeding ticket later, I managed to get there with 20 minutes to spare. My first session titled What is BI Semantic Model and why should you care? started on time and was packed. It was literally standing room only. I had a great time with the audience. They were very engaged and asked both technical and philosophical questions. A couple speakers and friends attended such as my “tweep” (tweeter peep) Phillip Rosen (LinkedIn | Twitter) whom I got to know him in person for the first time. Also, the amazing Data Mining expert and newly minted Microsoft MVP Mark Tabladillo, Ph.D. (Blog | LinkedIn | Twitter) with whom I exchanged a couple of good jokes and funny moments during the presentation. The most memorable being the conception of the term “Bingle” which is the abbreviation of Bing and Google.

Sanjay Soni, Microsoft TSP

One of the attendees was Sanjay Soni (LinkedIn), Microsoft’s new Data and BI Platform Technology Solution Professional (TSP) for the Greater Southeast District (Florida). Sanjay introduced himself after the presentation and was very kind to provide very positive feedback for my BISM presentation. We  spent some time chatting about opportunities to co-present and network some more. I’m very excited to assist Sanjay  as much as I can in his new role in Florida. I encourage for you to network with Sanjay as he is a very friendly and approachable guy. You will see him very soon at a user group or event near you!

At the end of my session several other attendees came up to introduce themselves personally. There were so many I almost ran out of business cards. I appreciate when attendees take a moment to chat with me after my sessions and get the opportunity to listen to their feedback and kind words. I feel truly honored to have been able to present to such a great crowd.

So, What is BI Semantic Model and why should you care?

In this presentation, I did an overview of the new Business Intelligence Semantic Model (BISM) and the new “flavor” of the Analysis Services Engine that Microsoft will release with SQL Server Codename Denali. I explained about the new focus on “Personal BI” with the PowerPivot add-in for Excel, “Workgroup BI” with PowerPivot for SharePoint and “Enterprise BI” with Analysis Services UDM and Tabular mode. I showcased the free PowerPivot add-in for Excel 2010 and later demonstrated how easily a PowerPivot workbook can now be turned into an enterprise grade Tabular database using the new Business Intelligence Development Studio in SQL Server Codename Denali and how simple it is to deploy them.

You can download the slides here or by clicking on the picture above.

Designing  and Optimizing Analysis Services Hierarchies

 

On my second presentation Designing and Optimizing Analysis Services Hierarchies I also had a great time interacting with the attendees. The session was designed as an intermediate session but changed it a little bit to more beginner level from the results of my skillset level audience survey. I had great questions and participation from the attendees.

You can download the slides here or by clicking the picture above.

Thanks to everyone who attended my sessions and to the organizers and volunteers that made SQL Saturday #79 a success!

SSAS errors: DefaultMember(Measures,Measures) (1, 1) The ‘[xxx]’ member was not found in the cube when the string, [Measures].[xxx], was parsed.

When attempting to browse an Analysis Services cube you may be presented with the following error message:

DefaultMember(Measures,Measures) (1, 1) The ‘[xxx]’ member was not found in the cube when the string, [Measures].[xxx], was parsed.

The error message is self-explanatory. There is a Default Member being referenced and was not found. But, pinpointing where this “DefaultMember” is being referenced may me a bit challenging.

The Root Cause

The root cause of this issue is quite simple: a deleted or renamed measure is still being referenced by its original name. Some of the areas where this deleted or renamed default measure could still be referenced by its original name include:

  1. Cube DefaultMeasure property
  2. Perspective DefaultMeasure property
  3. Calculations

The fix

The fix may involve adding the measure back or renaming the measure to its original name or digging through several areas of the cube where the deleted or renamed measure is still being referenced by its original name. Here are your options:

1. Add or rename the measure back to its original name.
This may not be the desired option, but it could be the easiest and quickest way to fix the issue.

2. Modify each Perspective’s DefaultMeausure property
Unfortunately, deleting or renaming a measure does not “trickle-down” where it is being referenced in a perspective. The original measure’s name will remain.

3. Change the cube DefaulMeasure property to the new measure name.
Similar to Perspectives, deleting or renaming a measure does not “trickle-down” its new name to the cube’s DefaultMeasure property. You will need to select a new measure in the DefaultMeasure property.

4. Modify the cube XML code
This option may be a little daunting to some, but in the end it is the quickest way to make sure the original measure’s name is replaced by the new measure’s name. To do this, in Solution Explorer right click on the Cube’s name and select View Code. The XMLA definition will open. At this point, press CTRL+H or click on Edit->Find and Replace->Quick Replace.The Quick Replace window will open. Type the original measure’s name in the “Find what” textbox and type the new measure’s name in the “Replace with:” textbox.

SSAS errors: Logon failure / Cube process failure

Often I encounter this error myself when trying to process an SSAS cube at a client’s site or get asked by customers and friends how to solve this error. Although it may seem really obvious,  not everyone may understand “what” is rejecting their credentials or “what” are they authenticating against.

Simple. The data source.

So, what credentials are being passed through to your data source?

At this point you may answer: “my Windows credentials” or “credentials of the current user.” This is not always the case.

Impersonation
Your Windows credentials are used to create the connection strings to your data source at design-time. During processing and run-time, Analysis Services needs an account to be able to reach the data source. Keep in mind that you will not always process Analysis Services cubes manually within BIDS or SSMS. Once the Analysis Services cube is initially deployed, in most environments you will have scheduled SSIS packages that will re-process the cubes every night. In order for Analysis Services to be able to read the data from your data source, it needs some form of credentials to pass through. This is also known as impersonation.

In Analysis Services 2008-R2, there are several impersonation options:

  • Use a specific Windows username and password
  • Use the service account
  • Use the credentials of the current user
  • Inherit (or default)

To learn more about each of these options you can read MSDN Books Online at: http://msdn.microsoft.com/en-us/library/ms187597.aspx

In a nutshell, the impersonation account specified needs to have read access to your data source. For example, if your data source resides in SQL Server, the account specified as your impersonation account needs to  be mapped to your database with db_datareader role membership. It is best practice to specify a dedicated account that has limited read-only access to the database. Avoid using an account with elevated privileges such as sysadmin or db_owner.

The recommended impersonation setting is to specify a Windows username and password. If your server is joined to a domain, you will have to specify DOMAIN\Username in the User name textbox and the associated password in the Password textbox as seen below:

If you specify “Use the service account”,  it will use the account used to start the Analysis Services instance to authenticate against your data source. In this case, the service account will need read access to the data source. Keep in mind that the system service accounts Network Service, Local System and Local Service will not have access to your data source if the data source is on a different server. Typically, you will use a Windows domain account that has Log on as Service privilege in Active Directory Group Policy as your service account to start Analysis Services.

Inherit (SQL Server 2008/2008-R2, Default in SQL Server 2005) uses the impersonation mode  and credentials set in the Data Source Impersonation Info database property. To view or modify this database property, open SSMS and connect to the Analysis Services instance, expand the databases folder and right click on the database you are interested and select properties. You will now see the database properties window and the impersonation mode selected. To change the impersonation mode, click on the elipsis inside of the Data Source Impersonation Info textbox highlighted below:

Per MSDN Books Online, by default the Data Source Impersonation Info database property is set to Use the service account. This means that if the Inherit impersonation option is selected in your BIDS solution, it will use whatever is specified in the Data Source Impersonation Info database property. If this database property was set to Default as shown on the image above, then it will use the Default impersonation mode in which it will use the impersonation method that is most appropriate for the context in which impersonation is used. For more details read http://technet.microsoft.com/en-us/library/ms126693.aspx.

In summary, if you are getting a logon failure when processing an SSAS cube, check the impersonation settings of your data source. As a best practice, choose “Use specific username and password” as Impersonation option and use a dedicated Windows domain account.

The dedicated domain account should be configured at a minimmum as follows:
– Regular domain user or part of a restricted domain user group
– No password expiration policy should be applied. If not, password may expire and processing will fail at some point.
– Account should be mapped to a SQL Server database login with db_datareader role membership.

If the dedicated domain account will be used as the service account, make sure it is part of the “Logon as a service” AD Group Policy.

%d bloggers like this: