February 28, 2006 at 2:10 am
Hello,
I have a dimension that represents the organizational structure of the company. It's a parent child dimension, with the group of companies as first member, and it divides into activities, companies and divisions.
This organization changes every year due to business needs. In the profit analysis, that queries the Analysis Services, the structure shown for each year must be the corresponding to it, and not the last one.
So I have implemented a table for companies with the year and company ID as primary key. And this table is the source for the dimension. It also has a field with the parent element, and another field that indicates the code of the same company for the last year.
So, you can see that there are two hierarchies for the dimension (the organizational view, and the year dependency).
The problem becomes when it's needed to calculate the result of the previous year for each company. The cube uses the organizational hierarchy, but in the calculation it's needed to obtain the the parent of the member in the time dependency hierarchy.
I don't know how I can make an MDX formula to obtain this calculated cell.
Anyone has solved this kind of trouble?
Thanks in advance,
--Toni
March 3, 2006 at 8:00 am
This was removed by the editor as SPAM
March 7, 2006 at 10:54 am
At the end, I’ve decided to add a field to the table, which contains the index to the previous year for each company, in the format [year].[activity].[company].[division]. I don’t find this solution very clean, but it’s the way to achieve my purpose. In the dimension, this field is added as a property. So, later, in the application for the profit analysis, this property is used to query the cube to obtain the result of the previous year.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply