I have a little mess on my hands… I have a universe (HR) with no fact table, no contexts. There are 6 tables - almost fact-like - with about 80-150 columns, 2 tables have 4MM rows, others have 2MM give or take. The rest of the tables are dim tables. All joins are outer joins. There are over 800 objects in the universe and all calulations are done within the reports, there are only 4 true “measures”.
Client doesn’t want any changes to the universe, yet, not happy with performance and won’t consider moving measures up to the universe or database.
Only way “out”, as I can see, is to rebuild the universe and “prove” certain realities… That being said, I am running into some performance issues on dim objects with no measures - taking 6-8 mins - anyone have any insight on how I can increase performance of dim only objects???
Well… I think I put my foot just about everywhere (no relationship building skills here!) and I’ll probably be out the door here in the next couple weeks! Quite tiring arguing with clients and even other consultants, particularly in line with what you have correctly identified.
Any suggestions on working in a DIM (not just the objects) environment would be appreciated! Just want any feedback prior to working nights and this weekend trying to convince others of something I would have thought was common knowledge, and I’m no rocket scientist myself.
Its quite a common feeling that usually occurs in the first week…
Had many when I have felt like walking on day two, never have though…:).
You need to be sure of what you are saying and why you are saying it.
i.e.
Why it is best to resolve SQL aggregates at run time.
Why it is better not to use outer joins, I mean, whoops, there go my indexes, if they had any in the first place :).
Why it is better not to just join things all together at the universe level, just because you can.
I believe that the “universe” was created this way to avoid having to deal with any actual universe design issues, i.e.Fan, Chasm Traps, Contexts, the list goes on.
Surely, you could just work on objects for one or two reports as your POC, as to why not to do it the current way.
Without any (or little) measures - what type of reports does the client expect - as far as I can gather from that snippet of info it’s going to lists rather than reports.
How performant and expensive is the database? are they happy ot have that sitting idle while the BO server or client PC (deski) crunch through those lists.
Throw in some RoI figures - are they really getting the performance that hey paid for? why not?.
Why not put together a simple PoC universe and show the performance of your query against that of the client query?
Thanks and YES… I apparently - well, not apparently as I was told I offended literally “everyone” when I asked who designed the universe… apparently someone here (still here but won’t come forward) tried, but there are some sensitivities and no one likes to be told they’re wrong (I’m in this group myself).
Thanks for the input… I’m not one to turn down a challenge, so I’ll burn the midnight oil on this one!
I think when I’m done I’ll go for a walk at the Duke of Westminister’s estate and play with the herds of deer that roam his property. I’m sure the armed security will understand when I tell them I work in IT and just needed a break!
Your accurate, however, the client does all of their “logic” in the query panel AND they actually want to keep it there… they perform all of their if/then else in the query panel and they feel if the move this upstream, they’ll “lose” something as the requirements will “change daily”… My 18th attempt at trying to move measure to the universe didn;t even get to the second word as the client blutterred out "No, I don’t want the measures in the universe cause I’d have to re-do them evertime!
I don’t think they really grasp the idea of BI, but trying to break that ice- berg daily!
Is there a sponsor for this project? I honestly don’t understand some people and organisations, why pay to get someone in and glibbly disregard advice? I mean they may as well do it themeselves…
…oh right, they did that and look where they are now!!!
Point them to BoB and suggest that you develop some training material, deliver the training, hand hold the users etc etc etc…
you could be made up for life in this palce if you play it right
A) Measure belong in the universe whenever possible, see also B)
B) All calculations should be pushed down as close to the source as possible, read: first database, then universe, as a last resort in the report. Why? Because of consistency, ToC (define once use many times, will save on maintenance), performance
C) You might have factless fact tables, these are tables where you count some columns for example. Also, your Dim tables are very wide, 150 columns, this will negatively impact performance on the database side (reading wide rows from the DB is time consuming). Ask to get a competent DBA with performance tuning experience involved.
Finally, you can bring a horse to the water, but you cannot force it to drink.
[quote:ebaa210380=“BOBUJ’s Customer”]No, I don’t want the measures in the universe cause I’d have to re-do them evertime!
[/quote]
They have to do them every time anyway.
Why not ask them if you can spend a couple of hours creating a new version of the universe and then show them how your version is better than theirs? Or just do it anyway.
Nice to be in a situation where, as abnormal as it can get, there are others that have run into similar situation, or can offer a well educated sounding-board to at least confirm perceptions, process etc…
I’ll update this in a week or so to let all know what has transpried
Thanks… and much appreciated. However, I don’t think you can have a “real” true life experience without pulling a couple feet from numerous parts of ones own, and others, anatomy!
This is the path I’m going down… univ off to the side, via the weekend work, etc…
(BOB member since 2008-11-19)
(BOB member since 2005-01-06)
(BOB member since 2004-01-29)
(BOB member since 2004-01-29)
(BOB member since 2002-06-20)