- Ramesh, can you tell what was the major business challenge?

In our athlete application, we could manage what information could be captured, difficulty around it, hold this information so everybody could see it. That was a typical application. But in sports, they are very much based on health: they can collect all information about different players, and they should be able, by the end of the year, see who got injured, when, what type of injury and how much time and money have been invested during this year.

What we were trying to do was actually to predict the type of injury that would happen. The top stage was obviously reporting, what you did, so you can look at different tournaments, types of injuries that happened. But the ultimate intent was actually to use these reports not just for reporting purposes but actually build intelligence on specific case. For instance, when Sidney Crosby got injured, they all started talking about concussions a lot more for the whole year. They looked at different types of injuries, but focused on concussions. So, in application we’re capturing data about concussion. We needed then immediately go back in time to get all the statistics about concussions: when it was happening, and they did about it. In the application, it was difficult to report on those stats.

- What options did you consider for the analytical reporting solution?

There were not many options.  The data was in the database, and we could run reports of it, but this would take a lot of time to actually run these reports, to get information together. But the ad-hoc reports are easy, the whole purpose of a BI solution.

We knew that we need a data warehouse. When we talked to our client, all requirements they were talking about reporting side, they were all BI requirements rather than reporting requirements.

- What criteria did you use for selecting a business intelligence solution provider?

The criteria that we used were to clarify at high-level what the solution would look like. So, what we wanted was a consultant who would come with experience with similar requirements, could work independently and show us how the application should look like, and what software we should use. We wanted somebody who is knowledgeable, who has experience, and who can work independently. That’s what we were looking for.

- And how did the implementation go? Did I satisfy your requirements?

Yes, absolutely. In terms of how the project implementation itself went, we obviously had some challenges, because it was not very clear what client wanted. Although we ultimately knew what they wanted to achieve, in terms of reporting, it wasn’t clear what type of reports they wanted, especially in terms of infrastructure requirements, how on the production environment the data is going to be transferred, and how the changes in the reports were supposed to be happening. Those were some challenges that we had.

You helped us actually to define that, in terms of production environment, what needs to be run where, how to stage data and how much time it would take, – typical challenges in terms of implementation, in terms of a bigger picture, which should be the focus initially in terms of architecture itself, in terms of creating reports and how long it takes, how they should be. But I think the biggest value that you’ve added is actually defining the big picture, and how many servers we needed, what services were going to run, how they needed to be connected, helped us develop automation scheduling.

I think the key difference is actually, if you look for a great BI, between developers and consultants, – developers would immediately dive deep into defining regulations, and going to very deep to subject matter, while the core is defining the bigger picture. You started with the bigger picture, and you kept emphasizing it to us, to understand how you would your service and everything else would really work. I think that’s what differentiates BI consultant from BI developer.

- How did you think we worked with developers? How easy it was to pick up and share information? Do you think the project went smooth?

So, you needed the information, the schema, and data types, the volume of information for you to get started. And the key, from management perspective, that I would look for is how much time you consume, how much developers time you’re taking, for you to get started. I think you didn’t take too much time. You picked up really quickly. From a manager perspective, it’s important how much of your time would it take to become productive. You did really well. I think you picked up very quickly, set yourself up quickly in terms of information architecture. You didn’t take that much time from developers as our previous vendors did.

- Thank you. And finally, Ramesh, what feedback did you hear from the users?

The feedback was good. We had issues from time to time regarding some system delays, but on the reporting site the data was always correct. Unfortunately, we didn’t have direct relationships with client, but we still got a good feedback.

- Christina, can you recall why Unilever Canada needed new reporting system in the first place?

We needed a reporting tool that we could use to pull the sales data from once the new SAP instance was implemented in Canada.

At that time, Unilever Canada had their own sales reporting solution, but it didn’t meet the need of the business. It was for the executives: total sales, then break down by brand… It was more to support the sales part of business, where we needed something that supports actual business people, something that would provide them much more detail.

The main reason why we needed the reporting cube was to allow the sales reps to drill down and analyze data based on the actual delivery date of the product as opposed to the sale or invoice date which happened in SAP.

If you’re looking at information in the sales system that Unilever uses, it would say, “we sold X amount of dollars in week 1, X amount of dollars in week 2…” however you’re not really looking at your sales. What you’re looking is what we invoiced at that week. Well, really, the sales reps don’t care when product was invoiced, that gives you not nearly enough information.

