.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

Why shouldn't I snowflake a date dimension to a table in my data source view that's not a fact?

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :

This is quite difficult for me to communicate so please bear with me.

I have created my data source view in my solution. I have a central fact table surrounded by the dimensions. Several of the dimensions have dates which I converted to integers. Thinking that I would need separate date dimension tables so the date integers could find their dates I created a data dimension table "snowflaked" to the dimensions with dates.

An example is my dimPolicy table has dates of creation and ending.

I converted these to integers.

A table called dimPolicycalendar was made in the DSV and then linked. A dimension snowflaked to a dimension.

When I went to create a dimension for Policy it auto attached the dimPolicyCalendar. Then all my dates disappeared. And the generic values in the dimPolicyCalendar appeared.


How do I get my values back?

If I wasn't supposed to add a calendar table to the dimension how will I convert those integer values to the date values I want?

I am panicking a little because I can not find any helpful resources.

You are my last hope before we are pushed to an Oracle solution. Yuck!



BJ Gordon

View Complete Post

More Related Resource Links

Getting counts by 2nd Date Dimension Attribute with Snapshot Style Fact Table

  I have an MDX question finding hard to solve.  I have a Snapshot Fact Table with a snapshot of the records in the source system for each batch date.  All records in the fact table are assigned the batch date with the batch date key.  There are many records for each day and each batch date is an entire copy of the source records.  So, the grain of the fact table is one record for each batch date that exists in the source system.  These facts rows have another date in them for when the record was entered.  This date is different from the batch date in that the batch date is based on the day the batch was processed and the entered date is based on when the record was entered.  If a record was entered many days before, its batch date will be today but its entered date will be several days ago.  Therefore each day a copy of all the records entered the previous batch date and all the records added on today's batch date are present. Fact Table : FactSnaphshotKey (surrogate for easier administration) BatchDateKey (link to batch date dimension – date dimension, first in dimension list so it is used for semi aggregate measures) EnteredDateKey (link to entered date dimension – date dimension) Facts Count – measure for fact table - default measure from Analysis Services cube 2 Dim

Change data source view for dimension


Is it possible to change the data source view the dimension gets the table definition from?

Basically I would like to restructure the ds views and dimensions I created earlier are reading from ds views that are no longer appropriate.   Tables would be exactly the same.

Entity Data Model and database view returning the same columns as there are in a table


When adding a stored procedure into the Entity Data Model I can select whether the procedure returns a scalar, a (new) complex type or one of the entity types I already defined. 

How do I do something similar for a view?

I mean assuming I have a view like this

CREATE VIEW FilteredFoos as SELECT Foo.* FROM Foo join ... WHERE ...

(that is a view that implements some involved filtering, but returns all columns from one table) how do I add it to the project so that I can use the entity set, but get the Foo objects, not some new FilteredFoo objects.

var foos = myDB.FilteredFoos.Include("Bar").ToList();

foreach (Foo foo in foos) { ...

Thanks, Jenda

How to create scrolling table for columns in a SharePoint Designer Data View


I have a fairly complex SharePoint Designer 2007 Data View they have added two more requirements.

1. Have the columns on the right side be able to scroll left to right as there is a large number of columns, while the first 3 columns on the left side stay locked in place.  This is only one list not two, and because of some other requirements I have it must remain to be just one list.

2. Then they also need to have scrolling up and down of the entire list with the headers locked in place.

The first requirement is more important than the second, but preferrably I need to do both.

I am new to XSL so not familar with the syntax. Is this something I can do on a custom Data View? Does anyone have any examples of how?

I have a drawing that show how it needs to look but I don't see an option here to upload a graphic.

So here is a link to my graphic example uploaded to my blog area: http://lindachapman.blogspot.com/  or just the image here: http://4.bp.blogspot.com/_aB01ue__NvQ/TGL42MOvrGI/AAAAAAAAAA4/c3xP4uCy4BU/s1600/Scrolling.png

I failed to mention that 3/4 of the columns are HTML Calculated fields wh

Calculated date field displays differently in List View and Data View web parts

In SharePoint GUI I created a simple custom list with a calculated Date/Time field (Due).  The calculation is =[Created]+7.  I'm trying to display this data on a page using a DVWP.  But first, for testing purposes, I also created a second calculated Date/Time (TestCreate) field in my custom list that is simply =[Created]. In the List View from within SharePoint, the dates show as expected (for example): Created = 8/12/2010 3:45 PM Due = 8/19/2010 3:45 PM TestCreate = 8/12/2010 3:45 PM In SharePoint Designer, I added a Data View web part and the above three data elements (each configured to display mm/dd/yyyy hh:mm:ss tt).  I see the time displayed for the example list item as: Created = 8/12/2010 3:45:30 PM Due = 8/19/2010 10:45:00 AM TestCreate = 8/12/2010 10:45:00 AM I've tested a couple scenarios and the calculated fields always display five hours in the past (along with dropping the seconds).  It seems like the DVWP is returning the date in a different time zone (with no seconds).  The lcid is set right (1033).  I didn't see any time zone settings for the DVWP specifically.  I'm basically stuck.  Could someone help me figure this one out. Thanks.

Data Source View Gives incorrect Error--Different Data Type

Hi, I'm creating a report model (2008 R2).  I am trying to create a relationship between two tables.  One FKs to another--same name and type (tinyint) columns.  But the Data Source View editor doesn't allow this, giving the message "...source and destination column have different data types".  But the columns are the same types.  I ahd this once before  acouple of years ago and don't remember how I got around it. Does anyong have ideas?  

Data Source View

I followed this below procedure for create a DataSource View In Solution Explorer, right-click Data Source Views, and then click New Data Source View. On the Welcome to the Data Source View Wizard page, click Next. The Select a Data Source page appears. Under Relational data sources, the Adventure Works DW data source is selected. Click Next. On the Select Tables and Views page, you select tables and views from the list of objects that are available from the selected data source. You can filter this list to help you in selecting tables and views. ( My probelm comes in 4th step, I see my server name in Relational Data sources area, i click next to select Tables & Views but i dont see any list of tables in there..? What am i Missing? Today is my day one on SSAS...please HELP FM

How to get "Current Date" on SharePoint Designer for Invoice Ageing Data View Report??

Hi Experts, I would like to know how can i get/show "Current Date" in SharePoint Designer ?? Any good tutorial ? FYI, I need this "Current Date" for my Ageing Data View Report, Purpose To offset with "Invoice Receive Date" to get total days of payment payable for the invoice. Please assists, Thanks

SSRS 2008 R2 Data Source View Wizard does not recognize unique key constraints on views when using O

Hi, SSRS Data Source View Wizard does recongnize Primary/Foreign Key relationships from oracle datasouce tables, but does not recognize unique key constraints on views. How can I solve this problem. Thanks

Non parent-child dimension with fact data at different levels... How!

Hi all, Quite new to SSAS, wondering if anyone could help with the following... I've got a dimension with attributes that indicate geographical location based upon UK postal boundaries - so ~1.8m Postcodes > ~10000 Postcode Sectors (PCDS) > ~3000 Postcode Districts (PCD) > ~100 Postcode Areas (PCA). The problem I have is that the address information is not of the highest quality. I have matched 50% of my facts to postcodes, of the remaining 50% i've mapped them to PCDS, PCD or PCA, where possible, leaving the lower-levels as NULL in those cases. My attribute hierarchy works fine where I have complete records all the way down the hierarchy, but I only have a single UNKNOWN member at the top (GOR) level. I want an UNKNOWN member at each level. It makes sense to me how to do this using a Parent-Child dimension, but i'm keen to avoid that as the performance is terrible. I'm hoping there's a method of configuring this. Any help massively appreciated.

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

Bridge Table dimension or fact? updating from snapshots



Scenario: Bank Accounts and Customers. One Account can have many customers and many customers can have one joint Account. so its Many to Many relationship.

Special Scenaio: Bank provide us daily snapshot of all thier dimensions and facts, every night thier ETL run, and newsnapshot is available, previous is gone.

I am using SCD Transformation to update the dimensions, Type 1 for all the columns.

Tables1: DimAccounts (AccountsID(PK))

Table2:DimCustomers (CustomerID(PK))

Table3:DimBridge (AccountID (FK), CustomerID(FK), RelationShip (varchar10))

Question1: Are we supposed to treat bridge tables as Dimensions or Facts?

Question2:If it is to be treated as Dimension, How would I apply SCD Wizard to it, Since there are two business keys involved?

Question3: Do i need surrogate key in Bridge Table, like i have in other dimensions?

Thank You


Data view web part: Change Data Source

I have a data view web part that I would like to use multiple time in the same page, but tied to different sharepoint lists.  Is there an efficient way to do this?

I can add additional data sources, but I don't see how I can remove the original.

Data in Physical table and not in Dimension


Hi All,

I have a strange problem. For Analysis services deployed in three environments. I have problem only with data in Production, one of the site is missing in Production, but the same site is present in Development and QA environment. I have already verified following things.

1) Site is present in Physical Table

2) Verified SQL Statement thats generated while processing dimension: Data is retrieved from physical table using this query.

3) Have already done full processing of Dimension.

4) I have even check DSV table and see data. 

5) Corresponding data is present in Fact

For Example if I am looking for Site Number: 123456, this site is present in Development and QA. But missing in Production. By the way, structure, and data of Development, QA and Production is same. It would have made sense for any issues or problem if this site is missing in all the environments, but thats not the fact.

Any help or suggestion is deeply appreciated.

Thanks in advance,


Data Flows in SSIS - Mapping Multiple source tables to Destination table **Newb question**

Hi I am new to SSIS and had a basic question. I have around 30+ tables in a db that needs to be migrated to a newer schema in the DB. The data flow task seems to be ideally suited for my requirement. My question is do I need to create 30+ different data flow tasks for this which will get executed one after the other or is there a better way to migrate large number of tables. Also how are referential constraints taken care of during such migration. Thanks and Regards, Ganesh Ranganathan
Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

Save Dimension member only when this have lines in Fact Table


I have one dimension table with over 600 item, but in my fact table only 40 of this member have lines (my fact have a filter for the 2 laste year)


How save on the dimension only this 40 members?


Best regards




format date in data view




How do i convert date which is entered in mm/dd/yyyy format to dd-AUG-yyyy format in data view?Is there an xslt code for it?


Thank you.

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