You can do ROLAP with OLAP Server, but they are looking to build a
relational star schema not ROLAP, MOLAP, or HOLAP. I agree, I would always
try to find a tool that works over building it. And after re-reading the
original post I strongly recommend against doing anything that involves
users writing SQL against your database. Perhaps a MOLAP cube w/ Excel2K
would do the job required here, and it would be fairly easy to implement.
> Please correct me if I wrong, but it is my understanding that OLAP
> also supports ROLAP ... I would not necessarily disagree that to go the
> Services route they might need a front-end tool that is OLAP Services
> ... however, it seems that that just goes back to my original point about
> re-inventing the wheel.
> I seriously doubt that the cost of development and maintenance of a wholly
> in-house solution will favorably compare with using a pre-built solution.
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.
> > Using OLAP Services will result in a MOLAP cube, but it seems they are
> > looking to build a relational star schema. With MOLAP they would need to
> > a front-end tool that supports MOLAP or write a lot of MDX which is
> > way more advanced than what they are looking to do.
> > So to answer the original question....
> > When you query a Star Schema database you should always use all your
> > dimensions even if you are not restricting the results based on every
> > dimension. Each data element is described by all of the dimensions so it
> > proper to join with all of the dimension tables for your query. Of
> > always prefer to use a front-end tool like Brio or Cognos. But then
> > also prefer MOLAP anyway.
> > Bryant Salus
> > Data Advantage Group
> > http://www.dataag.com
> > > Bob,
> > > Unless I'm mistaken, it sounds to me like you are re-inventing the
> > By
> > > that I mean that you are building a data warehouse tool from scratch
> > rather
> > > than using something like SS7.0 OLAP Services or SS2K Analysis
> > If
> > > you use either of these, and build a cube, all of these "problems" are
> > taken
> > > care of automatically.
> > > Am I missing something here?
> > > ----------------------------------------------------------------
> > > BP Margolin
> > > Please reply only to the newsgroups.
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> > > can be cut and pasted into Query Analyzer is appreciated.
> > > > I have built a data warehouse that contains four dimension
> > > > address, owner, tax_account, taxlot. The "fact" table (we call
> > is
> > > > supposed to serve the purpose of linking information from the four
> > tables,
> > > > so it simply contains the four primary key columns from the four
> > dimension
> > > > tables listed above.
> > > > The fact table works great when linking ALL four dimension
> > > > However, when joining any two dimension tables via the fact table,
> > > > sometimes get a duplicate result set.
> > > > For example, if I link the tax_account and owner tables to the
> > > > table, I want to get one row back, but get two rows. This is what
> > > xref
> > > > table looks like:
> > > > owner_id tax_account_id address_id taxlot_id
> > > > 5 45 2 688
> > > > 5 45 3 688
> > > > You can see that the owner_id and tax_account_id are repeated,
> > because
> > > > there is more than one address associated with them.
> > > > If I build the query to include only the columns of interest and
> > > include
> > > > the "distinct" keyword, then I get the desired result set. However,
> > > are
> > > > trying to make the data warehouse as simple as possible to query,
> > > would
> > > > like users to be able to join any two or three dimension tables
> > the
> > > > xref table without having to use any special keywords like
> > > > Any suggestions? The only options I have thought of so far are:
> > > > 1. Build more fact tables, basically one for each possible
> > of
> > > > the four dimension tables. The user would then have to decide which
> > fact
> > > > table to use.
> > > > 2. Build views from the existing fact table. Again, users would
> > to
> > > > know which view to use.
> > > > 3. Make the users build queries that include the "distinct"
> > > > Any other suggestions???
> > > > thanks