Hi,
I've a good SQL and OO programming backgound but I'm rather a newbie in
analysis services and OLAP in general.
I'm starting a new project, a distributed application with an heavy part of
sales data analysis, and I think analysis services could save us a lot of
work and give us the chance to offer our customer advanced reporting
features.
I'm able of doing common reports with cubes and MDX expression but how can I
do if I don't know STATICALLY how vendors are classified in my sales data?
I'm going to explain.
I've a set of vendors each vendor can sell one or more brands (B1, B2, B3,
...). Brands are grouped togheter to create various group of brands (G1, G2,
G3, ...), a brand can be part of only one group or can be a standalone brand
(we can think to standalone brands as groups of only one brand). A vendor is
classified as sole agent if all brands he sells are included in a single
group, otherwise the vendor is classified as generic.
Example:
G1 = (B1, B2)
G2 = (B3, B4, B5)
G3 = (B6)
G4 = (B7)
If I sell B1 and B2 I'm classified as sole agent, if I sell only B4 I'm
classified as sole agent, if I sell B7 I'm classified as sole agent, but if
I sell B3 and B1 I'm classified as generic.
My report has to give out monthly sales data grouped by type of vendor.
Things are not too difficult but brands grouping is decided at runtime, by
the user when he ask the report! The same user should have the possibility
to define different groups of brands each time he ask the report!
I can figure how to solve this using SQL (every time a procedure fills a
temporary table with the right vendor classification...) but how with cubes?
My problem is that the grouping logic can't be defined statically.
Can someone give me some hints? Are there features or tecniques to solve
situations like this?
May be cubes are not a good way for solving this kind of problems?

Thanks in advance

np