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


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

"Hide" dimension members

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

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




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

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 
 

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.

 


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


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.


Correct Time Dimension Settings to Hide Extra Years in Drop List

  

This is going to be easy for the experts to answer (I hope). Here is my problem:

1) I have a Fact table that is linked via a TimeKey (Not date, it is a number) to the Time Dimension table. (I want to build a cube in SSAS)

2) The Fact table contains data from 2008 to 2010 (YTD)

3) The Time Dimension Table includes years  between 2005 to 2015

My problem is:

When I drop years into a column (in the browser) I get the correct year range between 2008 and 2010 (which is correct)

but

At the same time, when I use the drop down list of the column I can see all the years from 2005 to 2015. (Which is included in the Time Dimension Table)

How can I make those "extra" years disappear from the drop down list? How can those years appear in the browser even though there is no matching "TimeKey" in the fact sheet that would link them.

I know this maybe a simple setting in the Time Dimension but I cannot find it.

Your help is greatly appreciated.

Thanks


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.


Hide a repeater in ASP.NET C# if the DataSource contains no items?

  
I have an ASP.NET page that uses a repeater nested within another repeater to generate a listing of data. It's to the effect of the following:
In the (C#) code-behind I'm basically using LINQ to pull a listing of information from an XML document and bind that information to the first repeater.

Searching for the answer to this, it seems the method is to determine whether the data for the nested repeater is empty. If it is, then you set the visibility of the repeater to false.

The number of members in the conceptual type ... does not match with the number of members on the ob

  

 Hello,

I've added a scalar property to an entity and now ASP.NET Dynamic Data scaffolding throws this error:

The number of members in the conceptual type 'TrainingModel.Users' does not match with the number of members on the object side type 'TrainingModel.Users'. Make sure the number of members are the same.

This happens at the following line in global.asax:
model.RegisterContext(typeof(TrainingModel.TrainingEntities), new ContextConfiguration() { ScaffoldAllTables = true });

Please help!
Thanks


How do I get the emails of all the members of a given group in ActiveDirectory - Need help

  

Hi,

  I have a need to send out emails to all the people who are in a given group say "HR Department". I need to be able to get all the emails through a for loop and then as it retrieves I should send emails to them. Can some one please help me with the code

this is what I came up with but not sure how to go about writing a for loop to get all the emails in the given group.

public static string SendEmails(string loginName)
    {
        string Department = "HR Department"
        string userName = ExtractUserName(loginName);
        DirectorySearcher search = new DirectorySearcher();
        search.Filter = String.Format("(SAMAccountName={0})", userName);
        //search.Filter = String.Format("(SAMAccountName={FAY ImprovmentForm})", userName);
        //LdapSearcher.Filter = string.Format("(&(objectClass=user)(department={0}))", Department); 
        //search.Filter = string.Format("(&(objectClass=group)(SAMAccountName=" + Department + "))", userName);
        search.PropertiesToLoad.Add("cn");
        search.PropertiesToLoad.Add("samaccountname");
        search.PropertiesToLoad.Add("givenname");
        search.PropertiesToLoad.Add("sn");
        sea

CLR Profiler: No Code Can Hide from the Profiling API in the .NET Framework 2.0

  

The common language runtime (CLR) profiling API makes available information about the application domains, assemblies, and classes that are loaded and used in a process, just-in-time (JIT) compiler notifications, memory usage tracking, tracing of events, exception tracking, managed to unmanaged code transitions, and the state of the runtime. And if that weren't enough, you will find a nicely enhanced profiling API in the .NET Framework 2.0. Find out what's coming up in this next version.

Jay Hilyard

MSDN Magazine January 2005


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