The cube that we’ve built goes down to item level, and has actual order data, invoice and delivery note data. It would also allow us to not only know what they sold or what they delivered on every single day and every single order for each product for each customer, you can also compare that to order data, which Unilever had no visibility of.

SOCS cube reporting allowed us to compare year back to actual data and to do case fill or order fill. When we’ve built the sales reporting cube off the SOCS database, we now have visibility of who did sell what and to whom. So we can which sub-distributors they sold it to and also their own individual customers, which is huge. We needed to have good visibility of what we sold them, what the product mix was, even down to how ads were pulled. Well, Unilever had none of that information. Now it’s still limited, because they don’t have the visibility to the sales that sub distributors made to buy sell customers we have visibility only to the data that is on the SOCS system.

It’s really important for account managers to be able to tell how an ad pulled. Because in ice-cream there’s a lot of promotional activity and a lot of trade spend that’s going on, the price is not the same throughout the year, it changes drastically from week to week, so, if they put a $1.99 ad on over Easter, they want to know exactly what the sales were that resulted from that ad. In SAP, the account managers only have the invoice data. If they go and look in SAP, or whatever the program is that pulls from SAP, all they can see is “I sold these many cases at this price in this week”. In our system, they can actually literally say, OK, I want to include only the products on add, and see deliveries made during the week leading up to the ad by store. And they can immediately tell what an ad pulled.

They also have the ability to be more proactive and see what orders are coming in leading up to an ad. So, if they had a $1.99 ad running at A&P, they can run a report by store and see what orders have been placed before we’ve even delivered them. Because we typically order 2 days ahead of time, so, they can go through and say, OK, this route is delivered on Wednesday, all the order should be in the system on Monday, and if I look on Tuesday, they’ll say, “Oh, this store didn’t order, this store – didn’t order enough,” and proactively follow up with them and by saying, “I think we need to increase the orders so you have that product to sell during the ad period.” Whereas before it was always “just wait and see how the sales come in and then say how it did.” It gives them a lot more control over the account.

For the out-of-home team, it’s quite different. That’s on the grocery side, where it’s basically all about the delivery and order and everything leading up to it. For out-of-home, again, the key advantage is that they have information they wouldn’t have otherwise. They now have the buy-sell sales information, all sales that a distributor makes to sub-distributors and individual customers, that information doesn’t exist anywhere else other than this reporting cube. So they would just be completely blind without it. We’ve created the two new measures, sales based on key account price and sales based on the distributor’s price, we’ve sent that pricing down into the cube, and that gave us that price so we can compare what the sales were at these price points to what the plan was versus what the actual invoice dollars were.” And that’s fabulous; they use these new measures over everything else. The out-of-home team – that’s the main thing that they look at.

- If we look back, how did the cube development go? We started in the last days of June and finished in the middle of August. Did we manage to do it on budget?

Yes. It was definitely on budget and within time. I think we were probably a little delayed but it has nothing to do with you, – it was because of a delay in Unilever giving us their ‘go ahead’.

- What was your experience with the implementation?

It was really good. I think it was much better than the previous reporting solution we had, which we used as a starting point.

We had something similar when we were part of North America ice-cream, but it was awful. The users hated it. It was very-very cumbersome to use. It was one of those programs, where you use the Wizard, and the user is forced to select or not select items from various hierarchies and measure groups. It’s horrible because it may have 20 different options for selecting criteria when you really only need to filter on just 3 things. So, I found users were always complaining “I don’t know what to put”, and I’m answering, “Oh, you can ignore – you don’t need to put anything in there”, “But then why it’s asking me select something?” It was just difficult.

- How was user experience with the SOCS cube different comparing to previous reporting system? And how did the user training go?

We even had the ability to create a library. We asked our users beforehand “What do you want to see”, “What are you getting today?” and “How could we make your life easier?” And then we came up with about 20 or 25 different reports. We looked for overlapping requests, that we thought would be good for the business, and we went and created those as a library.

Now I can use this report library as a starting point. If I’m training somebody and somebody calls me to say, “OK, I want to build a report like this.” How do I do that? I can easily say, “Well, that’s very similar to this report, in the library, so let’s start there, and open it up,” and I just show them why I selected what I did, and what this report is doing and say, “OK, now you want to see this measure instead of this measure, then we can just go and make changes, instead of starting from scratch.” I find a lot of people, especially if they are new to reporting, they don’t know what they want to see. They’ll say “I want to be able to see how my accounts are doing, but they don’t really understand what is it they need to pull.” So, if you have a library of different reports, you can easily pull them up for users and have them tell you yeah, I kind of want something like this.” It’s then easier for them to tell me what needs to be changed rather than starting from the beginning.

- How long does it take to train a person to use the cube reports?

We usually do a 2 or 3 hour training session, but that also involves an overview. What I normally do is I give them an overview of the business functions and say, here’s what SOCS is, here’s how it’s used by our distributors, and here’s the flow chart for the order process. I think it’s important that they get the background of the entire process first, and then based on that, here’s the information that’s available on the cube.

Then we basically go through every different hierarchy, as well as associated attributes and the cube measures. Once the overview has been completed we’ll start showing them some of the reports that exist and say “How you think that would fit in your business?” or if you’re an account manager or the home team manager, “What kind of information would you like to see?” and we can help them build some of the reports.

Typically, I’ll work with them, and create the first 3 to 5 reports during the training period, then I’ll send them an email as a briefing book and tell them, “Now you go ahead and play around with these and then let me know if you have any issues or if you want me to help you with something else.”

- Are users comfortable to change reports themselves?

It depends on the user. I’d say, 75% of them yes. 25% – you can show them 18 times, and it just doesn’t sink in. I don’t think that’d be any different with anything else. It’s just one of those things.

- How many users do you have at the moment?

There are probably around forty to fifty. It’s primarily used by account managers and out-of-home team.

- Did you measure some of the benefits of using our BI system?

It’s very hard to actualize. We had to come up with something when we put the proposal together to roll out to the distributors. We tried our best to compare those accounts in which we do have this visibility, to those accounts to which we don’t have the visibility. We showed between the 15 and 20% increase in sales.

- If having this information in the cube would bring 20% increase in sales, how in particular would it happen: by better marketing, pricing or other activity?

By better asset management, by moving those cabinets that are not performing, pulling them out and putting them somewhere else where they’d perform better, and better product mix.

We can easily look at trends, too.

You can look at the percentage of makeup, what they were buying last year, and what they’re buying this year, and look to see that your key items are going up in sales, and check also volume versus dollars. Because even if your dollars increase, – we’ve put price increase, what you really want to see if your actual volume is also increasing.
For example, this year Unilever had a huge push on Magnum, which was a new launch in Canada. The cube gave us the ability to go and look by SKU, by banner and see what was shipped to each location by the launch date, any promotions that ran, and how that each individual SKU performed. The cube was also useful for sales and marketing analysis, you could easily see what the trends are and if the sales on certain products are trending down or if the cost of certain items going up, you want to see if they are actually performing well enough to keep them in the portfolio, or discontinue them. It is a very-very strong application.

But it’s just unfortunate we’re so limited by Unilever. Because they host the cube it can only be accessed by Unilever employees – for security reasons. I would love to roll this application out to the SOCS [relational database] users as well. It’s really hard when there’s something you know would benefit people and you just can’t give it to them, and you see them struggling. In SOCS, I couldn’t separate the Unilever products versus the non-Unilever, so I’d have to run one report per month, because there’s so much information, the number of records is so huge, and I had to put them in Excel, and sort them, and filter them, it was a nightmare. The other day I was asked for a report of total sales by month, by product… He said, “I really need this report, it’s very urgent”. I had to tell him that I didn’t have time to do it until the end of next week, because the number of records was so huge, it would probably take me at least a half a day to pull all the information and compile in the format that he wanted it. If I had the data in the reporting cube, it would have taken me 5 minutes. It’s frustrating for the users, too.

I’ve actually been telling users “The best thing you can do is help me talk Unilever into rolling the application out to the other Non Unilever users so that everyone has access to the same reporting capabilities.

- So, as US took over our SOCS cube, does somebody else except Canada is using it?

