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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Add non primary column as key column in Dimension

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


My dimension table X has primary column as Primary_ID as char(3) column. About 90% of rows in my dimension has value 'Not assigned' and such rows are scattered in table.

I have added one column as OLAP_ID to dimension table X which will assign default '000' value to all 'Not assigned' rows. This will group together all invalid rows.

Fact table has Primary_ID as FK.

I have following questions:

1. Which column should be use as key column in my dimension table.

2. What should be the hierarchy between Primary_ID and OLAP_ID column.

Let me know if you need more details.



View Complete Post

More Related Resource Links

Dimension design: Key column of non-key Dimension Attribute

Assume I have a product dimension where key dimension attribute is product code - this is a very large dimension with more than 1 million members, the key column for this attribute is productID (integer). There will be other attributes in this dimension related to key attribute.   My question is about the key columns to be defined for these other attributes. As it is related to key attribute, it has to include ProductID as part of key and hence forming a composite key - e.g. Product inception date - the key column for this has to be ProductID + date as many products can have same date. This design will violate the best practice recommendation from MS to have only numeric key columns for very large dimension attributes. But I can't find a way around this and assume this will be case for all non-key attributes in very large dimension. Is my understanding right? I assume this will be an issue for everyone? Any standard way to get around this? Or best to leave it as such? Thanks in advance.

How to delete number in primary key column

Hi, BurnbetDataContext nwContext = new BurnbetDataContext(); var db = new BurnbetDataContext(); var personPets = from p in db.tbl_Payments where p.ID == 1 select p.ID; db.tbl_Payments.DeleteAllOnSubmit(personPets); This code does not work because the primary key ID.What should I do so I can delete the ID = 1?

What is the best way to get the primary key column name of a table in a stored procedure ?


I'm writing a stored procedure which manipulates a table whose name must be given as a parameter. The stored procedure needs the primary key column name (by design the primary key must have only one column), and I thought I could get this from the system table instead of having to provide it as a parameter. So I need to put the primary key column name in a variable.

The best way I found so far is this :

declare @keyname nvarchar(1024)
table #keys (table_qualifier nvarchar(255),table_owner nvarchar(255),table_name nvarchar

Get SSAS Dimension Attribute SQL Column Name using C#



I am trying to determine what the real SQL Server Column name is for a Dimension Attribute using ADOMD.net, however all I am able to find is the the TABLE_NAME and COLUMN_NAME Columns using the GetSchemaDataSet("DBSCHEMA_COLUMNS", null) function.

Does anyone have any suggestions?



Select Primary Key Column of Table returns 0 rows


I have three tables: Design, Box, Wall.

Design has a column DesignId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false for compatibility with Entity Framework v4.

Box has  a column BoxId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false as well.

Box also has a column DesignId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Wall has  a column WallId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false as well.

Wall also has a column DesignId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Wall also has a column BoxId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Box.DesignId has been set as a ForeignKey relating to Design.DesignId.

Wall.DesignId has been set as a ForeignKey relating to Design.DesignId

Wall.BoxId has been set as a ForeignKey relating to Box.BoxId.


If I execute      SELECT DesignId FROM Design     I see the correct number of rows.

If I execute     SELECT BoxId From Box        I see zer

the column in table tbl_table1 do not match an existing primary key or UNIQUE constaint


ID primarykey (autoinc)
UserId uniqueidentifier
IdNumber primarykey int

 I'd like to have a relationship between two tables.

Where is the problem?


Prefixes on column headings of date attributes using shared dimension


When reusing a date dimension multiple times as in the Adventure Works cube, the attribute names are prefixed with the cube dimension name in the selector screen. However, when they are brought into the query, the prefix is dropped. So "Delivery Date.Date" becomes just "Date". This is also the case in Excel 2007 which can be confusing for users. Any way ar ound this?



Explicitly defining Clustered Index and NOT NULL constraint on Primary Key column


In the following table definition, is it necessary to explicitly define clustered index and NOT NULL constraint on Primary Key column, since defining Primary Key alone will do the job.

