.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

Multiple companies and fiscal calendars in date dimension table

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :Sql Server
We are creating an application where different companies can have their own fiscal calendar starting on different dates. For example one company’s fiscal year may start in April and some others in Sep. Also fiscal calendars may start at any date. (E.g. 29 Sept).

These fiscal calendars would be created for parent companies. We have added the parent company ids to the date dimension table. Please suggest the correct way of setting the attribute relationship in the above scenario. If this is not the right method to do this, please suggest the correct design approach for achieving this.


Thanks in advance,


Hamlin Stephen

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

select top row for multiple entry for a date in table


My current query is

select  Date= createdon , Total=count(*) from ReportDetail where 
reportid = 9 and (CreatedOn BETWEEN '07/01/2010' AND '09/30/2010') 
group by  createdon
order by createdon desc

return data like

2010-09-21 09:36:46.493    112
2010-09-21 08:33:12.667    114
2010-09-21 07:45:20.830    176
2010-09-21 07:33:34.340    114
2010-09-20 07:27:43.753    125
2010-09-17 10:04:27.120    75
2010-09-16 11:50:05.777    52

What I am looking for

2010-09-21 09:36:46.493    112
2010-09-17 10:04:27.120    75
2010-09-16 11:50:05.777    52

Basically for 9/21, I want to get only one latest row. Please advice.

Unalbe to create date dimension with fiscal start date 29 Sept

I am trying to create date dimension with 29-Sept as starting date of fiscal year. But the dimension wizard always displays errors related date conversion. The following is the environment I tried.

OS : Windows XP

SQL Server R2

Please help.


Thanks in advance.


Hamlin Stephen

Dimension where source DSV Table has multiple FKs to the same table


Hi All, if I have a dimension table "DimService" and it has 2 foreign keys to another dimension table "DimCustomer" on 2 different FK fields, lets say they are called SellTo and BillTo Customer.

If I wanted to show properties from both SellTo Customer and BillTo Customer it doesn't seem possible as there is no way to tell the dimension builder *which* relationship to go down for the magical join that it must be producing.

Is there any way to do this?

I have worked around it by creating named queries in the DSV (DimCustomerSellTo and DimCustomerBillTo) but of course this means that it needs to load the same data twice on processing which is sub-optimal.

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


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

merging multiple tables in a single dataset to single table


 i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset 

like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid  as a compact data and combination of 3 tables from d single dataset.....

can some1 help me please.....

How to insert the Current date and time in to SQL Table..

Hello Members,              I have create the table as per the following ..create table company(  empname varchar(30),  empid int,  joindate smalldatetime)I tried,insert into table values('Kumar',202, ??????)I want to insert Current date and time into table Company.....Please give me the solution...Thanks.. 

Is it possible to use multiple date fields to have an event show more than once on a Sharepoint 2007

Hello, Is it possible to use multiple sets of start and end times to have events display multiple times in a sharepoint calendar? To clarify: - I have the standard fields of "Start Time" and "End Time" in my calendar list. - Events show on the calendar just fine. - Would like to create additional fields called "Start Time 2" and "End Time 2" within the same record and have the event show multiple times on the calendar. - The recurrence functionality of sharepoint will not meet my needs because the pattern I need to use is not in one of the allowed choices. Thanks in advance, John

SSRS Table hide duplicate by date

Hi, I am parikshit deshpande i am working in SSRS 2005 from last three months. I am trying to solve this problem please help me for that, I have one table as per  Date                     Shift                               Tons                       Passes 1/1/2009            Day                                    Formula                  Formula 1/1/2009           Swings                            1/2/2009           Day 1/2/2009     &nb

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

Problem displaying Date from Sql table on Calendar Control

Hi, I am displaying Event dates from SQL table on Calendar Control in ASP.NET. Also, I have GridView Control which shows event details when certain date is clicked on calendar control. But I have a problem as not all dates are displaying properly. Strangly enough only dates which have same month and day date are displayed. For example: It shows ok dates such as 08/08/2010, 09/09/2010, 10/10/2010 etc. If I click on the date which in SQL table exists as 11/25/2010 or 12/15/2010 etc (no matching month/day numbers) it shows error message saying: "System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string."  Follwoing is my code: using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.Sql; using System.Runtime.Remoting.Messaging; using System.Configuration; using System.Data.SqlClient; using System.Data; using System.Drawing; public partial class Test_Calendar : System.Web.UI.Page { SqlConnection mycn; SqlDataAdapter myda; DataSet ds = new DataSet(); DataSet dsSelDate; String strConn; private void Page_Load(object sender, System.EventArgs e) { strConn = "Data Source=mydatasource;Initial Catalog=DBname;Persist Security I

How 2 join Multiple Keys based table???

I have a table INC with 2 Columns/Fields, i.e. YR and CL set as primary keys by selecting both the columns and selecting primary key symbol with right click. How to set up a FK with the other table INC_DTL's CL which I seek to be restricted to a combination of the INC's 2 fields? Thanx in advance.

Analysis Service Process Dimension Cause : OLE DB error: OLE DB or ODBC error: ORA-00942: table or v

Dear Gurus, I'd face a "STRANGE" problem. I'm using SQL2008 connect to Oracle Data Source (OracleOldDB.1) When I process a snow-flake dimension . There is a ORA-00942 problem. But if I past the SQL Statement to a Oracle Tool . It worked without any problem. Does any body ever face this "STRANGE" problem ? And I'd try to remove this table from data-source-view and re-join it . Still same problem !!  Wilson

Help in Building Fiscal Dates Table

hi Experts,  I'm trying to build fiscal periods table. I have Calendar Dates of year 2007  in 1 table and just start Dates of Fiscal weeks in another table, can any one suggest me about building Fiscal table containing entire year using the 2 tables.  thank you ....   

Infopath 2007 Repeating Table - Multiple Value Column Text - Hiding Rows based on Column text values

Infopath 2007 browser based form Full Trust Example: I have a repeating table (FruitChoice) that has multiple columns. Both drop down list point to sharepoint list data sources. Choose your tree ft. drop down list – 6Ft Choose your Department drop down list - 103 This repeating table is conditional on the drop down values. This works great. Trees     Fruit       Cost   Date Ordered    Date Delivery Department 6Ft        Peaches                                                        103 3Ft        Apples                                                          102 3Ft        Peaches         &

Multiple Source connections in Configuration Table

 I am trying to query multiple servers for properties and record those properties in a table for later use.  I can make this work with the ForEachEnumerator by querying a table. However, I would like to use the Package Configurations option if I can instead.  Any options? Is there a way to force the package to loop through multiple values for one variable, contained in an SSIS configuration table?  Thanks in advance!

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
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