Data Source Pulls

We are pulling data from about 6 sub-systems and change management is hard.

At first we were using SMO to transfer across the tables into a pre-staging
area. However; any change whatsoever to the table structures break this
part of the ETL.

We believe creating views on the sub-systems is the answer - however we have
2 choices. Create the view on the sub-system or create the view on our
pre-staging area.

The pro's to having the views living on the sub-system:
* Any changes to that system, the developers of that sub-system would be
responsible for not breaking those views

The con's:
* Would require the DW folks to have access to the subsystems to maintain or
add to the views

Anyone else have some thoughs on this?

Joe,

You might not get the best answer on this here as that's more a data
warehousing type question, but I'll give it a whirl.

I'd say that unless the subsystem folks also used those views for
their own stuff, I'm not sure they have any incentive to keep the
wheels on the wagon. If something breaks, do you have enough pull (or
access to someone who does) to compel them to fix it?

Another thing to consider is that the subsystem guys, who again you
may not manage or have a lot of say in their priorities, they could
leave you hanging for quite a while. You're putting your fortunes in
their hands. I'd go for more control. That was one of the nice
things about SSAS 2005 is that they introduced the DSV concept so that
made it easier to apply easy, quickie changes. Some DWs required an
act of congress to get even a simple view created. DSVs are not as
flexible as normal views (they cannot refeerence other DSV objects for
example, which normal view can do), but they're good in many
situations.

You might also consider a "stale" cube strategy. That is, instead of
the cube just not getting processed if one of the views breaks, the
end-users are SOL, you might consider having a kind of contingency
cube. If the main, normal cube will not process, the end-users can go
to the one-day old cube. It's a bit stale, maybe one day, but usually
that is not a huge issue, and that would give you some time to address
the issue. The end-users would just use the stale cube until the
normal one was put back together. A special RS report that just
points to the stale one (same server in production, just a different
cube name). But if you're cubes are more mission critical and users
would freak out at the thought of old data, that's not going to fly.
Again, another thing that SSAS ought to have built-in...an optional
mirror of every cube that could be retrieved if the normal cube was
incapacitated.

One last idea...what if you wrote a simple table with a query that
just did a SELECT * (or list out each field to make it more robust) on
each of the important, cube-dependent views? That is, if any of them
comes back with a 0 row count, there's an issue. That would be a
piece of cake to implelement. If 0 rows came back, it could e-mail
you straight away. A trigger or even a simple app could provide
this. This would not prevent the issue form happening, but you'd know
immediately when a view went dark. You'd then have an additional day
or half-day to get it fixed up before the normal nightly cube builds
(assuming that cubes get built daily, which is typical).

Hopefully that makes sense...

-exologic

::: Pain with Essbase? Use CubePort, The BI Porting App :::
::: Go from Oracle Essbase to Microsoft Analysis Services! :::
::: web: exologic.com/products.htm :::