Dave Rodabaugh's Cube

Deleted All Logins to SQL Server 2005 and Lost sa Password: You Did WHAT? And BizTalk 2006 Is Using It?

drodabaugh — Fri, 11 Apr 2008 13:46:00 GMT

It's been a while since I blogged but I had a circumstance yesterday that I thought was noteworthy. Recently my employer cut our staff to about 1/4 of what it was nine months ago, so all of us in "management" are now hands-on. I've inherited DBA work (since we cut the DBA), something of which I've not done much in the past eight years during a BI career. I have always appreciated what competent DBA's do for those of us in BI development. I'm sure I'll appreciate it more as time progresses.

As the title suggests, I committed an egregious error. I was working on a dev server and needed to reload a database which hasn't yet gone to production. I inherited the database and the application from a consultant (yup, cut him too) and it hasn't been fully loaded. As part of the work, I thought I'd build an "initial load" procedure and add it to the documentation. One of the methods for initialization is to create the database with a script, so I had SSMS write a script. I told it to script the database logins. Did I read the script before punching the go-giddy button? No. In a few seconds, the script errored out saying that I didn't have permission to create logins. Huh? Of course I do! I'm a sysadmin. A few more minutes of sleuthing revealed the disaster: I had deleted every single login in that server. Every last one, including myself. I had even whacked Builtin/administrators.

Shortly thereafter I realized we didn't have the sa password. I had completely locked myself out of my own server. What to do? I considered a full rebuild of the server, but there were 17 user-defined database on the server. Additionally, the majority of those databases were for a dev instance of BizTalk 2006, and while I'm not an experienced DBA, I worked with our DBA during initial installation of BizTalk 2006 and knew that it wasn't backed up or restored conventionally. Plus, there was a dev SharePoint installation using the SQL Server as well and I knew even less about the restore procedures for that. I was felling a little sick at that point.

A quick Web search revealed that Microsoft has left a trap door in SQL Server 2005 for this problem. You may find a simple and correct procedure for resetting a lost sa password when you cannot otherwise get access to the server, in the following link: http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx. I did exactly what Raul Garcia told me to do, and it worked! Restart the server in maintenance mode and then add a user to the the sysadmin role with sp_addsrvrolemember.

Next, I ran only the portions of the offending script which created logins. But was I done? Hardly. Inspection of the script (in which I asked for descriptive headers) told me that all SQL Server logins would be recreated with a random password and automatically disabled. I only had four of those, but I had to find the application passwords and make them match, and then enable the accounts. I also noticed that the associations between those logins and their database users seemed to be incorrect. I reset those with SSMS and they seemed to be working fine.

Next, I noticed that none of my backup jobs were running. SQL Agent could not access SQL Server. I use the same domain account to start both the SQL Server service and SQL Agent, but I had wiped out the permissions of that domain account. I fixed the problem by adding a login for the service account to SQL Server and gave it sa rights. I'm sure this is not the best possible way I could have solved that problem, but I can go back and fix it later. The highest priority of any DBA is to safeguard the company's data and this was only a dev server.

Finally, the SQL Server log showed that the BizTalk accounts could not log on to SQL Server. The message shown in the log was something like this:

Login failed for user '<domain>\<domain user>'. [CLIENT: 10.1.2.17]

Error: 18456, Severity: 14, State: 16

State 16 seems to indicate that a login has no permissions in a database which it is querying. Running sp_change_users_login with the "Report" option showed nothing. All of the BizTalk users had proper associations in their databases, just as SSMS said they did. Furthermore, we got the Windows password for one of the BizTalk Windows accounts and logged in to the server. We were able to connect to all the proper databases and query tables, just the permissions said we would. In other words, BizTalk was having no real problem connecting to SQL Server and querying its databases.

So, what kept causing the problem? We noted that the errors only occurred after restarting the SQL Server service, and then only one time. A ha! Is it possible that BizTalk was trying to connect to SQL Server and access its databases before the restart recovery is complete? Yes! Had I been more observant, I would have seen this in the log file. We put this to the test by stopping the BizTalk services on the BizTalk dev server and restarting the SQL Server service. Sure enough, the errors disappeared. It was nothing more than an order of operations issue. BizTalk tries to connect very quickly when it loses its SQL Server connection. It was trying to access a database before recovery was complete.

So that's the saga. Here's what we learned:

Don't let Dave be your DBA on production servers if he can't stop destroying dev servers.
Always have current backups. We did, so I knew I could get back if the method described herein didn't work.
BizTalk is a very complicated system. You'd better practice restoring it because if you really have to do it, it could take hours just to know the process for recovery.
Always record your sa passwords and securely store them.
Read database creation scripts BEFORE you execute them. Five minutes of reading may save four hours' work.
Microsoft has provided a way to create a new admin account on SQL Server even if you are boneheaded enough to completely sever access to SQL Server.

Drillthrough failed because the coordinate identified by the SELECT clause is out of range: Cause and Solution

drodabaugh — Thu, 26 Jul 2007 18:10:00 GMT

Drillthrough failed because the coordinate identified by the SELECT clause is out of range.

Ever gotten message shown above when using DRILLTHROUGH in SSAS? I prepared the Integrating Analysis Services 2005 and Reporting Services 2005 with DRILLTHROUGH series after encountering significant problems with drillthrough on a real project that absolutely required drillthrough. Solving the drillthrough mystery was not the easiest problem ever solved, and just when we thought every problem was conquered, another one struck.

My teammate encountered this error while authoring a report that quried a sample SSAS database I built with data only for October 2006. (The database was for a client.) Despite only one month if fact data, the Date dimension had several years’ members. He wrote a multidimensional selector component which extended Reporting Services to navigate a multidimensional hierarchy, and he was testing this with the report using our newfound drillthrough solution.

His initial query looked like this:

DRILLTHROUGH

SELECT

NON EMPTY [Measures].[SomeMember] ON COLUMNS

FROM

[SomeCube]

WHERE

[Date].[Calendar].[Month].[Oct 2006]

He then parameterized the query to insert any month into the WHERE clause after the user picked a month in the selector component. While testing, everything worked as long as he picked October 2006, which actually had data. As soon as he picked any month other than October 2006, a strange error occurred, shown below:

How can a coordinate identified by the SELECT clause of an MDX query be out of range? By definition, SSAS requires one coordinate from every hierarchy, andd if one is not supplied by the query then SSAS uses default members. It seems impossible to "identify" a coordinate that is "out of range."

I conjured two possible causes.

In SSRS, a DMX query is used to make drillthrough work. Perhaps this was an error related to DMX.
The error had nothing to do with MDX or DMX at all. Since drillthrough queries are not answered as MDX queries, perhaps this error was actually thrown by the code executing the DRILLTHROUGH statement.

I had no way of directly testing the first possibility. But I was able to triangulate to the second possibility because the MDX query worked perfectly when the DRILLTHROUGH keyword was removed. I also knew that the query could only return one cell because by definition, drillthrough acts upon a single cell. For Oct 2006, the returned cell had a numerical value because the dates descended from Oct 2006 had data. For Sep 2006, the returned cell had a NULL numerical value because none of the dates descended from Sep 2006 had data. Said in the vernacular, the cell was empty.

To illustrate this, I’ve modified Adventure Works DW by deleting the Reseller_Sales_2003 partition in the Reseller Sales measure group. The following query shows that there is now no data for [Ship Date].[Calendar].[Calendar Year].&[2003].

SELECT

Measures.[Reseller Sales Amount] ON COLUMNS,

[Ship Date].[Calendar].[Calendar Year].Members ON ROWS

