Help with Cube Measures
-
I'm relatively new to AS but have managed to get a data mart, dimensions and
cubes up and running in AS 2005 (SP1).
I'm having difficulty trying to do something seemingly easy with respect to
measures.
One of my dimensions is Accounts. I have about 7 additional dimensions
including Time.
I have 5 measures. One of these measures is heavily used most all of the
time.
I need to filter this measure by the Account dimension to include only
measures >= <defined_number>.
The other measures should reflect this change also, but I also need to
analyze other dimensions with or without the Accounts dimension in the
crosstab.
I need this to be put in the aggregations, not a temporary dynamic
calculation.
Aside from doing this at the database level, is their a way to do this in AS
either with a calculated member, named calculation or other?
I've been trying for a few days but just can't seem to get it.
Please, any help would be great.
-Troy
-
Hi Troy,
Could you try rephrasing what you're trying to do because I can't see the relationship between the acct dim and the 'defined_number'??
Cheers,
Steve.
-
Sure. Let me try it another way:
Our "Account" Dimension is actually named "Domestic Companies". They import/export various commodities and there are some 450,000 distinct members which are, for this scenario, the parent company. Analysis is performed at the company level on other dimensions for this particular cube(s). They is an Export/Import and "virtual" cube combining the two. Although, that terminology appears to have been dropped in AS 2005
There are no heirarchies in this dimension. It's flat, right now. The majority of these companies have very small volumes and account for appoximately 15% of the total volume. The remaining companies contain the rest, which is very small compared to the total number of companies. What I need is a calculated member (or other means at the AS level) to be able to filter this list of companies, by year, who move volumes of 500 or more total.
So, if company XYZ exported any number of commodities totaling 499 in vloume (TEUS is the volume or measure. This is container volume in Twenty Equivelant UnitS) for year 2005, this would need to be filtered out when analyzing. If the company moved 502 TEUS total in 2004, it would need to be included, and so forth.
Right now, filtering at the client application level is not an option because it is to intensive an operation when bringing other dimensions into the crosstab.
That said, this cube contains other measures that, when filtering the TEUS measure, should also be filtered respectively.
What I've tried so far is using the IIF funtion:
IIF
([Measures].[Export TEUS] >= 500, [Measures].[Export TEUS], null)
This will filter the measue but all others are visible as EMPTY Members. Plus, if I drilldown from another dimension, say Year-->Qtr, if the child members measure value is not >= 500, it is displayed as empty. I want to see values at lower dimension levels regardless.
When analyzing for this cube, all other dimensions values should aggregate based on the total at the individual company level, just filtered.
Does that make sense? If you need more info, just ask.
-
I found the solution to this problem using the new aggregation Functions in SQL Server 2005. Thank you SQL Server Magazine (June '06)!!!
I used the OVER clause with PARTITION BY my_company_id to created a new named query in AS 2005. It works beautifully
Here is a sample for anyone else in the furture.
select
TimeID,DCParentID,DCLocID,USPortID,ForeignPortID,TradeLaneID,CountryID,CommodityID,ShipLineID,UltimatePortID,ExportShipments,
ExportTEUS
,ExportMTONS,ExportSTONS,ExportEstValue
FROM
(select TimeID, DCParentID, DCLocID, USPortID, ForeignPortID, TradeLaneID, CountryID, CommodityID, ShipLineID, UltimatePortID, ExportShipments,
ExportTEUS
,ExportMTONS,ExportSTONS,ExportEstValue, SUM(ExportTEUS)
OVER
(PARTITION BY DCParentID) AS total_teus FROM FactExports) AS t
WHERE
TimeID BETWEEN 25 AND 27 AND total_teus >= 500
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply