.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Calculated members in dimension are not filterable in reports

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server
 

Hi all,

I have created a time calculation dimension (based on David ) with calculated members. When I work in Management Studio, I can select the dimension called [Date Calculations] and use it in the query. The only thing is, I don't need everytime all the available calculations, so I want to restrict them by selecting a few.

This is what I have done in the query below, but when I want to run this query I get an error:

TITLE: Microsoft SQL Server 2008 Analysis Services
------------------------------

A set has been encountered that cannot contain calculated members.

------------------------------
BUTTONS:

OK
------------------------------
Query:

 

SELECT 
 NON EMPTY { [Measures].[Stock Value], 
       [Measures].[Stockquantity], 
       [Measures].[Nr of products] } ON COLUMNS, 
 NON EMPTY { ([Date].[Year - Month - Date].[Day].ALLMEMBERS * 
        [Date Calculations].[Date Calculations].[Date Calculations].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM 
 


View Complete Post


More Related Resource Links

Please Help with Defining Calculated Measure based on Dimension Members

  

Hello,

The issue looks pretty simple yet I got stuck. I want to define the measure [NR Var] as [NR]-[F NR] for years/months/dates before 2009 and [NR]-[FI NR] for 2009 and on. I am using [Year]-[Month]-[Date] hierarchies in the cube. I have defined scope:

Scope ([Measures].[NR Var], {
descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2006], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2007], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2008], 2, self_before_after)});
this=[NR]-[F NR];
End Scope;

but it does not work for some reason. If I get rid of DESCENDANTS function, it works but applies the scope only to the YEAR level. Another problem with using SCOPE is that it affects [NR Var] only yet I have other calcs derivative of [NR Var] which I want the scope to affect as well.

So I ideally I would like to have something like:

 

CREATE MEMBER CURRENTCUBE.[Measures].[NR Var] as