FROM

[Adventure Works]

That query returns the following cellset.m Note that CY 2003 is now empty, a result of deleting the the partition with CY 2003's data.

The following query is analogous to the real-world query which produced the error, but without the DRILLTHROUGH keyword.

SELECT

NON EMPTY Measures.[Reseller Sales Amount] ON COLUMNS

FROM

[Adventure Works]

WHERE

[Ship Date].[Calendar].[Calendar Year].&[2003]

That query returned the following cellset.

Two interesting observations emerge from the answer returned to Microsoft SQL Server Management Studio.

The Results tab is completely blank. (Not shown in the screenshot above.)
The Messages tab, however, displays the information pictured below.

This message indicates that the cell set consists of 1 row and 0 columns. To be honest, I’m not sure what this means, but I know it is significant because things change when the NON EMPTY keyword is removed.

First, the Results tab is no longer blank, as shown below.

Second, the Messages tab has a different message than before.

So, comparing the two, when the NON EMPTY keyword is present, no cells are returned, and "1 rows and 0 columns" is the technical way of describing this. When the NON EMPTY keyword is removed, one empty cell is returned (as expected) and "2 rows and 1 columns" is the technical way to say this.

At this point I had a major clue. This must be a problem when DRILLTHROUGH was combined with NON EMPTY. Removing NON EMPTY and putting DRILLTHROUGH back in the query returned the following result.

And this was the Messages tab.

Bingo! The error at the beginning of this article is caused when drillthrough executes on a cellset that does not exist. The following two items are now evident.

DRILLTHROUGH will not work when the cell set consists of zero cells, which would be produced by a NON EMPTY query against a cell which has no data. This is perfectly sensible since drillthrough acts upon a single cell. Naturally, when there is no cell, there can be no drillthrough.
DRILLTHROUGH will work when the cell set consists of one empty cell. In such a case, DRILLTHROUGH returns no rowset, which is the expected case for an empty cell since it has no value. There is no fact row for this cell; DRILLTHROUGH should return nothing.

In other words, DRILLTHROUGH must have a real cell set upon which to act, even if that cell set is a lone empty cell. This gives rise to an important rule for drillthrough queries.

Do not use the NON EMPTY keyword in a DRILTHROUGH query.

IMPORTANT NOTE: This rule applies ONLY to queries using the DRILLTHROUGH keyword. As noted in my four parter on drillthrough, it is possible to emulate a drillthrough query by using a crossjoin MDX query. The NON EMPTY prohibition does NOT apply to the crossjoin approach; indeed, NON EMPTY may be desired in certain circumstances.

Integrating Analysis Services 2005 and Reporting Services 2005 with DRILLTHROUGH: Part IV of IV

drodabaugh — Thu, 26 Jul 2007 18:02:00 GMT

CONCLUSION

Drillthrough is changed substantially in SSAS because Microsoft rearchitected it. SSAS will no longer prepare an SQL statement to pass through the data provider to the data source, and pass along a rowset returned by the data source. All drillthrough queries must be answered from SSAS, using the information present in UDM.

The goal of drillthrough in SSAS should be the same as it was for MSAS: return one row for each row in the fact table, regardless of how many detail cells are underneath the cell upon which drillthrough occurs. (NOTE: I am aware that drillthrough is actually more sophisticated than this and can return rowsets at nearly any grain, but if it will not produce the fact row set, then flexibility is limited.) Since SSAS now answers all drillthrough queries directly, savvy DW/BI designers must adhere to several prominent design elements, most notably enforcement of fact row uniqueness. The goal is to produce a one-to-one relationship between fact rows and detail cube cells. One cell should have one and only one fact row.

While SSAS will work fine without unique fact rows for aggregate and analytical queries, it simply cannot produce the most granular drillthrough query without fact row uniqueness. Decide whether drillthrough requirements require fact row uniqueness before ETL and cube builds, because it may be very difficult to add once a cube is in production.

Add a dimension with an attribute to hold the column or columns that make fact rows unique. This attribute will have as many members as rows in the fact table, so it could be very large. Take steps to control it. Techniques include making it a ROLAP dimension and perhaps hiding it if it is useful only to drillthrough queries. This dimension ties into a measure group by using the fact dimension relationship. CAUTION: ROLAP dimensions must be processed the first time they are queried, which could result in extraordinarily long query times. Cnosider MOLAP first.

Once the one-to-one relationship between fact rows and detail cells is enforced, drillthrough queries can be written with two divergent techniques. The first is to use the DRILLTHROUGH keyword in an MDX query. The DRILLTHROUGH keyword requires an MDX query that returns a single cell, and then returns one row for each detail cell under the single cell. The second is to use an MDX statement to emulate drillthrough and produce a cell set. This is done with an inordinate number of crossjoins, but it works.

Even though these two query options produce the same answer, choose one or the other carefully based on performance. The query using the DRILLTHROUGH keyword does not execute as an MDX query and basic testing shows it is faster than its crossjoin counterpart. Indeed, be very wary of the crossjoin alternative. Crossjoin queries slow down considerably as the number of members being crossed increased, and since the fact dimension contains one member for each fact row, the crossjoin approach can become unwieldly very quickly. Remember, the point of this series is to integrate SSRS with SSAS using drillthrough, and drillthrough returns a nice, flattened rowset convenient for SSRS.

Integrating Reporting Services 2005 and Analysis Services 2005 with DRILLTHROUGH: Part III of IV

drodabaugh — Thu, 26 Jul 2007 18:02:00 GMT

I have been most delinquent in finishing this multi-part series on drillthrough with SSAS and SSRS. I wrote all the parts of this series at one time in December, 2006, but only posted two. Four weeks ago a client asked about specific problems with drillthrough, and I had all the answers he needed in the total blog series, including pieces that I hadn't published. I though that perhaps I should finish posting the series.

Parts I and II of this series demonstrated the differences in drillthrough from MSAS to SSAS, and listed design implications for the entire DW/BI manufacturing pipeline. This section discusses ways to write effective drillthrough queries.

The introduction of this series stated that web research did not provide a single, conclusive way to handle drillthrough in SSAS. As noted, the first hint of trouble was the inability of SSRS to use DRILLTHROUGH in an MDX query. SSRS returned the following error: "Failed to parse the query to detect if it is MDX or DMX. (MDXQueryGenerator)".

http://connect.microsoft.com/SQLServer/feecback/ViewFeedback.aspx?FeedbackID=126175 shows the bug and Microsoft’s response, which is that drillthrough queries are not supported in the SSAS integration with SSRS because MDX can effectively duplicate the fact rowset. Part I of this series demonstrated empirically that this statement is not always true. Part II outlined the conditions in which the statement is true, and how those conditions influence design.

A Web search reveals two workarounds.

Use the OLE DB for OLAP 9.0 provider. http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/85a65872f286686b
Darren Gosbel posted a second workaround, using a DMX query with the DRILLTHROUGH keyword. This works surprisingly well. http://connect.microsoft.com/SQLServer/feedback/Workaround.aspx?FeedbackID=126175

So, apparently there are three ways to write drillthrough when integrating SSRS with SSAS.

Use the OLE DB for OLAP 9.0 provider instead of a native provider. This approach is undesirable if the source is SQL Server, since the native provider probably performs better OLE DB. It may also affect parameterization in SSRS, and it makes little sense to use a query that limits reporting functionality. This is "the old way" complete with the drawbacks of "the old way."
Use a DMX query with the DRILLTHROUGH keyword. As noted, this works quite well but it requires any new report writer to know the workaround.
Write a crossjoin MDX query that returns the correct rowset.

Option (1) is discarded. That leaves options (2) and (3). The following examples illustrate how they work.

Example for Option (2): DRILLTHOUGH with an MDX query.

The following example illustrates how the DRILLTHROUGH keyword works with an MDX query (and also with a DMX query in SSRS). Like Parts I and II, this example uses the Adventure Works DW cube and the Reseller Sales measure group. (All of these queries may be run in SQL Server Management Studio.)

The following is the most basic drillthrough MDX query:

DRILLTHROUGH MAXROWS 100000

SELECT

[Measures].[Reseller Sales Amount] ON COLUMNS

FROM

[Adventure Works]

It returns the following columns:

[Reseller Sales].[Reseller Sales Amount]

[Reseller Sales].[Reseller Order Quantity]

[Reseller Sales].[Reseller Extended Amount]

[Reseller Sales].[Reseller Tax Amount][Reseller Sales].[Reseller Freight Cost]

[Reseller Sales].[Discount Amount]

[Reseller Sales].[Reseller Unit Price]

[Reseller Sales].[Unit Price Discount Percent]

[Reseller Sales].[Reseller Total Product Cost]

[Reseller Sales].[Reseller Standard Product]

[$Reseller].[Reseller]

[$Promotion].[Promotion]

[$Employee].[Employee]

[$Delivery Date].[Date]

[$Sales Territory].[Sales Territory Region]

[$Reseller Sales Order Details].[Carrier Tracking Number]

[$Reseller Sales Order Details].[Customer PO Number]

[$Reseller Sales Order Details].[Sales Order Number]

[$Reseller Sales Order Details].[Sales Order Line]

[$Product].[Product]

[$Ship Date].[Date]

[$Source Currency].[Source Currency Code]

[$Date].[Date]

[$Geography].[City]

[$Geography].[State-Province]

[$Geography].[Country]

[$Destination Currency].[Destination Currency Code]

Several observations emerge from the results set:

The query returns 80,655 rows. This is the exact number of rows in the fact table underneath the Reseller Sales measure group, and is the correct answer. Since SSAS answers all drillthrough queries and does not pass the request on to the data source, the one-to-one relationship between detail cell and fact row is evidenced.
Dimension columns are prefixed with a "$". Measure columns have no such prefix.
Ten measures are returned by the query. These are the ten measures with their Visible property set to True. Those measures with their Visible property set to False are not returned by default. They can be returned if the optional RETURN keyword is used and the columns are explicitly listed therein.
Both the Reseller Sales Order Details and Geography dimension returned non-key attributes. This is because their key attributes have their AttributeHierarchyVisible property set to False.
All other dimensions have key attributes with AttributeHierarchyVisible property set to True. These dimensions return only their key attribute.

Additional attributes and hidden measures may be returned with this query if the RETURN keyword is used. However, this will require all return columns to be explicitly listed in the RETURN clause. RETURN also permits column aliasing, which may particularly useful to comply with organizational standards for SSRS data set column names.

Additional considerations apply.

Of course, a WHERE clause may be added to this query as required.
It does not matter which measure is called in AXIS (0); the query always returns the same set of columns (absent the RETURN clause).
The query sends CPU utilization for msmdsrv.exe to maximum capacity. This test was conducted on a laptop with a P4 HT, and only one of the virtual CPU’s answered the query.
There appears to be some caching effect. The first query took about four minutes to run. Repeating the query reduced completion time to 10’s of seconds.
DRILLTHROUGH will not return calculated members. Anything returned by DRILLTHROUGH must be tied directly to an attribute or measure, which in turn relies directly upon a DSV column.

Example for Option (3): Emulating DRILLTHROUGH with an MDX Query.

The following example illustrates how MDX may be used to emulate a DRILLTHROUGH query. This example assumes that the proper design criteria have been met to emulate drillthrough with MDX. (See part II of this series.) This example uses the Adventure Works DW cube and the Reseller Sales measure group. (All of these queries may be run in SQL Server Management Studio.)

Deepak Puri correctly assessed the lone requirement to emulate a drillthrough query with MDX when he said, "…you need to include the fact dimension key attribute, so that each fact table row is returned, regardless of how many contribute to each cell." Any other attribute or measure in the query may add content, but it will not change the number of rows returned by the query.

The following query returns 60,855 rows from Reseller Sales in Adventure Works DW.

SELECT

[Measures].Members ON COLUMNS,

NON EMPTY

[Reseller Sales Order Details].[Reseller Sales Orders].[Order Line] ON ROWS

FROM

[Adventure Works]

Observations about this query include the following:

The query returns 60,855 rows. This is the exact number of fact rows and provides evidence that MDX queries can be used to emulate drillthrough so long as the proper design techniques are employed.
This query returned all physical measures in the cube. Since not all measures originate with the Reseller Sales measure group, not all measures can be sliced by the Reseller Sales Order Details dimension. The values returned by those measures will be for the All member in that dimension and will have the same large value in every row. Be sure to explicitly list the measures to be returned in the query.
This query returned no calculated members.
A WHERE clause may be added as required.
Caching effect is large. The first time the query was run execution time was 10:12. The second time the query was run execution time was 3:06.
Performance conditions were identical to the previous example since the same machine was used, in the same configuration.

Additional attributes may be added as desired to the previous query.

Is it possible to prove that the only important part of drillthrough emulation is to include the key attribute of the fact dimension? Indeed it is. Suppose the previous query is altered so that [Reseller Sales Order Details].[Reseller Sales Orders].[Order Line] is replaced by [Date].[Date].[Date] to form the query shown below.

SELECT

[Measures].Members ON COLUMNS,

NON EMPTY [Date].[Date].[Date] ON ROWS

FROM

[Adventure Works]

This query returns 1,159 rows and clearly does NOT emulate drillthrough.

What is required to make this query return multiple attributes while still emulating drillthrough? Crossjoins. Lots and lots of crossjoins. The previous query is modified to reinsert [Reseller Sales Order Details].[Reseller Sales Orders].[Order Line] to make it a drillthrough query again, as shown below.

SELECT

[Measures].Members ON COLUMNS,

NON EMPTY { [Date].[Date].[Date] *

[Reseller Sales Order Details].[Reseller Sales Orders].[Order Line] } ON ROWS

FROM

[Adventure Works]

This particular query takes a very long time to run but it returns 60,855 rows, as expected. Specifying a single measure instead of [Measures].Members on Axis 0 would make the query much faster.

So, Microsoft really DID know what they were talking about. You can either use the DRILLTHROUGH keyword, or model UDM so that MDX produces a granular rowset.

Integrating Analysis Services 2005 and Reporting Services 2005 with DRILLTHROUGH: Part II of IV

drodabaugh — Sat, 02 Dec 2006 21:03:00 GMT

Part II of IV: Design Implications of the New Drillthrough

Part I of this series showed that using drillthrough in SSAS is hit-or-miss. Drilling through the top cell in the Adventure Works cube returned a rowcount identical to that of the FactResellerSales table underpinning the Reseller Sales measure group. Another example, from a real-world project, showed that this is not a reliable indicator of drillthrough’s behavior. In that example, drillthrough returned fewer rows than the fact table contained, even though the sum of dollars was correct. Microsoft has intentionally changed the behavior of drillthrough to return one row per nonempty detail cell that feeds the cell upon which drillthrough acts.
This is decidedly unsatisfactory if the goal is to show the actual fact rows that contributed to a cell, not a rowset imitated by detail cells. There are three possible ways to address this change.

