.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

Modelling snowflake type schema as referenced dimension and unknown members

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

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




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


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


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.


Parent-Child relationship on a Type 2 (SCD) Dimension

  
For a Type 2 slowly changing dimension (SCD) such as Employee with a Parent-Child relationship, how do you handle the explosion of new rows when a high ranking employee has an attribute change?   It will require terminating the high ranking employee row and adding a new current row with a new surrogate key. Then every employee row below this employee will also have to be terminated and a new current row added with the new surrogate key of the parent.   This will have to be performed 1 level at a time so the subordinate rows will have the new surrogate key of their immediate parent.   For a large organization such as 20,000 employees, this will result in a dimension with hundreds of thousands rows in a relatively short time.   Is there a better way to model this?

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

Problem with Dimension of type Account

  
Hi, I have a simple fact table where I have a dimension time and a dimension of type acount. The fact table looks like this FinanceKey AccountKey ProfitCenterKey DateKey DateAlternateKey ScenarioKey Amount 1 11 5 7308 2010-01-01 1 131 2 11 3 7308 2010-01-01 1 62115 3 11 0 7308 2010-01-01 1 27626 4 11 6 7308 2010-01-01 1 12 5 11 2 7308 2010-01-01 1 74316 6 11 7 7308 2010-01-01 1 14 7 11 5 7339 2010-02-01 1 147 The account dimension looks like this AccountKey AccountAlternateKey ParentAccountKey AccountName_en AccountName_de AccountType Operator CustomMembers ValueType CustomMemberOptions 1 1 NULL Statistical Accounts Statistische Kennzahlen Statistical ~ NULL Units NULL 11 11 1 Customer Count Anz. Kunden Balances ~ NULL Units NULL If I browse my cube I get no data for the measure amount. When I remove hte parent Account "Statistical Accounts" and ste the parent of "Customer Count" to NULL I will get the correct Customer count. Any idea?

Cannot create unknown type '{clr-namespace:Blog.LongRunningWorkflow.Example.Activities}WaitForReques

  
Hi, I am facing problem in loading XAML at runtime. Below is the code please help me. public void ProcessRequest(Request request) { // Get the workflow instance id to use Guid instanceId = this.requestToInstanceMapping[request.Id];         // Setup the arguments that are passed to the workflow          IDictionary<string, object> input = new Dictionary<string, object>();              input.Add("Request", request);            using (FileStream fs = new FileStream(@"ProcessRequest.xaml", FileMode.Open))          {             // Load the ProcessRequest workflow           Activity wf = ActivityXamlServices.Load(fs) as Activity;                            // Create the workflow instance specifying the arguments and instance id                      WorkflowApplication wapp = new WorkflowApplication(wf, input);          string persistenceConnectionString = "Data Source=Dev1;Initial Catalog=InstanceStore;User id=sa;password=xxxxxxx;Asynchronous Processing=True";          &nbs

In SSIS 2005 how can I implement Slowly Changing Dimension Type 6

  
Hi I can't see SCD Type 6 in SSIS 2005 ,how do I get add  into my data flow task and if anybody can explain me step by step process for SCD type 6

'schema.AllCategories' threw an exception of type 'System.UnauthorizedAccessException

  
We're porting a solution from 2007 to 2010 and we're having trouble accessing the CrawledProperties property from the Schema object. Other properties from the Schema object however are accessible (e.g. AllManagedProperties). It does not matter whether we use the obsolete SearchContext to create the Schema object, or the new SearchServiceApplication (Example code from Steve Curran here: http://social.technet.microsoft.com/Forums/en-US/sharepoint2010programming/thread/2550e6e8-34a6-4012-969f-9657dc526d74) Now what's interesting is that the SAME user (Farm administrator) can access the AllCategories using a Windows Forms application, but when using a SharePoint solution (Codebehind on an application page) it doesn't work. We have double-checked that the executing user is the same. The same user can also browse the CrawledProperties using the Central Admin UI. We're experiencing this on several server installations, one of which is the "Standalone" installation of Search Server Express 2010, whereas the same user was used to perform the installation. I'd appreciate any ideas you might have.

Referenced dimension on referenced dimension - second

  
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

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


Cannot create unknown type when loading WF Service from loose Xaml with ActivityXamlServices

  
Hello,

I am trying to host a WF4 (RC) Service dynamically. I have a test solution with two projects. The first is a declarative workflow service library with one root Flowchart activity in it, and a simple custom code activity. The workflow service library does not depend on any other custom assemblies or references. The second is my host app, which in my test solution is just a console application.

In my host app, I am attempting to the use ActivityXamlServices to load the Xaml for the workflow service into an activity, and then use the WorkflowServiceHost to fire up a workflow instance using that activity.

As soon as I try to new up the WorkflowServiceHost object, I get this exception...

Cannot create unknown type '{clr-namespace:DeclarativeServiceLibrary1}CodeActivity1'.


If I remove CodeActivity1 from my Flowchart designer, everything runs fine. If I add a direct reference to the workflow service project from my host project and then create a WorkflowServiceHost using an instance of my Flowchart activity instead of the activity created from the Xaml, it also works fine.

It seems to not like using my CodeActivity for some reason when loaded dynamically.


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


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