newbie - defining dimensions.

I'm new to datawarehouse and needed some assistance in designing the cubes. Below is the scenario.

I have two tables.

tblbook with columns (bookid, author)

tblauthor with columns (authorid, authorname)

Now, my report should include the no. of books authored by an author and also include details of co-authors.

i.e. tblbook has the following values

1 mike

1 joe

2 mike.

3 joe

4 richard

my result should display

mike 2, joe 1

joe 2

richard 1

I would really appreciate any help.

You may have over simplified your example a little bit. In order to properly model this sort of situation I would have thought you would have needed at least 3 tables.

tblBook(bookid, book_name, publishing_date, ...)

tblAuthor(authorid,author_name, ...)

tblBookAuthors(bookid, authorid)

Basically it sounds like you need to set up a many to many relationship (one book can have many authors and one author can have many books)

There is an excellent whitepaper on some of the uses of many to many relationships here: http://www.sqlbi.eu/Home/tabid/36/ctl/Details/mid/374/ItemID/7/Default.aspx

And you should be able to find information in Books Online.

|||

Thanks Darren, i will dig more into the article and update you for any issues.