Tell the users such detail is no longer available. This will surely go over like the proverbial lead zeppelin and does not show a servant's heart.
Force drillthrough behavior by using parameterized relational queries. This is not a satisfactory approach since it adds complexity to report authoring and assumes an available and capable relational data source.
Alter multidimenional design principles to accommodate the new reality. Use an MDX query with crossjoins or with the DRILLTHROUGH keyword to provide drillthrough.

The third option is the only viable option. Since drillthrough seems to return one row for each detail cell that comprises the target cell, there must be a way to ensure that each cell contains one and only one fact row. This suggestion will rankle many. As noted earlier, the point of using OLAP is to have a single cell for a value, regardless of how many fact rows correspond to that tuple. Stated another way, it was legitimate to have many fact rows per single detail cell. But for the “new” drillthrough to work properly, this relationship must be changed to one cube detail cell per one and only one fact row. If each detail cell has one and only one fact row, then drillthrough has no choice but to return one row per fact row. This can be codified with the following rules.

For drillthrough in SSAS to return fact rows based only on UDM information, each detail cell must have one and only one fact row.
For each detail cell to have one and only one fact row, each fact row must be dimensionally unique. No two rows may have the same combination of surrogate keys.

SSAS has two features to enable this design change.

SSAS permits an optional primary key in the DSV fact table. This key can be a combination of existing dimensional columns, or it can be a value manufactured by ETL to ensure uniqueness.
SSAS permits fact dimensions. Christian Wade has an excellent explanation of this nifty new feature at http://blogs.conchango.com/christianwade/archive/2005/04/07/1255.aspx.

This fact table primary key becomes a dimension attribute in the fact dimension, which allows a UDM attribute to enforce the one-to-one relationship between a detail cell and a fact row. It can be invisible, to be used only when called by a drillthrough query. Beware though, as it could exact a hefty toll on SSAS. Imagine having one attribute member for every single row in the fact table! Such an attribute would be prodigious. The multidimensional lattice could grow from “huge” to “unimaginably monstrous.” For those running on 32-bit Windows with the ongoing 3GB RAM limitation, this could be a huge problem even with the slick new dimension memory management in SSAS. Making the fact dimension a ROLAP dimension may be a good idea, and it may be possible to disable the attribute hierarchy for that attribute as well.

Part I of this series used Adventure Works DW as an example. This example is carried forward now, since it illustrates how these techniques are implemented in the Reseller Sales measure group. Inspection reveals the following:

The FactResellerSales table in the AdventureWorksDW SQL database has a unique constraint on the SalesOrderNumber and SalesOrderLineNumber columns.
The FactResellerSales table in the DSV reveals a compound primary key on the SalesOrderNumber and SalesOrderLineNumber columns.
UDM contains a ROLAP dimension called Reseller Sales Order Details. The key attribute, called Reseller Sales Order, is a combination of SalesOrderNumber and SalesOrderLineNumber.
The Reseller Sales measure group ties to the Reseller Sales Order Details dimension with a fact dimension relationship to the Reseller Sales Order attribute.

The upshot of all this is that each detail cell in the Reseller Sales measure group can have one and only one fact row.

Clearly, SSAS drillthrough creates design ripples all the way back through the DW/BI manufacturing pipeline. The following guidelines specify practices if drillthrough is desired.

Facts must have primary keys. ETL and the data pipeline must be able to manufacture meaningful unique identifiers for each fact row. The preferred unique identifier is a combination of meaningful attributes. Identity columns are less desirable since these keys need to be semi-permanent; that is, if the fact table is dropped and recreated with identical data, all of the values in the table primary keys should be as they were. Failure to enforce this will require complete reprocessing of the dimension holding the fact table’s primary key as an attribute, and every measure group using that dimension.
A PK must be designated on the fact table in the SSAS DSV.
A dimension and attribute must be created to pull this key into UDM. This dimension ideally has real business meaning (like an order number and detail line number that could never be duplicated). And regardless of its meaning, it could be huge. Suppose a fact table contains one billion rows. There will be literally one billion members populating the attribute. The new fact dimension in SSAS is tailor-made for this role. The dimension can be populated directly from the fact table without the need to build and populate separate dimension tables in the data source, or carry those objects forward in the DSV.
The storage mode must be carefully considered when building this dimension. As Christian Wade points out, SSAS now permits MOLAP partition storage with ROLAP dimensions. If this dimension is not used for anything other than drillthrough imitation, then ROLAP could be an ideal choice. Dimension processing times will be greatly reduced, and queries that do not directly use the dimension can ignore this dimension.
A decision must be made about how to author drillthrough queries. The DRILLTHROUGH keyword in MDX will still work. The alternative is to write a hefty crossjoin MDX query. The next part of this series will compare these two methods.
Limit drillthrough queries. This limitation takes two forms. First, account for drillthrough during design, by designating which measure groups will be use for drillthrough. Not all measure groups will be used for drillthrough, and in many cases, the data available to SSAS will not have a natural primary key for the fact data. If drillthrough is not required, then there is no mandate to create a unique fact key. Second, carefully control where and how drillthrough is executed. For small cubes, the query response time for any type of drillthrough query is immaterial. For cubes with huge data (e.g. – a billion fact rows), drillthrough on high-level cube cells would be disastrous, regardless of the method by which the query is answered. Ensure that MAXROWS is set properly.

Integrating Analysis Services 2005 and Reporting Services 2005 with DRILLTHROUGH: Part I of IV

drodabaugh — Sat, 02 Dec 2006 20:43:00 GMT

Part I of IV: Two Examples Show that the New Drillthrough May Not Always Behave Like the Old Drillthrough

As noted in a previous post, there was usually no compelling reason for a fact table to have a primary key since there was no need for row uniqueness in the fact table. Since there may be readers who erroneously dispute this, an example is in order. Suppose a customer buys three identical hammers at a big orange home improvement retailer. The cashier swipes them one at a time and places them in the bag. The fact table that records these occurrences has a grain of day, store, cashier, and product, with a measure of quantity and price. Is it okay to write three rows into the fact table? Yes. Could ETL aggregate these identical facts into a single row? Also, yes. Does Analysis Services care? Not one whit, aside from some additional processing time to load three rows instead of one. But keeping three rows is a convenient way to count the number of scanner swipes, and these three rows will be perfectly identical.

Drillthrough on a cell might return all three identical rows to the client and this occurrence was easily factored into the data warehouse design. But this does not work for Analysis Services 2005 (“SSAS”) because drillthrough has been rearchitected. Two examples will show how fundamental these changes are; in fact, the changes are so pervasive that (a) MSAS cubes using DRILLTHROUGH will lose their DRILLTHROUGH upon migration to SSAS, and (b) the “new” DRILLTHROUGH returns a completely different answer in SSAS than it did in MSAS.

The first example provides evidence that drillthrough in SSAS can provide the same answer as the old drillthrough. It uses the AdventureWorksDW sample database for SQL Server 2005, and the Adventure Works DW sample database for SSAS. It focuses solely upon the Reseller Sales measure group, which has the following characteristics:

The DSV fact table is FactResellerSales.
The measures in question are Reseller Sales Amount (source column: SalesAmount) and Reseller Transaction Count (rowcount aggregate function).
Reseller Transaction Count is a hidden measure.

The first QA any good Analysis Services architect/developer performs is a rowcount check and top cell summary check. This first step in the check is to know what the “right” answer is. In this case, the right answer is 100% of the rows in FactResellerSales and the sum of the SalesAmount column. The following query provides the control totals:

SELECT

count(*) as ResellerSalesRowcount,

sum(SalesAmount) as ResellerSalesTotalSales

FROM

dbo.FactResellerSales

ResellerSalesRowcount = 60,855

ResellerSalesTotalSales = $80,450,596.9823

Any drillthrough operation should return 60,855 rows and total dollars of $80,450,596.98. The following MDX query executes a DRILLTHROUGH statement on the Adventure Works cube:

DRILLTHROUGH MAXROWS 100000

SELECT

[Measures].[Reseller Transaction Count] ON COLUMNS

FROM

[Adventure Works]

(Note: MAXROWS is set to 10,000 by default. Since this fact table has far more than 10,000 rows, MAXROWS is set to 100,000.)

This query also returns 60,855 rows, which is the correct answer. Do not be fooled into thinking that this one example proves that drillthrough in SSAS will always return the right answer.

The second example, which uses the aforementioned cash report, shows that drillthrough may not always return the correct answer. The cash report fact table has 20,473 rows in the fact table. But a drillthrough query, identical in form to the query used in the first example, sent back only 18,585 rows. The sum of the dollars returned by the drillthrough set was correct. So the dollars were right, but the rowcount was wrong.

After adding a rowcount measure to the measure group and running the query again, the results were plain. In SSAS, drillthrough does not return every row that went into the cell being drilled. In fact, it does not even query the data source. (http://msdn2.microsoft.com/en-us/library/ms345125.aspx) It returns one row for each detail cell in the cube that contributed to the cell upon which drillthrough occurs. So the 18,585 rows returned by the DRILLTHROUGH statement represent 18,585 distinct combinations of member values for the non-empty detail cells comprising the aggregate cell. Examination of the rowcount measure showed many fact rows with duplicate dimensionality, but not amounts (48 in one case, 41 in another, etc.) In all cases the dollar value returned for the drillthrough row was the sum of the fact rows that went into the detail cell represented by the drillthrough row. In other words, drillthrough reported that there were 48 fact rows that populated that detail cell, but it refused to return the individual rows. This was not acceptable since the cash report requires one row for each transaction, even if they are dimensionally identical.

The difference between MSAS and SSAS can be summarized with these two points:

In MSAS, drillthrough queries the MSAS data source with an SQL statement and returns the fact rows underneath the cell upon which drillthrough acts.
In SSAS, drillthrough does not query the SSAS data source. It queries the cube and returns one row for each detail cell underneath the cell upon which drillthrough acts. An obvious implication of this new behavior is that all information returned by drillthrough must reside solely with SSAS as UDM attributes.

When asked about this behavior, Microsoft’s response was, “This is by design. You can write all drillthrough queries as MDX now.” Darren Gosbel wrote a bug report about it. (http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126175)

Is the latter part really true? Can you really write all drillthrough queries as MDX? Yes, but only if you modify your design to accommodate the “new” drillthrough, because Microsoft changed the rules of the game from MSAS to SSAS.

The next installment of this series will address the changes required in fact and dimension design to accommodate the new DRILLTHROUGH.

Integrating Analysis Services 2005 and Reporting Services 2005 with DRILLTHROUGH; Introduction

drodabaugh — Sat, 02 Dec 2006 20:14:00 GMT

My current client has engaged my employer to implement a financial reporting solution. The first report we chose was a cash report, which displays details not usually carried in a cube. In the past, I never worried about uniqueness of fact rows because it was unnecessary. Having completely identical rows in a fact table was perfectly legitimate, a fact giving indigestion to many traditional data modelers. When Analysis Services 2000 (“MSAS”) loads duplicate rows into the cube, it simply aggregates the measures into the single designated cell identified by the dimensional tuple formed by the surrogate key combination. This is one of the things we love about Analysis Services, that it provides (semi) detailed and aggregated information on a moment’s notice.

But sometimes you need to see more detail than the cube provides. Sometimes you need to see the individual fact rows contributing to a cell because reports require such detail. The aforementioned cash report is little more than a rote listing of all cash transactions for a particular period, e.g. 2006. While the dimensions which form the cash report provide some finely grained information, there are many cash transactions with duplicate dimensionality (though different dollar amounts). These are aggregated into a single detail cell, as expected. But the report needs those detail fact rows. In MSAS, drillthrough on a single cell returned the fact rows (with designated dimensional columns) from the cell’s data source. It did this by forming an SQL statement, pushing it back through the data provider, and passing along the rowset which answered the query. This handy technique allowed very detailed reporting and analysis without overstressing MSAS, though it carried no performance guarantees since query performance was governed by data source performance, not MSAS itself.

While developing the cash report, I needed fact rows so I turned to the trusty DRILLTHROUGH statement. To my chagrin, it didn’t return the correct answer. Specifically, it didn’t return the correct number of rows. This began a frantic search to understand why drillthrough was not behaving as expected. I was tipped off by Teo Lachev’s book, which said that drillthrough had been completely rearchitected and that Analysis Services 2005 (“SSAS”) answered all drillthrough queries from the cube. Uh oh. That had nasty implications that I’ll discuss later and it has forced me to change a couple of design guidelines.

A Web search yielded a lot of clues, but nothing in one place that allowed me to craft a solution. I hope to provide a solution here, in multiple parts. This is the first part in a series to explain how DRILLTHROUGH has changed and how you must change your design techniques to properly utilize it. I will reference several websites when appropriate, for I stand on the shoulders of giants. And should I err in this series, I trust my fellow cubers to provide the truth.

Part I: Two Examples Show that SSS Drillthrough May Not Always Behave Like MSAS Drillthrough

Part II: Design Implications of the New Drillthrough

Part III: Query Examples for SSAS Drillthrough

Part IV: Summary

My First In-Depth Study of SSIS

drodabaugh — Fri, 07 Jul 2006 01:52:00 GMT

I'm a BI guy. I've been mashing on data and building cubes for several years. I've always tended to avoid DTS because there were performance aspects of it I didn't like. In fact, I never considered it a true enterprise-scale tool, a position which has inflamed the passions of several coworkers in the past year as we discuss SSIS. One even said, "I could pluck a star from the sky with DTS!" Perhaps, but you had to build your own rocket to do it.

Without a good ETL tool, my cohorts and I always tended to use the "dump and chase" method. We'd suck the data into a SQL Server data warehouse staging area, and then write stored procedures to transform and cleanse the data. This method was not intuitive and required prodigious T-SQL skill, but it ensured performance because we could easily tune the database with indexes, and it gave us great error handling capabilities to boot.

I'm newly employed as an application development manager for a small upstart BI shop in my hometown. One of my responsibilties is to make technical and technological decisions. It's a foregone conclusion that we're a Microsoft shop; I wouldn't have taken the job without such assurance because that's where my expertise lies and I believe in their framework, products, and vision for BI. So it's SQL Server, SSIS, SSAS, and SSRS. Since ETL seems to chew up the vast majority of an end-to-end BI project, I chose SSIS as my first learning target. I don't need detailed expertise, but I need to be able to use it and guide others in its proper usage.

So now I'm studying this strange new offering in detail and I'm impressed. It's intuitive and everything DTS wasn't. What I love the most is how well it conforms to everyting else Microsoft does. It runs as a service. The packages are saved to dtsx, an XML implementation read by SSIS. It's extensible by .NET. Event handlers provide an easy way to see what's going on. The list is much larger, of course, but I'm citing a few to illustrate that if you just pick a point in the Microsoft BI continuum and start studying, you'll learn the approach and lexicon for nearly everything else as well.

Jon Baker and I are studying for certification. We've tackled XML first because it's arguably the area about which we are most ignorant. At first, I thought it would be an academic curiosity, though admittedly a LARGE one. I was completely wrong. To say it's pervasive is like saying I breathe air about 30 times per minute. I've come to the conclusion that you can't be a SQL Server 2005 product expert without an in-depth knowledge of XML and how it's used in the SQL Server suite of products.

If you're like me, you don't like to study. I say it like this: "I don't like to study, but I love to know." Of course, the former begets the latter, so I have to study despite my distaste and my present struggles have reminded me that it's not important what you start studying; it's only important that you study. You're going to see the same things in so many places that you'll eventually assemble a coherent mental picture about SQL Server 2005. I'm fairly jealous of Allen White and others who already have your 2005 certs, but I'm willing to put in my time. For now, that's SSIS. I highly recommend diving in if you're a Microsoft BI guy because you're going to need detailed knowledge of this fabulous new product.

Part V of My Analysis Services Interview Questions: The Most Common MDX Functions

drodabaugh — Mon, 26 Jun 2006 02:25:00 GMT

I have a litmus test for true multidimensional expertise: MDX. I’ve found MDX to be so potent that I freely admit my own inadequacies. I study it, I use it, and I consult with others who do the same, but I know there’s more. If somebody says they know Analysis Services, I want to see their MDX. Show me somebody who doesn’t know MDX, and I’ll show you a pretender.

Remember, I don’t ask closed-ended questions. I’ve heard interviewers say, “Do you know MDX?” Of course, the answer is always “Yes” but this answer tells you nothing of what the candidate actually knows. I have two favorite MDX questions.

Question 1: What MDX functions do you most commonly use?

This is a great question because you only know this answer by experience. If you ask me this question, the answer practically rushes out of me. “CrossJoin, Descendants, and NonEmpty, in addition to Sum, Count, and Aggregate. My personal favorite is CrossJoin because it allows me identify non-contiguous slices of the cube and aggregate even though those cube cells don’t roll up to a natural ancestor.” Indeed, CrossJoin has easily been my bread and butter.

If you stammer and stutter, you’re in trouble. If the first word out of your mouth is “sum” without any explanation of how you use it, you’re in trouble.

Question 2: Where do you put calculated members?

The reflexive answer is “in the Measures dimension” but this is the obvious answer. So I always follow up with another question. “If you want to create a calculated member that intersects all measures, where do you put it?” A high percentage of candidates can’t answer this question, and the answer is “In a dimension other than Measures.” If they can answer it, I immediately ask them why. The answer is “Because a member in a dimension cannot intersect its own relatives in that dimension.”

Then I ask a real toughy. “Where would you put a calculated member if you wanted it to intersect all other dimensions?” You can hear the wheels turning with this question. You can’t put it in the Measures dimension because it wouldn’t intersect the other measures. You can’t put it in, say, the Time dimension because it wouldn’t intersect the other Time members.

The answer is simple but not obvious unless you’re very creative or somebody told you the answer. (Somebody told me the answer when I first started learning Analysis Services.) You create a dummy dimension solely for the purpose of holding those calculated members! My friends and I refer to this dimension as a “hanger dimension” because the only function it serves is to “hang” calculated members. Any member placed therein will intersect all other dimensions.

In fact, you can use multiple hangers. This is particularly handy if you have calculated members that need to intersect all other user-defined dimensions, Measures, *and* another hanger dimension.

In an earlier post I mentioned a fabulous lady who has taught me many things. She wasn’t able to answer this particular question. When I told her the answer, she immediately understood it. It didn’t affect my recommendation to the boss because she was so good. You’ll never believe what she told me when she arrived for her first day of work. She said, “Do you remember the question you asked me about the hanger dimension? After I hung up the phone, I pulled up Analysis Manager and tried it. It worked really well.” Wow! No wonder we like her so much! She even learns during interviews!

Part IV of My Analysis Services Interview Questions: Technical Features

drodabaugh — Fri, 23 Jun 2006 00:38:00 GMT

In parts two and three of this multi-part series on Analysis Services I discussed interview questions about cool business problems and architectural/design philosophies. The third level of questioning I use deals with product features. You’ll always get these questions if you fail one or both of the first two because even if you aren’t an architect, you could be very productive while working under an architect. Plus, if you’re an ambitious learner, you’ll become an architect by learning from an architect.

Sometimes I even ask the technical questions if you pass the first two questions. Remember, I’m not just looking for answers. I’m also watching your thought process. I want to see passion, and I’ve found the best people can hardly wait to tell you about the great things they’ve done, and when you ask them a question, the answer practically leaps out of them. So if you answer the first two correctly, but I’m not convinced, you may still get the technical questions.

(Interesting story: one fabulous lady I worked with was annoyed when we hired a guy who clearly knew what he was doing, but of whom I asked no technical questions. She said she was bothered by this, and asked my why I’d done this. I said, “Because he clearly knew what he was doing and I’m satisfied that he knew the technical details as well.” He was passionate and articulate. But I told her that we’d ask technical questions in the next interviews if it made her comfortable, and we did that for two more interviews. But she changed her mind when she figured out that people who really know how to use Analysis Services also know its features. She once noted that I didn’t hammer her in her phone interview. I replied that I could tell she was thoughtful, passionate, and a great learner. I wasn’t wrong. To this day that lady is one of the very finest technologists I have ever met, and I would work with her anywhere, anytime. Though I have left the client for whom she works, we still talk regularly.)

Even my technical questions are stratified. My favorite technical question is “How do you optimize a dimension?” (Remember, this question’s context was Analysis Services 2000.)

The correct answer has four parts:

(1) If possible, ensure that member keys are unique across a given level, and

(2) If possible, ensure that member keys are unique across the entire dimension.

(3) If possible, ensure that member names are unique across a given level, and

(4) If possible, ensure that member names are unique across a given dimension.

When this is achieved, you have very compact dimensions. When answering queries, Analysis Services can avoid assigning context to members by using ancestral chains, and things get very quick and compact. In my mind, this is the first technique for excellence in Analysis Services, and only two or three people have ever answered it correctly. Usually the respondent, eager to get up after being knocked down by the two prior questions, blurts out, “I go to Tools and select Optimize Schema.” They’re usually shocked when I inform them that this isn’t the correct answer because Optimize Schema optimizes a cube, and I asked about optimizing a dimension. Honestly, I’ve never had a candidate answer the question correctly if this is their first answer.

My second favorite question is about optimizing the cube schema. Nearly all candidates know what this feature is and have used it. I avoid closed-ended questions like “Have you ever optimized a cube schema?” because the answer is a simple, “Yes.” This doesn’t tell me what you know, so I always ask what optimizing a cube schema does. Ironically, the same people who use schema optimization to answer the dimension optimization question don’t know what schema optimization actually does, or why it’s used. The answer is that it reduces or eliminates joins in the cube processing SQL statement. It may be the most powerful technique to improve cube processing performance (assuming ample hardware and parallel partition processing that doesn’t swamp the server, and moderate aggregations).

One candidate, who took a beating on the two high level questions, also took a beating on these two technical questions. He was obviously unaccustomed to this because after a half hour he said, “Well, maybe you just haven’t actually done this.” This new tack caught me a little off guard since I had explained to him he couldn’t articulate any cool business problems, he didn’t have any strong architectural philosophies with which I agreed (and I told him why), and he didn’t know how to optimize a dimension. I even told him *why* these things were important and what the right answers were. In addition to getting the answers wrong, he demonstrated that he wasn’t teachable, a bad trait for any technologist since we must learn continuously. He was notable not only because of his belligerence, but also because he only had one name on his resume. He’d concatenated his first and last name into a single word, which had eight syllables and was in a foreign tongue. He left quite an impression, and six months later when his resume landed on our desks from a different staffing company, we immediately put it into the circular file. Ignorance, belligerence, and a goofy name are a bad combination.

Part III of My Analysis Services Interview Questions: Architectural Philosophy

drodabaugh — Wed, 21 Jun 2006 21:58:00 GMT

In the previous installment of Dave’s Cube, I laid out my first interview question for Analysis Services candidates. Since I use an adaptive technique, I start with the hard questions and move to technical questions later (if necessary). My first interview question is always about cool business problems that you’ve solved with Analysis Services. As noted, that’s rough for most people, but it doesn’t get easier with the second question.

My second question is always about architectural/design philosophies. I don’t ask too many closed-ended questions because I want you to talk, so I know what you know. I usually ask the following question:

“Do you have any particular design philosophies or architectural principles that you will use to guide this client toward best practices?”

Believe it or not, you can say something different than I would without consequence, as long as you can provide your reasoning. (You must also be correct, of course. Telling me it’s red when I know it’s blue won’t work, even if you have reasons.) I’m a stickler for best practices because they are the authority upon which you stand when telling a client how best to implement a product they’ve probably never used. Here are some of the answers I expect to hear:

“I recommend that a client always carry as much detail as possible into the data warehouse and into a cube. Details may use extra space and CPU cycles, but they ensure the future value of the application because they enable the most calculations to be performed upon that data without rework back into the data warehouse and/or cube. Details often carry negligible incremental cost on first development.”

“I like to use as many dimensions/attributes as suggested by the data – even if those attributes are not in the business requirements. The incremental time required to design and populate those extra attributes is usually negligible, but the business will often request those dimensions as filters and slicers for calculations they modify or identify late in the process, or even after a project is closed and customer acceptance is complete.”

“I design first for query performance, and then second for processing performance. The purpose of an Analysis Services implementation is to serve quantitative answers to end users; query performance is paramount. Processing performance issues can usually be solved by tuning in the relational layer and by eliminating joins in the cube processing statement.”

These answers tell me two things. First, you know best practices and you’ve actually used them. The reasoning behind a philosophy usually only comes from experience. Second, I know immediately when you are a learner and a thinker because such people answer this question *immediately* and with passion.

I have received the same spate of humorous answers I’ve received for my first interview question:

“I use surrogate keys.” Personally, I prefer skeleton keys since they’re very versatile.

“I love star schemas.” Me too. And Buckeye football. And Chevrolet Corvettes. And my kids. But I digress… None of these are architectural philosophies.

Of course, the Underpants Gnomes hold sway in the mind of many people answering this question. Recently I figured out why. Underpants Gnomes wear cones on their heads. Dogs hate cats. Buckeyes and Wolverines are at perpetual enmity. Everybody knows that cones hate cubes, but they don’t have an annual contest to settle the score one November Saturday at noon (unless ABC says to play at 3:30 PM). The Underpants Gnomes consider infiltration into the minds of aspiring Analysis Services candidates a dutiful service to Gnomekind! Kill the cubes!

Has a candidate ever answered quickly and passionately with a wrong answer? Only once. We were interviewing a candidate from NYC who said he was an Analysis Services architect. He was articulate, smooth, and eager. He laid out some very good business problems he’d solved with Analysis Services, and we were feeling good since it’s hard to find experienced, thoughtful Analysis Services resources. His answer to this question flabbergasted us. He said, “Use as few dimensions as possible.” Upon inquiry, he divulged his reasoning. “Every time I add more dimensions to the cube, cube processing slows down to the point where I can’t process the cubes.” This man was freely sacrificing the power of multidimensional databasing (dimensions) on the altar of performance because he couldn’t figure out how to tune the technology!

I told him that not only did the three people on our end disagree with him, but we had empirical evidence that he was wrong. We had cubes with 50 or more dimensions in them, yet we could process them just as quickly as if they had only five dimensions. Since he failed a high-level question, I immediately drilled into technical knowledge. I asked him if he knew how to optimize a cube schema. Oddly, he answered this question by discussing dimension optimization, a question that flunks all but the most knowledgeable of Analysis Services people. He said that he could make dimension keys and names unique, which is great but doesn’t help you optimize a cube schema.

Incredibly, this promising guy had used Analysis Services for years but had never once bothered to learn about optimizing a cube schema. I explained to him that when he adds dimensions to a cube, Analysis Services adds a join to the SQL statement it issues against the data provider. (He said he’d never looked at the SQL statement, a sensible answer since he didn’t know he could do anything to change it.) By optimizing the cube schema, you reduce or completely eliminate the joins in the cube processing statement. Naturally, queries against a single table in the data source can have nearly instantaneous response times and marvelously high row payout rates, often choking either the RDBMS or Analysis Server. You can theoretically add an infinite number of dimensions and not appreciably slow cube processing. (Remember: I was interviewing for Analysis Services 2000 positions; the answer will vary somewhat for SSAS.)

He knew he was cooked. You could hear it in his voice. I think the full weight of his error hit him quickly. He hadn’t just missed a prominent performance tuning feature in Analysis Services. No, he’d been giving clients architectural advice based on this glaring error. I told him that after he hangs up the phone, spending two hours with Books Online to read up on this feature would make a much better Analysis Services expert than he was before the phone call.

It was obvious that he was experienced, smart, and teachable. What he didn’t know what that we asked the boss if we could bring him on anyway since we were pretty sure we could fill in his holes with little effort. Sadly, his hourly rate was so high that the boss rejected him. If you’re going to ask for a mountain of money you need to at least know as much (and hopefully more) than everybody else. Dave Ramsey says, “In our economy, you get paid for what you know.” I modify that to the following: “In our economy, you get paid more than the next guy depending on how much more you know than the next guy.” We’re both right.

Study hard when mastering Analysis Services! It pays!

Part II of My Analysis Services Interview Questions: Cool Business Problems

drodabaugh — Tue, 20 Jun 2006 17:42:00 GMT

I’ve seen a variety of techniques used by staffing companies to combat the beating I’ve given their previous candidates. Though I’ve never been hit by the bait-and-switch, candidates have tried to look up answers during the interview; they’ve had somebody in the room with them; and the last technique seemed to be the group interview. In addition to the candidate, the phone interview would include the sales rep for the staffing company, and often another technologist. Why? To write down the questions, of course! I can only guess that the last candidate I rejected complained about how unfair the interview was, and the recruiter wants to be sure they get every advantage possible and they think they’ll get a leg up by writing what I ask. Fair enough, but it won't help you because I’ve learned some great interviewing techniques, like asking follow-on questions based on your first answer. I understand that the goal for a staffing company is to put cheeks into paying seats, but that's not my goal when I'm interviewing, or when I'm a candidate.

I use an axiom in the classroom and with client personnel who are learning business intelligence: if you can’t articulate the answer, you don’t know it. Some will say, “I know the answer. It’s in my head but I can’t tell you what it is.” I repeat: if you can’t articulate something, you don’t know it. The only evidence of knowledge is its articulation, so let’s lay out a couple of rules.

Rule 1: If you can’t articulate something, you don’t know it.
Rule 2: If you think you know it but just can’t communicate it, refer to Rule 1.

I always start with high-level business questions. I believe that technical knowledge is the easiest of all skills to acquire. Writing, speaking, architecting, and mentoring are far more difficult. Complex business problems require passionate people with creative solutions. The high-level questions explore all of these at one time. I want to know if you’re a thinker and a learner, squeezing as much as possible from your experiences.

I usually lead with the following:

“Tell me about some great business problems you’ve solved with Analysis Services.”

I expect to hear an answer like this: “I used Analysis Services to provide certification supplier metrics for a retail supply chain. The database and cube(s) that provided these answers had 45 dimensions, about 10 measures, and 25 calculated members. My favorite metric was a ratio of dollars ordered to dollars received because the business could ensure they were getting what they paid for. Additionally, they were able to reduce back office labor for receiving because they could avoid detail receiving for suppliers they knew were doing well.”

Some of the answers I’ve actually received are downright comical.

“I used surrogate keys.” That’s very nice, but it’s hardly a business problem.

“I built a star schema.” Stating an obvious architecture is not evidence that you really know what you’re doing.

“I used MDX.” That’s good! Perhaps you used dimensions and cubes as well, but quoting product features provides no evidence of problem solutions.

But my favorite is a maddening three-part answer vaguely reminiscent of the Underpants Gnomes in a certain South Park episode. The Underpants Gnomes were “experts” in big business because “underpants are big business!” The Underpants Gnomes had three steps:

Step 1: Collect underpants.
Step 2: ?
Step 3: Profits!

Here’s what I get as the most common answer to the question about business solutions. “First, I talk to the business and gather requirements. Then I make a design. Then I build software.” See? It IS the Underpants Gnomes, who would say it like this:

Step 1: Collect business requirements.
Step 2: Make a design.
Step 3: Build software.

Of course, this isn’t a business solution. It’s an approach to solving problems and it’s so obvious that nobody should have to say it. It would be like going to a staff meeting and hearing a low-level manager lecture you for 15 minutes on the importance of profit. (Duh. And yes, I’ve experienced this with a prior employer. Note I said "prior.")

It may be obvious, but I hear it repeated so many times. Over and over and OVER again.

In an earlier installment of this blog I noted the existence of talking points. I invariably get these from candidates stemming from one Asian country. It’s like there’s a four hour seminar that instructs candidates, “Americans LOVE process. If you tell them this, they’ll see that you’re truly an expert and will have no choice but to take you!” I’ve had candidates stick to this party line even when I tell them, “That isn’t a business problem. What interesting business problems have you solved?” I’ve actually told more than one candidate to stop repeating the line because I don’t want to hear it again. (I must be the Dr. Laura of BI interviews.) I usually provide examples in hopes of spurring the candidate to action because sometimes there are linguistic difficulties. Some candidates respond well after that; some don’t. In a couple of cases the interview has ended because the candidate can’t deviate from the party line.

Why can’t they answer the question? There are many reasons. After teaching SQL Server to adults for more than five years, I’ve taken a very dim view of the average adult’s learning ambitions and thought intensity. Most people just don’t pay attention and learn lessons. Others are terrified of talking, but it’s an interview and you must talk. I think the majority of them simply don’t know the answer to the question but they believe such an admission is failure. Well, you surely won’t be recommended for an architect position if you can’t tell me what cool business problems you’ve solved, but you may have a personal epiphany that leads to personal growth.

Mostly, candidates responding in this fashion haven’t really used Analysis Services to any great degree even though their resume says they have. They’ve had success bluffing their way with interviewers who can’t tell the difference. Personally, I only put on my resume those things in which I am competent or expert but I seem to be a rarity. In fact, here's some advice: I don't like 10 page resumes. I don't mind four or five pages because it's my duty to read your resume and see what you've written. If you've worked for 10 or 15 years, I expect more than two pages. But if you get much past five pages, you're either playing cut-as-paste patsy, or you're telling me stuff I don't care about, like what you wrote in FORTRAN or how many NIC's you replaced. BTW, if you feel the need to tell me that, this doesn't help you and may hurt you. If this is a BI interview then I want to know what you've done that is pertinent to BI. (And don’t get me started on recruiters who don’t actually READ your resume. I get called frequently for Oracle/Informatica work even though those words don’t appear on my resume. Yes, many of the same letters in those two words appear on my resume, but not the actual words themselves. Would you believe that today I got a hit for a UNIX/Sun/Solaris admin? Huh? You can't find any of those words on my resume with just a keyword search! But I digress...)

The lesson here is to pay attention to your experiences. Wring every last drop of wisdom from your work. Be prepared to talk about those experiences and what you’ve learned. You never know when an interviewer will ask you about them!

NEXT. Part III of My Analysis Services Interview Questions: Architectural Philosophy

My Analysis Services Interview Questions

drodabaugh — Mon, 19 Jun 2006 21:05:00 GMT

My good friend Jon Baker suggested that I start a blog on this site, and as I tend to follow the advice of those smarter than I, here it is. As noted by the title of my blog, I’m a DW/BI guy with extensive experience using Microsoft SQL Server, Analysis Services, Reporting Services, and soon Integration Services. I’m in Columbus, Ohio (GO BUCKS!) and like all multidimensional guys, I’m a little crazy. Not overtly so, of course. Just off-kilter a little.

One day Jon and I were swapping interview stories. (He's posted about this very topic at http://www.sqljunkies.com/WebLog/outerjoin/archive/2006/06/19/21960.aspx Is this a conspiracy? Oh yes.) He fired his very best interview question at me and I flunked it in spectacular fashion. That got me to thinking about how I interview DW/BI candidates, particularly those who claim to know Analysis Services. We decided that some of these stories were pretty good, so I'll be following this post with my favorite interview questions, and some of the responses I've received.

My last big client was The Home Depot. For 18 months I traveled between Columbus and Atlanta to help pioneer an Analysis Services implementation in their Enterprise Data Warehouse (“EDW”) group. In that time we always seemed to be short on talent, so I was asked to interview candidates. I probably interviewed a dozen candidates in about nine months, most of whom had Analysis Services on their resumes, and most of who really knew little about it or how to use it.

I admit to being a real hammer when conducting an interview. I take an adaptive approach, by starting with the high-level questions about architecture, business problems, and design philosophies. If the candidate fails those questions (and most do) then I begin to drill down into their technical knowledge. Just because a candidate can’t fill the role of architect doesn’t mean they aren’t productive when working under an architect. I’m sad to report that most people who fail the high-level questions also seem to know very little about Analysis Services’ product features. What’s shocking is that some of these people are actually giving clients architectural advice! Why can’t people admit “I don’t know the answer to that question” when peppered in an interview? Why try to bluster and bluff your way through it? Do you think I won’t know what you’re doing?! (Apparently, the pretender detector for most interviewers isn’t very sensitive as many of these people continue to land Analysis Services gigs.)

This transcends race, gender, nationality, and educational pedigree. I’ve interviewed Americans, Russians, a Serb, an Iranian, and quite some number of Indians. I will admit that I’ve had the most trouble with the country presently serving in a large outsourcing role. I’m pretty sure there are talking points and that candidates are being coached because I see unswerving devotion to their use. Supposedly, the interviewer will lap up the answer and be convinced of expertise! As we’ll see, these talking points are vaguely reminiscent of the Underpants Gnomes in a particular South Park episode.

So this is the first in a multi-part series about interviewing Analysis Services candidates. I offer these stories because they’re humorous and many of you will nod in acknowledgement. Some of you who read these may think I'm a real blowhard. Please be assured that I intend no offense to anyone! I've mentored men and women of varying nationalities, and they have taught me a thing or two as well. I also write these stories because if you’re considering a career in DW/BI using Analysis Services, these can help you. All of us who pitch our tent in the Microsoft camp do well when Microsoft does well, and we help them by attaining product expertise and plying our craft in the field. I’ve worked hard to attain expertise (and hopefully I have). If I can do it, so can you.

The next installment of My Analysis Services Interview Questions: Cool Business Problems.