Hi,
I have the Standard edition of SQL Server 2005. I have created a Financial Reporting cube that has an Account dimension containing the company's Chart of Accounts. This dimension includes an AccountType attribute, which is used to determine whether each account is an Asset, Income, Expense etc.
In order to report the different account types differently over time, I tried to add Account Intelligence using the Add Business Intelligence wizard in BIDS. This works fine, however when I try to deploy the cube I get the error:
The Semi-additive measures feature is not included in the Standard Edition SKU
This is confirmed by looking at the features comparison for SQL Server - the Account Intelligence functionality is only available for the Enterprise edition. I am wanting to set up an Excel PivotTable to view this cube, but without defining semi-additive measures the results are meaningless.
Since we cannot afford the Enterprise edition, does anyone know how can I replicate this functionality
Cheers, Matt
Replicating Account Intelligence for a Financial Reporting cube
ourcupoftea
Hi,
I think you could create a MDX Script to aggregate the measures correctaly using the account type.
In AS2000 I used a Cell Calculation like these:
CREATE CELL CALCULATION CURRENTCUBE.[Balance] FOR '({[Measures].[vlr_cube]})' AS
'Iif([Account].Currentmember.Properties("accountType") = "Balance",
CalculationPassValue(([Measures].[vlr_cube], ClosingPeriod([Time].[Month Level], [Time].CurrentMember)),0),
CalculationPassValue([Measures].[vlr_cube],0))',
CONDITION = '[Account].Currentmember.Name <> "All"'
You could try to convert these Cell Calculation to AS2005, I didn't have time to do that yet.
The main ideia is to aggregate using the ClosingPeriod function when the account is a balance account.
PS: In AS2005 Enterprise Edition if you use the Account Intelligence you can't use the write-back :(, I have AS2005 Enterprise Edition but I need write back then soon I will have to build the same thing.
Handerson