how to write test cases for SQL Server BI project

how to write test cases for SQL Server BI project
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mu100Author Commented:
anyone...please reply. that will be great.
Mark WillsTopic AdvisorCommented:
Yep - back in a few... You have a couple of dupe questions (not your fault). Will leave a message in those other threads first :)
Mark WillsTopic AdvisorCommented:
Hi, sorry about that - there have been a couple of instances of a particular problem we are helping EE to track.

So, where were we :)

Ahhh, yes, Test Case for SQl Server BI.

Well, big call and very broad subject. Normally there is a few white papers from Project Management sites and BI sites. But assume you have already trawled a few of those...

In all honesty they tend not to take into account the business specific requirement - and that is the focal point for BI - to transform structured data elements into your business's perspective (and language). That is where a lot of test cases fail. Simply because they look at the heavily mechanical aspects and not the "business user" aspects.

The challenge really is it does need to include / cover a whole pile of things - normally focus on data quality - especially with date / time dimensions and any transformations as well as complete data. That can be the fairly mechanical part - building the datawarehouse - the ETL process.

Then we have aspects like the data warehouse design itself. How is it being built ? If a refresh / rebuild then a little more straightforward, but, what about incremental updates ? Is it robust and designed well enough to ensure no duplicate data, and even recoverability. Then there are aspects like how to handle historical data. For example a sales territory might be measured on the currently assigned salesreps, but, the salesreps commissions would be measured on the customer that they were assigned to at the time so now we have a concept of "historical" data relationships versus "current" data relationships and they can be different. Even the slightly more obscure aspect like a change in business rules (for example a commission calculation, or the definition of an invoicing period) needs to be "managed" properly and predictably.

The last piece of the puzzle is the end user interface and how well that delivers the business message. Sometimes this can be easy to reconcile / test in so much as there is often a baseline of transactional reports that can be used as a comparison. The risk is that BI is a heck of a lot more than a simple post analysis reporting syste,, and as we move more toward predictive analytics this task becomes more involved. The important aspect here is to get a few "what if" type targets accurately defined before hand so the real ad hoc nature of interacting with a Cube can be reasonably measured with known and predicted results. The interactions of dimensions and measures are fully exposed in the hands of the user and needs the different combinations tested to ensure uniformity of data across the different presentations (e.g. do territory or department queries report the same totals as say an all customer query of the same measures for those areas, or is "privileged" data adequately protected).

So, when we embark on these types of projects. Normally initial approach is to build a small subset of known data having sufficient variation the adequately test the model. It is not foolproof and often leads to a more complex review, but if sufficiently real life and absolutely "clean" then dealing with predictive results is significantly easier than trying to hunt down a larger more involved suite of data relationships. It also serves the purpose of testing the user interactions and make sure the terminology and dimensions deliver the required results to the target users.

I am sure / or hope that other experts will chime in and give you some sample cases or a few links for some documentation. For me, I really do prefer to sit down with a few spreadsheets and do the very hard yards of enumerating a number of tests and the data required to adequately expose the business requirement.

Megan BrooksSQL Server ConsultantCommented:
Lots of issues; not a lot of answers. One good principle: keep it as simple as possible.
As a development/QA/deployment "team" of one, I have a difficult time maintaining accuracy. I have considered unit testing and such, but it is unlikely that those things would have caught more than a few of the issues that have actually turned up. I just fixed one yesterday where a minor change in an RS custom assembly required a change in a report parameter and the parameter hadn't been changed in one particular report that isn't often used. Everything seemed to be working individually but that one report was not showing all of the data.
One thing that might help in many situations is to compile a set of representative test reports that return data in XML or CSV form and then, after making changes, run those reports and diff the data against known good reference results. That could actually catch many of the issues I have encountered. But the test reports and reference data would have to be updated as things change, and that increases the chances of bad reference data creeping in. It could still work well for regression testing, and regressions are one of my biggest problems.
Mark WillsTopic AdvisorCommented:
Just read an interesting article (well a different one which lead to the following link)...

Suggest 98 steps for a datawarehouse design checklist :

(need to follow the link at the bottom of the Article, and it might require registration).

What it shows is the type of complexities that might come into play....


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.