Using Pubs sample data base that comes with sql server 2000 I imported some of the tables into framework manager and published the package to CC, I only modeled 4 tables titles, publishers, sales, and stores, can't really model authors because you double count qty in the sales table if you key into authors with the cardinality the way it is, anyway would be cool to see what percentage of revenue is attributed to each individual author vs just revenues by titles.
Any creative ideas to include it in fm model ? I already have a few ideas on how to set up in RS.
sql below,
SELECT authors.au_id, authors.au_lname,titles.title,
SUM(titles.price * sales.qty) ,
sum(titles.price * sales.qty)* (titleauthor.royaltyper)/100 author_revenue, titleauthor.royaltyper
FROM authors, titleauthor,titles,sales
where authors.au_id = titleauthor.au_id
and titleauthor.title_id = titles.title_id
and titles.title_id = sales.title_id
GROUP BY authors.au_id, authors.au_lname, titles.title, titleauthor.royaltyper
order by 3
Hi,
How much training have you had in FM modelling? This should be easily achievable providing you understand a) how the cardinality of the relationships affects query generation and b) how to model new structures to reshape the data and relationships into a form that will provide accurate, consistent results.
If you can give us an idea of the current query subjects and relationships, we can most likely suggest what extra steps you need to take to get the desired results.
Regards,
MF.