No, it’s just Canada using it. The main reason for that is US is a little bit different. In the US, they hardly have any buy-back, because it’s mostly warehouse customers. It’s just a different setup here. We have 4 master distributors, all using the same system, we feed them all the data every night, so they’ve got all the pricing and the products and the customer fed from the SAP. And in the US, because it’s so much larger, the size of business and the landscape, and everything – they’ve got literally hundreds of distributors. So, each distributor can use whatever system they want, they set up any product codes they want, any customers they want to maintain their own pricing, and they would want to go and key in the buy-back information into this program that would bring it in to Unilever. They were trying in implement some sort of reporting capabilities in the States for the last 2 years but it was very difficult. They wanted to get all the sales data from all of these different distributors, and build one cube. But the distributors create their own master data and hierarchies so nothing aligns with the Unilever master data. Mapping tables need to be created to create a common reporting structure. Where in Canada it is quite controlled, Unilever sends us the information that they need to us and we funnel it down to the distributors so everyone is using the same product codes, hierarchies etc… It gets a little muddy when you try to look at buy-sell information, as this is controlled by each individual distributor. Down the road we may want to look at talking to all 4 distributors and put in one hierarchy, structure that everybody can use, that’s consistent across them all, and you can properly report by hierarchy. For now, we created another field that we called the Retailer Reporting Code, just so we could classify the customer in the way that makes sense to Unilever. But it’s really difficult in the US.

- Do distributors use the system themselves?

No. And that’s the goal for the future, because we’re really-really struggling with that right now. The problem is: they don’t have the visibility into the reporting cube because it’s held behind Unilever’s firewalls. So, they’re constantly coming and saying, OK, I need you to build this report and this report…

- And how do you report on the data that is not in the cube?

I can’t pull the information out of SOCS reporting cube right now because I don’t have it all there. Because in the SOCS database itself, I have non-Unilever customers and Unilever customers, we’ve got Unilever products and non-Unilever products, so they may sell Chapman’s… and other brands. Well, I can’t give Unilever access to that data. So we only pull Unilever products into the mix. So, now when a distributor asks “I want to report on my entire business”, I don’t have it in the cube, because we don’t store all the information.

So I have to manually go into the database and build queries and schedule reoccurring data pulls. We ended up having to create scheduled tasks on the database to run these files that contain different SQL statements every night, let’s say 12:30 in the morning, and then we email the .csv extracts to all the distributors.

We are currently trying to convince Unilever, to let us host the application, we’ll add all the other Non Unilever information in to the cube that the distributors need, and we’ll segregate it. We’ll create multiple cubes, or views, however we want to do that… so Unilever can still see only their information, but I can roll the application out to additional users.

Some of the stuff the distributors ask me for is very complicated. It might take me 2 hours to build the query and run the reports directly out of the database, where it would take me 5 minutes to do it in the reporting cube. And if I build a report in the cube I can the template to other users. The problem with doing it right out of the database is that you have to create the query individually for each master distributor. And it’s very difficult to share reports.

So, they had a distributor meeting about a month ago, and they said: “It would be great if we had these reports built so that we have the same information for each distributor – looking at the data in the same way…” Because when they all build their own reports, and they all look different, you’re not comparing apples to apples. “The goal is trying to get one suite of reports that we can all share.” And I answered “Without the tools that I’m asking for, someone would need to go and run these different queries every day, put them in Excel, do lookups on them, and summarize the data. We’d have to maintain at least 10 different templates. It just doesn’t make any sense.” So, I asked again to please “give us the reporting cube project, and we’ll be able to deliver the suite of reports that the business is asking for.”

It’s going to the point now to where we have literally a hundred report getting run overnight. It’s really bad. The front end application has a very rigid report menu – these are the reports that other countries have asked for or that came initially with the program but they’re very limiting. You might be able to apply filters to some of them, or put date ranges – that sort of thing – but you can’t modify them. So, every time they want a new report I have to go to the developers and often it’s $5,000 to develop one report! That’s insane! Instead, we’ve created this extractor and we’re writing all these SQL statements off the database but now I’ve got the problem when developers are saying “OK, hold on, we can’t keep doing this, we’re just adding more and more extractor jobs every week and we are putting too much stress on the infrastructure.

We’re really at the point now when we’re saying, we can’t add anymore extractor jobs; it’s just too much.

- If you had all information in the cube, would it be better?

If I have all the data in the cube, I can literally build any report in 5 minutes. It’s so straight-forward.

It’s so easy for me to help and train users, too, because you can build briefing books for them, and simply email the report templates to them. They can then open them up and we can go through them together, on the phone. I can also easily build something that all of account managers can use – they simply select their account from the filter at the top of the page to see their relevant data.

- And it wouldn’t stress the original system at all?

No.

- Is it because the reporting data would be hosted in the totally separate environment?

Yes, completely. We’d just pull the feed nightly of all of our data from the data warehouse that resided on another server.

- Back in 2008 was it difficult for Unilever Canada, as a subsidiary of US, to get a permission to develop your own BI reporting cube?

It was a fairly easy thing to do, because we had that information, and the business was relying on the information that already existed. But because Unilever Canada was integrating systems we had the opportunity to develop a new cube that would meet the current business needs – added functionality.

1. Use Shared Memory protocol to optimize source data retrieval >1.5 times

I came across this remarkable feature while reading SQL Server 2008 R2 Analysis Services Operations Guide.

It’s applicable when your data warehouse database is on the same machine as Analysis Services, which is almost always the case. (There are definite reasons for adhering to this architecture: this keeps production cube intact during processing; cube refreshing is done afterwards by synchronizing or backing-and-restoring.)

To enforce use of shared memory:

1. Make sure Shared Memory protocol is enabled:
   
2. Force shared memory use. Change data source connection string in OLAP data source properties: add lpc:. The connection string will look like this: Data Source=lpc:<SQL Server instance name>; Integrated Security=SSPI; Initial Catalog=<database name>
   

   According to the white paper, the AdventureWorksDW2008R2 cube processing throughput has improved from 112,000 to 180,000 rows/sec. Impressive, isn’t? It is, especially considering that this just a simple setting tweak.

2. Disable Flight Recorder on machine which does data processing

This change is overlooked most of the time. Flight Recorder, while being a great tool for troubleshooting of end-user query performance in production environment, is absolutely unnecessary on your processing server. You better eliminate its I/O overhead.

Set Log\Flight Recorder\Enabled property to false. (By default, it is set to true.)

• - Prevent one application from impacting another, including times when upgrades or changes are made;
• - Duplicate a standard environment and speed up new system configuration;
• - Deploy different technologies on a single hardware platform.

Here, I’d like share some errors and trials I went through in my search of virtual environment that would support my BI development and testing work.

The idea of using virtual machines (VM) to distribute my software pieces came to me after I’ve hit the hard drive space limit on my Toshiba Qosmio’s rapid drive.

The laptop I’ve chosen came with 50GB solid state ultrafast drive. Providing it is two times faster than conventional spinning disc drives, it was set up as a system drive C: drive. On the downside though, my system drive has been filled up full within just a year, in spite of my efforts to direct all software installations away to the secondary drive.

The software has occupied the entire C: drive so it wasn’t any space left even for regular Windows updates. I had no other choice but to rethink my software environment architecture, clean the system and find a way to protect the hard drive from overflowing again.

While looking for solutions, I had to consider:

1. Keep various software installations away from C: drive.
   No matter how hard I tried to set custom installation folders to a drive separate from system drive, I stumbled over:
   1. Directing even some native Microsoft software, such as Visual Studio – it was wired to be installed onto the C: drive only.
   2. Controlling the size of notorious Windows volume shadow copy, C:\Windows\winsxs folder, which grows with every package version installation, and in no time occupies dozens of GB, locking it pretty useless!
2. Keep development environment for each of my client separate. This includes separate client databases and environment settings, as well as additional software such as different VPN clients, to make sure their versions upgrades don’t interfere with each other.

So, I came up with the list of virtual environments:

• For every client BI development
• For BI development testing environment (including latest SQL Server with samples)
• For Microsoft software (vs. BI) development environment (I decided to keep it separate because I rarely have a need to use the Microsoft Visual Studio development environment and didn’t want it to occupy extra space on my handy virtual machines)
• For home business (including MS Office and MS Outlook Business Manager)
• For packages conflicting with current operating system – I could only install them on Windows 7 through Windows XP mode (my 2005 Canon Rebel camera software happened to be one of them)

I’ve created virtual machines for Microsoft Virtual PC and used them for a year. Virtual PC approach addressed my needs in general though limited my guest operating systems to 32-bit.

For 64-bit systems such as Windows Server 2008, I rescued to Virtual Hard Disk (VHD) native boot. VHDs allowed me to boot an operating system with native file system support from a single file containing Windows image. With it, I was able to:

• Keep system completely separate from C: drive;
• Have isolated environments for my various needs;
• Allow easy environment copy and backup.

But there were couple of downsides related to using VHDs:

• I couldn’t benefit from my rapid hard drive. My solid-state ultrafast 50GB drive wasn’t not big enough to fit native operating system and VHD images. So I had to load VHDs from the secondary regular disk, which defeated the purpose of having the fast-speed drive.
• I had to reboot each time I needed to switch environment. I couldn’t multi-task between virtual machines.

Because of these limitations, I still preferred using Virtual PC machines, where I could run specific virtual machine liberally when only necessary. That matched my work style better.

Then a disturbing issue drove my decision to search for a better solution. It was a problem with screen adapter within my guest virtual machines. When I just edited a line in SQL query in SQL Server Management Studio, the screen refreshment took three times before allowing me to proceed. Virtual Machine’s Windows Action Center indicated that display adapter was incompatible with the ‘current Windows’. That meant that the guest 32-bit Windows on virtual machine display driver didn’t match to 64-bit host’s natural video adapter, and triggered the excessive screen flashing while affecting my performance.

While being a truly Microsoft follower, this time it occurred to me that I should try VMware software for my virtual environments. Free VMware Player allowed me to solve screen issues, use 64-bit guest operating systems and increase their performances.

To keep my existing virtual environments, I converted virtual machines from Microsoft Virtual PC to VMWare format:

1. Downloaded and installed VMware Player.
2. Opened existing virtual machine (created for Virtual PC) in VMware Player, with option of ‘shadow copy’.
3. After initial conversion, there were problems running the file. I fixed that with VMware vCenter Converter Standalone Client. I ran the conversion of virtual machine created by VMware Player in step #2.
4. Ran converted file with VMware Player.

I must say now I really like working from VMware Player’s virtual environments! In addition to benefits I’ve already extracted previously with using Virtual PC, I now enjoy

• performance improvements;
• faster loading speeds;
• not having flashing screen problems;
• ability to run both 64- and 32-bit operating systems;
• having several virtual machines ran at once (due to better performance and memory allocation); that allows to connect one VM to another to share resources and emulate production environments.

If you liked this article, you might be interested in: How to communicate OLAP Cube Design Decisions to Clients, #1 Productivity Shortcuts for SQL/ETL Developers, SQL Server 2008 Enterprise vs. Standard Edition: Seven Benefits for Business Intelligence, Resources for BI Developers.

Remember the 80/20 rule

For us, database and data warehouse developers, this rule is:

80% of the time we use “SELECT FROM table” query, and optimizing its call is very easy!

SELECT * FROM table… We type this statement over and over again, with our favorite typos like WEHRE or SELETC and corrections and then make awkward pauses to recall what we wanted to achieve by the query.

In the article Custom SSMS Shortcuts for ETL Developer. Part 1: SELECT in a Keystroke published with SqlServerCentral.com, I suggest you wrap the SELECT statement into a keyboard shortcut and save yourself 9 of 10 seconds usually spent on typing the same command by hand!

Also highly recommend: under the article’s discussion, find great recommendations of alternative approaches, such as use of SSMS Tools Pack, an add-in for Microsoft SQL Server Management Studio (SSMS) 2005, 2008, 2008 R2!

Ability to access metadata quickly is second important area of improvement in ETL development.

This idea has stronger focus on ETL development and intended for those who, like me, use the Ralph Kimball’s Data Model Excel worksheet for data warehouse modeling. The worksheet’s macro writes data mapping info into tables’ extended properties.

I use this metadata often as a reminder of where the data is coming from and what transformation rules are imposed. See in Custom SSMS Shortcuts for ETL Developer. Part 2: Extended Properties how to access the metadata fast.

Finally, I’d like to assist data warehouse ETL developers with a code that automatically generates data transformation queries. The resulting queries pull data from sources and convert it according to star-schema transformation rules.

The code obeys the best practices in data warehousing, primarily presented by the “father of data warehousing” Ralph Kimball and his group.

Please review the instructions I published on SqlServerCentral.com on how to automate Source-Target Mapping. Feel free to take part in the forum discussion as well!

I generate an Excel spreadsheet with lists of measures and dimensions and their properties and present them to my clients for reconciliation. They review the definitions and suggest names that are more intuitive for the business. They also suggest which attributes are unnecessary and can be eliminated. Then we decide which attributes will only be used as descriptors but never for browsing or filtering, that allows me to optimize them so that OLAP cube performs faster.

To generate the report, I use SQL Server Reporting Services to read OLAP cube metadata. First, I populate the metadata with a program called OLAPMetadataExtract, which is based on Microsoft AMO Samples. It reads OLAP cube definitions and writes the data into a separate OLAP_Metadata database that later I use for metadata reporting.

These workbooks are a great communication tool with clients at the beginning of a BI project development. They are also valuable for product documentation; I regenerate them when data warehouse structure is finalized. This guarantees that definitions are accurate and up-to-date.

1. Requirements gathering
2. Dimensional modeling
3. ETL and OLAP development
4. User acceptance testing (UAT) support
5. Production support

Business Intelligence Roadmap cart highlights major BI tasks and their relative durations:

As you can see from the chart, ETL and OLAP development consume most of the time in the BI project, because of so many tasks involved: physical design considerations, data consolidation, validation, cleansing, deduplication, performance tuning, just to name a few.

And yet, requirements gathering, dimensional modeling, and UAT support are the determining success factors of each BI project, as was recognized by many experts in the area and proven in my own practice:

• Requirements gathering includes drawing vertical and horizontal lines of organization, interviewing various stakeholders, and finding common ground to make sure BI solution is aligned with the enterprise strategy.
• Dimensional modeling provides the rest BI development processes with an exact plan of addressing data discrepancies, and sets the best practices guidelines.
• UAT presents developers the last chance to fix missing links and make adjustments that make customer comfortable with the product.

Production starts with deployment and followed by monitoring of the data processing, troubleshooting of earlier hidden problems (which might include surprises coming from new data streams), and user queries performance optimization.

Note: The processes shown in the chart aren’t linear. In practice, most of these stages are repeated iteratively. For example, I always start ETL and OLAP development before completing the dimensional model: I quickly generate a ‘proof-of-concept’ model, share it with future users and start receiving their feedback. The approach of involving business community earlier in the process helps aligning solution with business requirements.

First, consider the costs of two products:

• Standard Edition – $7,499 per processor
• Enterprise Edition – $28,749 per processor

By looking at the differences, many of my clients at the beginning were reluctant to pay 4 times more for the Enterprise Edition when Standard Edition seemed to provide all the basic features:

• Relational database engine for data warehouse
• SSIS for ETL packages
• SSAS for OLAP cubes
• SSRS for reporting

The problems with Standard Edition appear when data volume grows. In the most cases, the Standard basic functionality only lasts for 3 months or less starting from production date. By that time, you might start hearing your users’ complains about their reports running slowly, and notice other problems like the immensely grown processing time hitting the time limit dedicated for data processing. At this time you urgently need software upgrade.

Differences between Enterprise and Standard Editions

My clients often asked me to explain the difference between these choices in my own words. So I put together 7 important features in the following comparison table with my commentaries.

Features that only Enterprise Edition provides:

As you can see, if you’re serious about your BI/DW system, most likely you’ll need to obtain the Enterprise Edition sooner or later.

When to Make Transition to Enterprise Edition

Although you might work with the Standard Edition for a while, it’s highly beneficial to start the BI/DW development on Enterprise Edition as earlier as possible.

Note. You can start your BI project using the Development Edition (it provides the same functionality as the Enterprise) and have Enterprise Edition ready by the time you’re ready to move your system to production.

By early incorporating important features such as partitioning and change data capture, you can avoid expensive phases of rewriting ETL packages.

Tip. ETL developers can implement the logic once in a template SSIS package, and then use it as a basis for the rest packages, thus minimize efforts and keep packages uniformed.

What objectives can business intelligence help me achieve?

Business Intelligence for the Strategy-Focused Organization
Is Building a Data Warehouse the Right Solution for Your Business?
Why Building a Data Warehouse Is Not the Right Solution
Business Intelligence: Case Story
CPG Marketing and Sales Focusing on the “Single Point of Truth”
Need for Simpler Integrated CRM
Even small Improvements in data accessibility lead to big returns in revenues
Canadian Corporate Productivity Is Suffering Because of a Lack of Investment in Innovation
Evolution of Data Warehouses for the Next Three Years

What business intelligence platform to choose?

SQL Server 2008 Enterprise vs. Standard Edition: Seven Benefits for Business Intelligence
Packaged BI vs. Custom Solution
Business Intelligence Platforms are the Fastest Growing Software Markets in 2010 – Gartner Research
Microsoft Position on BI Market
Microsoft’s Next Generation Federated Data Warehouse Platform

What does a business intelligence project involve?

Five Major Activities in Business Intelligence Project
How to communicate OLAP Cube Design Decisions to Clients

• Watch Business Intelligence Webinars provided by Pragmatic Works
• Get to know the Report Builder
• Learn How to communicate OLAP Cube Design Decisions to Clients