create table t1 (
col1 char(4) Not Null Primary Key Clustered,
col2 varchar(20)

Tooltip for GridView Column Headers (ASP.Net)

I just wanted to add auto tooltips for my GridView headers. There are couple of ways but I don't want to use javascript and wanted to use simple 'title' attribute. It took couple of hours to get the working code supporting sortable columns. I thought it will be very useful for others. Here's the code and logic behind it.

Multiple Column Dropdownlist for the ASP.NET DataGrid

Based on my previous control "Multiple Column DropDownList for ASP.NET", I received many emails asking for the same control to be used in the DataGrid for web applications. Here we go.. This control can be used as the regular MS DropDownList in the DataGrid and also as a regular dropdownlist. It has all the properties, like DataTextField, DataValueField, DataSource, SelectedIndex etc. The download file contains the samples both in VB.NET and C#. In this sample, I have used the Northwind database of SQL Server.

Alternating row background for first column of a report


is there a way to make the first column of a report use the an alternating background color? 

I have the background color set for the entire row however it does not work for the first column if the color is set to an expression.  If I just set it to a color i.e. red it works just fine. If I insert a dummy column to the left it works just fine (but the dummy column does not work, presumably because it is first??).

In the file below only the textbox for part number does not show an alternating background.  All others work correctly.

                    <Textbox Name="PartNumber">

Use a column as parameter for a sds in another column; gridview


Hola, I have a gridview which is bound to a sds which returns 7 columns and I added two columns from the GUI.  What I want is to fill up the 9th column with DDLs and these  DDLS will get their data from a different sds which is using a stored procedure with a single parameter.  I created the 9th row, used template field, made the sds for 9th row, the stored_procedure, threw a DDL in Item template and all  I need now is to pass a parameter in the first column to the sds in the 9th column.  I can code this but i'm getting tired of aspx.cs :) and would like to do it from the GUI.

Pass a grid column value to a Modal


Hi, I have a requirement to hide a field called details on a gridview and show it in a jQuery modal dialog when users click on the Customer ID hyperlink. This grid is created programmatically. I will be hiding the details column via the CSS display:none property. Does some one know how can I pass the grid's selected hidden column value to the modal? Here is some of the code I started:

            GridView mygrid = new GridView();
            //.....................................More columns code for the grid

            //Here is where I need to pass the Details column value associated with the customer ID field selected to the Modal and open the Modal dialog.            
            HyperLinkField linkField = new HyperLinkField();
            string[] dataNavigateUrlFields = { "CustomerID" };
            linkField.DataTextField = "CustomerID";

Bug: Does not change column type from nvarchar to ntext


Scenario: If you create a table with a column of type nvarchar, populate it with some content and try to modify it using the WebMatrix editor, from nvarchar to ntext, it does not display any error message or warning that this is not possible. It silently saves and reverts the change, back to nvarchar. Wasted some hours on this issue as I thought my column was ntext, but in fact was still nvarchar.

Expected behavior: It should change to ntext, or if SQL CE doesn't support that, it should notify the user that no change was applied.

Display column from child table. Possible ?



I'am using dnamic entity with EF4. On a list page of a table, I would like to display a column containing information from a child table of the current element. Sample :

Order List :

Order Data | Required Date | Shipped Date | etc... | ... | Customer Name (foreign key with tostring() method override) | Customer Postal Code (Column that I want to add) |

I don't known how to do that. Is it possible ? Maybe I have to to create my own Metadata proxy that will add dynamicly a column on the MetaColumn list of the table.

Does someone have an easier or better idea ?

Thank you for any help.

Rendering data with HTML tags in the DD Gridview for a selected column


I am having trouble finding out where and how to HTML Encode a cell's data on the Dynamic Data (v4.0) gridview of List.aspx.  As a simple case, suppose I have formatted cell data that is A<br/>B in the DB.    Obviously, I want A stacked on B in the cell.

It seems gridView1.HtmlEncode = true has gone away.

So maybe I'll try to catch it on the RowDataBound event:

protected void GridView1_RowDataBound(Object sender, GridViewRowEventArgs e)

            if (e.Row.RowType == DataControlRowType.DataRow)
                // Html Encode the cells


but this event never fires?

Has anyone figured out how to properly render HTML tags data in the List.aspx's GridView1? 

If I figure this out, then I can add a MetaAttribute called something like [EncodeAsHtml(true)] and be on my way.  Thanks!

Filter by Computed Column



After looking for a driver to open legacy dbf drivers to connect, the next level trouble a have is this one:

first, i can retrieve the data but when trying to filter by a parameter (somesc columns) i get an error.

The error says about the database can't determine some blah blah!

The error is a columm tha is formated (  1234  ) about  8 space, fixed. The values in the query appears like that. I resolve this by Trimming Ltrim(ColumnName) and i get this (1234).

Now in SqlExpress, how can i filter by the new Trimmed Expression? (This is a primary key that the DBF use)


SELECT column1, column2, column3


SELECT Ltrim(column1) as NewExp, column2, column3
Where NewExpre = @NewExpres                  -Error-


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