SSAS Cube Opinions

[keskens]

Any opinions, tips, tricks and/or potential pitfall warnings would be
appreciated...

I'm working on a BI project that would utilize an existing reporting
database that houses bits and pieces of our OLTP database. This
reporting database has roughly 40 tables that are related by a
ClientID. The main table has the ClientID as a PK and all of the other
tables have it as a foreign key. These other tables do not have any
primary keys. There are probably thousands of users (hosted and non-
hosted) that have existing queries to this reporting database so
structure changes really aren't an option at this time. Each table is
loosely organized by areas such as Income Statement, Income/Expense,
Balance Sheet, CreditScoringRating and General Client Data. The idea
to use this reporting database for cubing is because there is a short
time to market deadline (6 weeks) and also all of the field names have
already been transformed to "friendly".

Any opinions on using SSAS2005 versus SSAS2008?

Ideas on creating appropriate Logical Primary Keys in the data source
view for the 39 tables without a physical primary key? There really
aren't any columns that lend themselves to a logical primary key or
even a composite logical key. I thought about creating a named
calculation using NewID() and making that the logical primary key -
but I think I will end up with attribute relationship issues. Ideas?

Opinions on creating one large cube or a few small cubes? (idea here
is one cube structure deployed across multiple databases - the
reporting database is one structure deployed across multiple databases
with the largest being around 25gb and the majority being 5gb or
less)

Opinions on foregoing the cubing on this reporting database and
creating a dimensional warehouse from which to cube?

[Bill Harrison]

I would have a system generated primary key (hidden from users) for
each dimension. Yes, I would build dimensions, if I had the time.
Looks like you would have a nice fact table with all the FK's from the
dimensions. Sounds like you have plenty of facts (income, expenses,
etc.). This would translate readily into a cube. I use Cognos to
generate cubes, but the concept would be similar with other tools.

You would build database indexes on the primary keys (surrogates), and
unique indexes on the business keys.

If users insist on maintaining the old structure, create a view. But
now you have a full fledged client data mart.

Time constraints may not allow this approach; however, it CAN be done,
and we built one similar in 2 weeks with a team of 6. But we have
spent the ensuing 2 years enhancing it. If you don't have the luxury
of time, I would still do the surrogate key thing and just build a
cube from there, using the modelling tool you have available. Cognos
has the ability to build a "dimensionally modeled relational" model
and I assume other tools might have this functionality.

What you would do is create a new surrogate key for each table, using
an auto sequence generator that increments by one on an insert. This
would be the primary key. Then assign the foreign keys to the other
related tables.

Hope this helps. Good luck!