Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

200 Near Identical Data Source Views in SSAS 2012 Cube: How to advise your clients to take a better path?

Posted on 2015-01-19
7
Medium Priority
?
130 Views
Last Modified: 2016-02-18
Hello Experts,

Questions

What issues can creating 200+ views and avoiding MDX - when there are only 2 true fact tables – cause down the line?
How would you convince your client that their current path is not in their best interests?

Background

I’ve inherited an SSAS 2012 development from a previous contractor.

The cube models a sophisticated patient administration system; one would expect complex measures in healthcare.
Documentation: non-existent, Kimball nor Inmon are nowhere to be found, 200+ measure groups confuse the situation, next to no aggregations or partitions, and host of other elements.

Most worrying is that the prior contractor did not write a single line of MDX by employing the following strategy:
Create 200+ views based on two fact tables in SQL Server.
Ensure each view contains filters to drive a measure.
Add all 200 views are to the cube as a new data source view, and sum or count a column from said view.

Issue

Despite spending 3 weeks trying to explain the issues to business and technical users with no multidimensional, data warehousing experience, the client believes there is nothing wrong with the current cube. Never had this challenge before!

Have you come across a situation like this before?

Looking forward to the responses!

Kind regards,



JohnAeris
0
Comment
Question by:JohnAeris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40558378
My suggestions:
1. Don't criticise the previous person or his methods.
2. Talk about the advantages to your method (additional features at little cost?)
If the previous person's things break down, because something has changed:
1. Estimate the amount of time it would take to resolve it his way.
2. How much would it take your way (and additional features).

And if that doesn't convience them, then maybe there's nothing wrong with it for the end user.
0
 

Author Comment

by:JohnAeris
ID: 40559394
Hi Phillip,
Great points! The key challenge at the moment, is that creating a view from a fact table, adding it as as a dsv, then a simple count is seen as much faster than creating writing and testing MDX for instance.

I'm find it difficult to get across that this method will create issues.

Would it work if I created a list of potential pros and cons of the current method vs best practice?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40559399
I agree with you as to whether MDX would be better.

However, if the end users think that the current system works (if it ain't broke, don't fix it), then they will need a pretty good reason to do so. They will not care about "best practice"; they care about:

1. Does it do everything *I* want it to do?

and that's it. Put yourself into their shoes, and try and work with their point of view, and see if you can then make a good case for MDX.
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:JohnAeris
ID: 40559432
Client is currently at pre-launch stage.

In terms of more complex measures requiring MDX (which they will DEFINITELY require in the near future) they don't have a need for it yet.

There is a window of opportunity this week to create a solid foundation by re-jigging the cube and getting ready to play better with calculated measures.

Question: Phillip, what issues do you see with the current 200+ measure groups / views / DSV approach above? Perhaps, if I can list these, it will help make a case?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40559451
Three key points are:

Maintenance is the key one. What is something changes in the underlying structure of the data?
Adding additional features requires yet more views to be created.
Requiring the IT guy to do it (requiring additional time to do it) as opposed to being able to self-support (and doing it instantly).
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40559457
I would therefore not criticise the previous guy - it worked for them.

However, moving forward I have a new strategy.

The advantages of this strategy are: ###

The problem with moving forward with the old strategy are ###

My strategy makes things easier for YOU because ###.
0
 

Author Closing Comment

by:JohnAeris
ID: 40627991
HI Phillip,
Followed your advice.

Despite multiple conversations and examples and walkthroughs, a simple email showing my way forward vs pitfalls of moving forward with old strategy did the trick. #WhoDaThunkIt?

The eventual decision was to can the project for 6 months, re-prioritise and re-plan then, come at the project again with a fresh approach.

Not an altogether unexpected outcome, but the best way forward in terms of resource management vs quality: client would never have achieved their goal with the current platform.

Thanks for the advice!
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question