.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

Select other dimension members automatically

Posted By:      Posted Date: October 05, 2010    Points: 0   Category :Sql Server
 

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.

 




View Complete Post


More Related Resource Links

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

How to Create an MDX Query Parameter to Select 30 Values from a Dimension?

  
Hi, I'm using SSRS 2005 to report on an SSAS cube that contains a Procedure dimension.  I don't need to use members of this dimension in my report, but rather need to select records (patients) where their chart has one or more of the codes.  I've researched this today and cannot locate the best approach.  Thus far, I've attempted to create an MDX query parameter as part of my dataset.  However, I don't know whether this is the correct approach, and how to structure the syntax so that only records with one or more procedures are included in the report?  If so, what is the proper MDX syntax for setting my Procedure code equal to the query parameter? Thanks, Sid

Avoiding a SELECT distinct query generated by SSAS when using dimension derived from fact table

  
Hi, I am using a dimension derived out ot a fact table and the factt able primary key is dimension key. Issue is, there are large number of rows and so many attributes. SSAS issues distinct query and it takes large amount of time. Without the distinct statement, query takes only 3 min for 4 million rows. With the distinct, it takes 20 min. Becuase the fact primary key is the dimension key there is no need of a distinct statement. I know there is a option in the dimension to say "By Table" to avoid this. But unfortuantely, i breach the 4 GB limit for strings. Any suggestions for optimization? Thanks,  Sambath

Automatically select item with keyboard navigation in a combobox

  

Hi there,

in a WinForms ComboBox the selected item automatically changes, when I navigate through the combobox items with keyboard.

However, in the WPF ComboBox (IsEditable = false), the item in the list is highlighted but not automatically selected. Is there a way to change this behaviour?

Thanks

Jus

 


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


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

Calculated members in dimension are not filterable in reports

  

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 
 

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


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


"Hide" dimension members

  

Hi all,

I have a sales cube and a stock cube that share a store dimension. The store dimension has some extra members for "phantom" stores that are used for storing stock which I don't want to be visible in the sales cube. For example so that they are not visible in Excel.

I've tried to use security in combination with a role, but it makes it difficult to develop reports.

I've also created a specific stock location dimension and limited the store location just to "true" stores thus having 2 dimensions which works, but this then affects a cube I have that combines sales and stock.

Is there a better way to do this? For example can I do anything in the cube script?

Many thanks,

Matt


Auto filter related dimension members with Many-to-Many dimension

  

I have designed a many-to-many dimension which joins to main fact table using a mapping table that has keys from two other dimensions.

I am using all three dimensions (i.e. many-to-many dimension and other two dimensions) in one of the MDX query. I want the members of other two dimensions to get filtered automatically based on member selection in many-to-many dimension. Please let me know how to achieve this.

Thanks in advance


A query for selecting on dimension members

  

Hello,

Is there a way to write a query for selecting only dimension members without any measures? I'm trying to select the members by using "ON 0", but then I get a result containing 1 row and 0 columns and I want to get a list of the members, like what I get when using Excel and dragging a hierarchy / attribute to the row labels section.

Thanks.


mdx select dimension value

  

Hi all,

i use the sql RS 2008R2. And now want to do a calculated member with iif statement.  But  MDX says that my code is invalid. i don't understand why. I already try all possibilities I find on the net, same bug. Please help me! I am very desperated..

 

Here is my code.

IIF([0GL_ACCOUNTINT].[LEVEL01].AllMembers.properties("MemberCaption" )=[0GL_ACCOUNTINT].[INT 1000000                     0HIER_NODE], [Measures].[7W2ESQFWHRIX0QSCGCBR2L7ZR]- [Measures].[BV06E2UYWYCBMXDZOW104GHOB], 0)

another variant: IIF([0GL_ACCOUNTINT].[LEVEL01].CurrentMember.name = "Meeting", [Measures].[7W2ESQFWHRIX0QSCGCBR2L7ZR]- [Measures].[BV06E2UYWYCBMXDZOW104GHOB], 0)

another variant: IIF([0GL_ACCOUNTINT].[LEVEL01].CurrentMember.name = [0GL_ACCOUNTINT].[INT 1000000                     0HIER_NODE], [Measures].[7W2ESQFWHRIX0QSCGCBR2L7ZR]- [Measures].[BV06E2UYWYCBMXDZOW104GHOB], 0)

 

but all failed!! Thank for each idea!


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.


AJAX GridView Checkbox Select All in ASP.Net

  
In the previous article GridView Select Multiple Rows you learnt the multiple row selection in GridView control. Here you will get the C# code sample to select all the gridview rows using single checkbox server control. You can highlight the selected rows with different color and Font state Bold. If you want to use the selection values on different ASP.Net

Select value in gridview

  

Hi, I am new to gridview. I want to make a gridview. Its row when selected should direct me to a specific .aspx page.Each row should direct to a different page. Should I use querystrings for this? Please suggest. Regards, ap.


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