I've read "The Data Warehouse Toolkit" and "MDX Solutions" and all
white papers I could find online to help me design an drillable account
dimension.

Can someone please advise as to how this is suppose to be designed.
What I have is an AccountingDim and GLFact table that allows me to
generate my cube. The question is how do I design it so that I can
group the AccountingDim Codes into a hierachie that allows me to drill
into the fact. For instance,

AccountName AccountID
ProfitTypeA 100
ProfitTypeB 101
ExpenseTypeA 200
ExpenseTypeB 201

NetProfit = Sum(Profit) - Sum(Expenses)
I would want my cube to be able to show NetProfit, but when I click on
a cateogry called Profit, it contains ProfitTypeA and B. Then I could
drill down the ProfitA and ProfitTypeB.

I've read using a Bridge table is the way to go, but I don't see how
this could be processed in a CUBE, but rather used in SQL.

Please advise. TIA

If you need to write a single account hierarchy, you can solve it by
using unary operators on accounts you are aggregating.
If you need to create multiple hierarchies and/or you want to
reclassificate a balance sheet, then you have to go to a solution based
on a many-to-many relationship.
You can read my paper "The many-to-many revolution" here:
http://www.sqlbi.eu/manytomany.aspx
Unfortunately, your exact scenario is not described but it is a thing
I'm working on (but I have no time to update the paper soon). You can
found an interesting discussion on the model you need on the discussion
forum on the same website: http://www.sqlbi.eu/forum.aspx (go into the
Many-to-Many dimensional modeling area).

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

carmaboy (AT) gmail (DOT) com wrote:

Thanks... I look forward to reading your paper.

I must vent. MS wants the BI community to thrive, but when it comes to
resources, its almost impossible to find examples, concepts or even
help. The BI community is very small and it will be a momumental task
if MS wants BI to thrive in an area where you are either an expert who
doesnt have time to help or a novice who can't find any help. Thanks.


Marco Russo wrote:

AS 2005 also has a predefined "Accounts" type of dimension which may
help you - see the Account dimension in the sample Adventure Works cube,
which includes profits, expenses, taxes, etc.

http://msdn2.microsoft.com/en-us/library/ms174609.aspx

Account (SSAS)

In Microsoft SQL Server 2005 Analysis Services (SSAS), an account type
dimension is a dimension whose attributes represent a chart of accounts
for financial reporting purposes.

An account dimension lets you selectively manage aggregation behavior
across accounts over time. An account dimension also lets use a standard
mechanism to resolve most of the nonstandard aggregation issues
typically encountered in business intelligence solutions that handle
financial data. If you did not have such a standard mechanism, resolving
these nonstandard aggregation issues would require custom rollup
formulas, calculated members, or Multidimensional Expressions (MDX)
scripts.

To identify a dimension as an account dimension, set the Type property
of the dimension to Accounts.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***