case when {
descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_befor

Excel 2010 - Calculated Members on Dimension

  

Hi

Was wondering if there was any workaround in Excel 2010 to make these appear. My understanding is that the behaviour is unchanged from 2007.

Does anyone know of any plans to support calculated members on dimensions in Excel?

Cheers


Sorting calculated members in dimension

  

Hello. I've created a calculated member in dimension member.

My dimension member have three values: Item A, Item B and Item D. My calculated member is Item C (Item C = Item A + Item B). When I browsing the cube this calculated member is shown at the bottom of the list.

Is possible to specify a sort order in MDX for a calculated member? I use a SSAS 2008R2.

Thanks in advance.


overriding "grand totals" for calculated members

  
hi all, is there a workaround for overriding "grand totals" for calculated members?  (I cannot use the workaround whereby I create the calculation in the DSV or view directly due to it being a pretty complex Descendant parent/child calculation).   thanks much for any thoughts, Cos

SSAS - How to get other members from dimension that has Parent Child hierarchy?

  
I have a Sales Territory dimension that has employee and parent employee attribute on which parent child hierarchy is defined and it gives below hierarchy while browsing - - Mark Rolls --- Lumin Jacs ----- Larry Gomes ------- Messica Owens ------- Tom Ted ----------- Jackson Lopez ----- Matthew Ron --- Fred jacob - Jason Ron --- Jecy Pedro   But beside this parent-child hierarchy I have other attributes like employee address, email and telephone. My facts related sales transaction is tagged to lowest level. For example here facts are available only for Jackson Lopez (being Field Executive). When use below query I get complete sales reporting hierarchy result with some measures like sales amount, sales volume. But while accesing other attributes like address or email, it's repeating address/email/telephone of jackson Lopes everywhere to whom fact record is linked, Actually I want address/email/telephone of each sales employee from that dimension within hierarchy. How do I get it? The query I used is: Here Parent Terr ID is parent child hierachy. SELECT ( Descendants( { [Dim SalesRegion].[Parent Terr ID].[Employee Level 01].&[538018] /* here Mark Rolls is 538018 */ }, 0,AFTER), NONEMPTY([Dim SalesRegion].[Emp Address].[Emp Address].Members), NONEMPTY([Dim SalesRegion].[Emp Email].[Emp Email].Members) ) ON ROWS , { ([Dim Date].[The Year].[The Year].[CY-2010], [Dim

Long list of calculated members

  
·         I have a long list of calculated members. How can I make this long list user friendly like categorizing them by functionality? Can I create measure group for calculated measures?   Alex  

MDX query performance is slow for complex logic implementation in Calculated Members.

  
We have implemented a calculated members which involves multiple calculated members  called in a nested e.q. Cal1 calls Cal2 and Cal2 calls  Cal3  and Cal3 calls Cal4 .  And when browing Cal1 through Excel it takes around 30 mins for getting result from a Partition having row count around 5 million. Follwing are the information on the scenario I am having: We are having partitioning in the cube. one partition contains around 4-6 million records Processers on Query Server: 4 quad core RAM on the Query Server: 64 GB Calculated members having simple calculation logics are giving results in no time. There is a Calculated members which calls 6 intermediate calculated in nested way and with small Data Set like 5-6 k rows it is running fine.   Any help will be much appreciated.   Regards, Sandeep

Use of Calculated members from one fact into the other fact MDX

  
Hi, I have a FactBookvalues which calculates the Book value of several aircraft. this book value has a startdate going fro example from 1980 up to inlcuding 2030. With this MDX statement im able to determine what the book value is for a specific month: SELECT {[Measures].[Asset Bookvalue balance]} ON 0,   [MSN].[Asset Label].allmembers on 1 from CMSX_DWH_OLAP where {StrToMember("[Book Value Start Date].[Year - Quarter - Month].[Month].&["  + Format(now(), "yyyy") + "]&[" +  Format(now(), "MM") + "]")}   this work great in SQL server management studio, it will list all aircraft with the bookvalue for that particulair month.What i want is to get this value for that specific date and use this value as a fact for the AssetFact. how is this possible? i tried to make a calculated member but this didnt work. Im a bit stuck. I dont want to get this bookvalue from the ETL process but from the the cube itself to see how you can reuse dataelements in other areas of ssas

Replace pre-calculated Measures at "All" Level depending upon the Dimension Hierarchy Level

  
Have an interesting Challenge; thought will reach out to you if you can help Currently I am working on SSAS 2008 OLAP Cube, whereby most aggregate level measures are pre-calculated using the C# Code as part of ETL and are stored in Data Warehouse as well as Measure Groups in Cube (MOLAP). These are mostly non-additive measures have to choose this approach for performance reasons, considering the complexity of the aggregations and data volumes.  Data  Volumes are Huge (about 300+ Million Rows/ per day),  98 % of measures are non additive / semi additive.  Cube is used primarily for advance analytics and will be eventually used for data mining like time series , what if analysis and scenario analysis  Excel is used as front end . Question is how can we replace the aggregate level data for various dimensions attributes (Totals and Grand Totals) from pre-calculated measures, those are also available in the Cube as measure groups? We are currently using Scope and Root statement which is not working as expected SCOPE ([D1].[H1].[A1], M1) Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A1],  > EndScope; SCOPE ([D1].[H1].[A2], M1) Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A2] > EndScope;   Thanks in Advance   Best Regards,   Dave

Sorting the list of attributes (not members) of a dimension

  

Hi there,

Any solution with SSAS 2008 R2 for that problem?

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/ef9e51ea-0ccc-46b1-b789-38e39f961287

"It looks very simple, but I cannot find how to order the list of attributes in a dimension. Let's say I have a dimension Item with two attributes:

Item
- Code
- Description

and I want the Description to appear first. By default it's using the alphabetical ordering but can I change that ?"

It works fine with measures. In BIDS, CubeDesigner, there is are up und down buttons in order to move measures up or down. In the attribute pane, these buttons are inactive.

any help?

Peter


Creation of two calculated members

  

I have set of data for which I would like to create two user defined measure members which could be used when browsing the cube or when reporting using SSRS. I'm a MDX novice and really struggling with how to go about it (coming from a T-SQL background!) so will try to explain my problem based on the Adventure Works Cube. 

The first of the two measures is to get a result back which is the most recent non-empty record of a certain type prior to the currently browsed date.

So using this data from AdventureWorks as a good example:

(Filter: <Internet Sales Order Details.Sales Order Line> = 1)

(Filter: <Sales Reason.Sales Reason> = Manufacturer, Other)

 

Miscrosoft SQL Server Analysis Service does not show conditions and calculated members

  

Hi,

I am using MS visual studio 2005 to modify a report. The reports has got some filters (conditions) and calculated fields but when I open the report, all it shows me blanks values in condition window screen (where it displays Dimension, hierarchy, Operator, Filter Expression, Parameter fields) and also it does not display any calculated fields in the calculated members widnow. 

I am using SP1. Do I need to re-install the software or am I misisng some tool to turn on.

Thanks in Advance

Naveed


Select other dimension members automatically

  

All,

I'm trying to figure out if something is possible. Assume a simple fact table of sales $ and 2 dimensions, customers and products.

The requirement is, when a customer is selected in the customer dimension and a product is selected in the product dimension, any other customers that have sales for that product would also be selected. So if I select John Smith and product 1234, anyone else that has purchased 1234 will also be selected in the customer dimension. Is this possible? I don't know a ton about mdx, I thought it was only for queries, but are there say an event I can capture when the user clicks on a customer and run some mdx to select other customers that have also purchased that product? Or some other way if possible.

Thanks in advance.

 


Using calculated members in rows

  

Hi guys

Is it possible to use Calculated Members in rows? I'm trying to alias my column name by using members on the rows axis. Take for example the following MDX query:

 

 

SELECT {

 

ON COLUMNS

,

{

(

[Dimension].[Hierarchy].[Hierarchy].

 

ALLMEMBERS

)

}

 

DIMENSION

Named Sets vs. Calculated Members and the Filter Field Conundrum (Year-to-Date measures by company c

  

I’m going to describe our SSAS solution a bit and what I have in it so far in order to ask the question regarding “year-to-date” sales. This is part example and part question, I suppose. "The Filter Field Conundrum" sounds like it should be a high-tech Encyclopedia Brown case or something. Can't seem to find a similar situation through searching, although perhaps I'm wrong and this has been done before.

We have a data warehouse that contains our transactional detail for the last nine or so years. Tables are somewhat analogous to the AdventureWorks solution, with a table for Sales, a table for Customers, Geographic information, etc. The data warehouse has a time table that contains many of the usual bits of information one would expect:

Full Date (mm/dd/yyyy)
Month Number
Month Name
Day of Month
Day of Week
Day of Week Name
Day of Year
Week of Year
and maybe a few other typical fields.

Then we also have:
Working Day (bit, with a 1 indicating that this was a scheduled working day according to our company calendar, and a 0 indicating that it was not)
Working Day of Month
Working Day of Year
(these fields indicate that a day is the nth scheduled working day, according to our company calendar, for a given month or year)

So, for example, 5/1

Modelling snowflake type schema as referenced dimension and unknown members

  

Hi All

I have a snowflake schema that has the following tables:

1. FactTransaction  

2. DimSecurity

3. DimSecurityGrouping 

DimSecurity.id joins directly to FactTransaction.securityid. The DimSecurityGrouping table is used to model security groupings for reporting purposes. Multiple groupings can be defined for the same security. The layout of the table us such GroupName, Level1Name, Level2Name, SecurityNumber. DimSecurityGrouping.securitynumber join to DimSecurity.securitynumber (natural keys). Not every entry in DimSecurity has a defined grouping.

In BIDS I have setup a referenced relationship between DimSecurityGrouping and DimSecurity. When I process the cube I get errors saying "the attribute key cannot be found..." for records in DimSecurity without matching records in DimSecurityGrouping.

How can I ignore just these errors for just this scenario and not all errors on the cube when processing, or is there a better alternative?

Cheers


Modelling snowflake type schema as referenced dimension and unknown members

  

Hi All

I have a snowflake schema that has the following tables:

1. FactTransaction  

2. DimSecurity

3. DimSecurityGrouping 

DimSecurity.id joins directly to FactTransaction.securityid. The DimSecurityGrouping table is used to model security groupings for reporting purposes. Multiple groupings can be defined for the same security. The layout of the table us such GroupName, Level1Name, Level2Name, SecurityNumber. DimSecurityGrouping.securitynumber join to DimSecurity.securitynumber (natural keys). Not every entry in DimSecurity has a defined grouping.

In BIDS I have setup a referenced relationship between DimSecurityGrouping and DimSecurity. When I process the cube I get errors saying "the attribute key cannot be found..." for records in DimSecurity without matching records in DimSecurityGrouping.

How can I ignore just these errors for just this scenario and not all errors on the cube when processing, or is there a better alternative?

Cheers


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend