.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

Referenced dimension on referenced dimension - second

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
 
Hi all In a previous post I have asked if it is possible to "nest" referenced dimensions. The response is yes, see link bellow:http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/2cd75f83-313d-44c0-853b-0302ac864943After having implemented itPath ServiceLine <- Organization <- Employee=> Employee is directly connected to the fact table => Organization is connected to Employee over the codeblock=> ServiceLine is connected to Organization over servicelineOn the Dimension Usage Tab When I click on the intersection between Employee and the measure group everything seems OK.When I click on the intersection between Organization and the measure group I receive the following warning message: The ReferenceMeasureGroupDimension X will be deletedWhen I click on the intersection between ServiceLine and the measure group everything seems OK.Is this warning message OK or do I have to investigate?Thanks a lot for your answer RegardsStefan


View Complete Post


More Related Resource Links

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


ConvertToUnknown with referenced dimension?

  

Hello,

 

I have a fact table with field ItemNo.
This links to Item dimension.
Sometimes, this field is empty. I have enabled CovertToUnknown for this measure group and KeyNotFound. This puts such entries under Unknown Item.

All that is good.

However, now I want to add another dimension link. Dimension Responsible is linked to fact table through reference dimension Item. Fact -> Item -> Responsible.

However, as soon as I do this, AS does an inner join to table Item when loading fact table. And as such, it does not pick up any records where ItemNo is empty.

Why does it do that and how do I keep ConvertToUnknown working?


The type 'System.Data.Linq.DataContext' is defined in an assembly that is not referenced. You must

  

Hi,

This is frustrating, and I don't know how to solve it.

I have a strange problem. I am adding a LinqDatasource object, and set the context:

        protected void LinqDataSource_ContextCreating(object sender, LinqDataSourceContextEventArgs e)
        {
            e.ObjectInstance = new KaruselaDataContext(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        }


Then I get this error:
The type 'System.Data.Linq.DataContext' is defined in an assembly that is not referenced. You must add a reference to assembly 'System.Data.Linq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.

 

On web.config I already have this:
<add assembly="System.Data.Linq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>

NOTE: The datacontext is in a different project (DLL proect) where I added a refernce to System.Data.Linq.

is there another way adding a refernce to a web project? or only though teh web.config?

Thanks.


Time Dimension Enhancement with Business intelligence Issue

  
Hi all, I want to add a year over year growth using the BI wizard (Time diemsion enhancement) but when I try to add this enhancement via the wizard then this last one has the button next disabled with a waning that says   A time dimension is required to enable this functionality. Ensure that you have a dimension of type Time, that contains at least one hierarchy with a level flagged as a time period. Inspite of the fact that I added that time dimsension with one hierarchy Time hierarchy Calendar Year Calendar Semester Calendar Quarter Time Key(With namecolumn defined as a named calculation that repsents the day with this format  yyyy, dd mm ) Me personaly I have a doubt about the last condition of the warning (with a level flagged as a time period) but I dont know exactly 1. If my doubt is right 2. What shoud I do to enhance the cube in this context using the time dimension enhancement The complexity resides in the simplicity

display non measure, non dimension fields in drill down

  
Hi All, I created my fact table with more fields than just the foreign keys linking dimensions and the field(s) to be used as mesures.  I did this hoping that on drill down I would be able to see the extra fields so that the user would have access to detail information on the records making up the measure amount.  The extra fields do not appear on drill down.  How can I make them appear, or am I on the wrong track? Thanks for any help

Dynamic Dimension with Aggregate Values

  
Hi, I have an specific requirement to make the measure value as an dimension. Let me explain my problem in brief. I have a fact table with dimensions like Time, Products etc and having single fact table with two measures. I have to create a calculated measure which shows the average of Measure 1 (here used calculated measure because there are couple of other calculations involved). And other two calculated measures. when I drill down with Products dimension for Calculate measure 1, it shows the average value for each products. Now, I want this calculated measure values (includes Product dimesnion drill down) as a Dimension and based on this value, I need to show the value of other two measures. For example: when the dimension products is used for drill down the values displayed will be like this and in this I need CM1 to be another dimension Products CM1 CM2 CM3 P1 0.10% 20 1 P2 0.20% 40 2 P3 0.30% 80 3 P4 0.40% 70 4 P5 0.50% 30 5 P6 0.60% 110 6 P7 0.70% 120 7 P8 0.80% 130 8 P9 0.90% 86 9 P10 1.00% 65 10 when CM1 is used as a dimension it should show the value like this CM1 CM2 CM3 0.10% 20 1 0.20% 40 2 0.30% 80 3 0.40% 70 4 0.50% 30 5 0.60% 110 6 0.70% 120 7 0.80% 130 8 0.90% 86 9 1.00% 65 10 How can we create the dynamic dimension with the aggregated values? Any assistance will be greatly apprec

Create a dimension based on 2 fields

  
Hello I have a table as follow: No   Placestart    Placeend 1      DK                USA 2      UK                USA 3      USA              DK Now, I want a dimension called Country, which selects either of the rows where a value exist In SQL it would be ex (SELECT * FROM table WHERE placestart = 'USA' OR placeend = 'USA) So, when I select USA in the dimension all 3 rows are listed, as USA is included either in placestart or placeend. If I select DK row 1 and 3 is selected etc... Is this somehow possible?      

DTA doesn't find referenced table form stored procedure with SP_executesql

  
Using the SQL2008R2 profiler and DTA connected to a SQL2000 server. I created a tuning tracefile with profiler for a certain db on the sql2000 server because I need to tune 1 table in the database referenced by 3 parameterized stored procedures which built a query depending on the parameters and is executed with an sp_executesql statement. When I run the DTA loading the tracefile I see the exec stored procedure events with it's parameters and the message Event does not reference any tables. And that's it. The DTA generates no Recommendatioin at all. How can I get the exact sql statements in the tracefile so the DTA can do what he needs to do? Regards

Relationship between a dimension - two fact tables

  
I have 3 fact tables and about 10 dimensions. I want to relate both "Fact_Pop1" and "Fact_pop2" to Dim_AgeGroup. But Fact_pop1 has single age group and Fact_pop2 has no single age group (5 years age group). I spent about a day on this but I could't solve it.   Fact_Pop1 Age        Race_key            Gender_key      Geography_key              Pop_size 10           White                    M                                            CA                          10000    10           White                    F        

Other group with VisualTotals and dynamic dimension

  
I All, I’m trying to create a query that dynamically display a dimension attribute on rows. I want display only first N member of that dimension attribute and aggregate the others in ‘Others’ member   (this query should be used in Reportig services and data displayed in both Crosstab and Pie Chart). So I’ve 2 parameters:   @TopN: Value: 5 @RowDimension:  Value: [Product].[Subcategory]   And the code look like this:   WITH SET [DynamicDimension] as strtoSet(@RowDimension + '.MEMBERS') MEMBER [Measures].[RowCaption] as strtoMember(@RowDimension).MEMBER_CAPTION   SET [TopItem] as TopCount(DynamicDimension,@TopN,[Measures].[Order Count]) SET [BottomItem] as {[DynamicDimension]} - {[TopItem]}   MEMBER [Product].[Subcategory].[Other ] as aggregate(BottomItem)   SELECT {[Measures].[Order Count]} ON COLUMNS , Non Empty {      [TopItem] ,[Product].[Subcategory].[Other]     } ON ROWS FROM [Adventure Works]   Unfortunately this solution doesn’t work when I change the value of parameter @RowDimension (ex with [Customer].[Customer Name]). This is because calculated member “ [Product].[Subcategory].[Other]” is related to Product dimension and is not possible parametrize the name of a member. Any suggestions?   The alternative solution that I&r

MDX Query - Get (Parent-Child) Dimension member regarding another Parent-Child Dimension, then, get

  
Hi there, I got the following MDX issue. My cube structure looks like that : DIMENSION CATEGORY (Parent-Child) --> (Many to many) FCTLESS_CategoryNode <-- DIMENSION NODE (Parent-Child) ___ DIMENSION NODE (Parent-Child) --> (Many to many) FCTLESS_NodeVariable <-- DIMENSION VARIABLE ___ DIMENSION VARIABLE <-- FACT (Supposed with only one value called VALUE) I'd like aggregate for N nodes linked to a specific category. For example, with the next content : DIMENSION CATEGORY - COUNTRY - SITE - BUILDING DIMENSION NODE US (Category Country)     - Site US_A (Category Site)     - Building US_A_A  (Category Building) FR (Category Country)     - Site FR_A (Category Site)     - Building FR_A_A  (Category Building)                                       - Site FR_A (Category Site)     - Building FR_A_B  (Category Building)                                       - Site FR_B (Category Site) 

MDX problem (Amount value is getiing doubled when multiselecting dimension parameters)

  
Hello...I need some help with the MDX...basically the problem is the Amount value is getting doubled when I am selecting more than one parameter. Here is situation...suppose I select a parameter  lets say A from dimension Account Element Hierarchy then I get the Budget Amount as $100, now in my SSRS report I change the dimension parameter value to B and then I get the Budget Amount $200. Now I multiselect A and B and ideally I should get the Total Budget Amount as $100+$200=$300 but in place of that I get $600 I have captured the mdx below...Please let me know if you have an idea to fix this --The below MDX returns $100 for Budget Value SELECT   NON EMPTY { [Measures].[Budget Amt], [Measures].[Budget / RSF] } ON COLUMNS, NON   EMPTY { (DESCENDANTS([Account Hierarchy].[Account Hierarchy].[Level 02].ALLMEMBERS) ) } DIMENSION   PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER ON   ROWS FROM [Finance]   WHERE (STRTOSET("{[Account Element Hierarchy].[Account Element Hierarchy].&[1214|425|406|495|1209|421]}"),   STRTOMEMBER("[Date].[Year].&[2010]"),   STRTOMEMBER("[Budget Version].[Budget Version].&[Budget]"), [Account Hierarchy].[Account Hierarchy Type].&[2])   CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,   FORMATTED

Error Message: The referenced table must have a primary or candidate key. [ FK Name = FK_tblA_tblB ]

  
An Entity Data Model is used to generate the script to create  a SQL CE database.  It creates all of the 10+ tables and their primary keys without any problem. A lot of foreign key creations generate errors like the following: SQL Execution Error. Executed SQL statement ALTER TABLE [tblA] ADD CONSTRAINT [FK_tblA_tblB] FOREIGN KEY([A], [B], [C]) REFERENCES [tblB]([A], [BBB], [C]) Error Source: SQL Server Compact ADO.NET Data Provider Error Message: The referenced table must have a primary or candidate key. [ FK Name = FK_tblA_tblB ] The primary keys are there and correct. Could anyone offer some tips on how to figure out what is wrong?hz

Record is already exists in Dimension table but still Kimball SCD Component is Identifying it as a n

  
Hi I have loaded Dimension table. Now even if the record already exists in my dimension table , every time I run package Kimball method SCD component is identifying as a new record. Please advise. Thanks, Anuja

SSAS 2008 Dimension root values changes after incrementally loading the dimension with 'process_up

  
The Dimension root  members (below 'ALL') changes after incrementally loading the dimension with 'process_update'.  How do I prevent that from happening ? There was no changes to data of the underlying table of the dimension.   
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