|
|
You're in the
sql server data warehouse
group:Time Dimensions in the Retail Industry
sql server data warehouse:
Questions: 1. Is it possible to create a time dimension with these extended levels and utilize the out-of-the-box functionality provided by SQL Server for time dimensions? 2. If it cannot be qualified as a time dimension, can we still add business intelligence to the cube itself to get similar functionality? Details: Retail Time Dimension: The time dimension definition that we need to work with, from our initial research, does not comply with any of the four specialized calendars that ship with SQL Server Analysis services. We have the following requirements for our time dimension: Year – may have 12 – 13 periods (user configurable, may change between years), 52-53 weeks (every five years it may be 53 weeks) Period – may have 4-5weeks, user configurable Week – 7 days Day The above levels are standard across all customers. Some customers however may have additional levels like Quarters and Seasons. Seasons may range from 1 – 12 periods. Quarters may have 4 – 5 periods. There will always be four quarters in a year. Each of these levels has a Start Date and an End Date. When we define a dimension with these attributes and try to generate a relational schema out of it, we get this validation error: “The DimCalendar dimension has the time type, but without time binding information. The dimension will be treated as regular dimensionâ€. The attached images show our dimension design.
Thanks Tim!
And how do you work with 13 months or 53 weeks a year, both of which can default to 12 and 52 depending on the year? [quoted text, click to view] "Tim Dot NoSpam" wrote:
> If you've set the usage type for the dimension to "Time", then you need to > associate the time members with each attribute. > > For example, if you have RetailQuarter as an attribute, associate it with > the "QuartersofYear" property, RetailYear with the "Year" property, etc etc. > > -Tim > > "Manu Puri" <manup@online.nospam> wrote in message > news:BD5893D8-12E2-4072-8080-D4C38A1983FD@microsoft.com... > > Question from Retail ISV, your guidance appreciated: > > > > Questions: > > > > 1. Is it possible to create a time dimension with these extended levels > > and > > utilize the out-of-the-box functionality provided by SQL Server for time > > dimensions? > > 2. If it cannot be qualified as a time dimension, can we still add > > business > > intelligence to the cube itself to get similar functionality? > > > > Details: > > > > Retail Time Dimension: > > > > The time dimension definition that we need to work with, from our initial > > research, does not comply with any of the four specialized calendars that > > ship with SQL Server Analysis services. > > > > We have the following requirements for our time dimension: > > > > Year - may have 12 - 13 periods (user configurable, may change between > > years), 52-53 weeks (every five years it may be 53 weeks) > > Period - may have 4-5weeks, user configurable > > Week - 7 days > > Day > > > > The above levels are standard across all customers. Some customers however > > may have additional levels like Quarters and Seasons. > > > > Seasons may range from 1 - 12 periods. > > > > Quarters may have 4 - 5 periods. There will always be four quarters in a > > year. > > > > Each of these levels has a Start Date and an End Date. > > > > When we define a dimension with these attributes and try to generate a > > relational schema out of it, we get this validation error: > > "The DimCalendar dimension has the time type, but without time binding > > information. The dimension will be treated as regular dimension". The > > attached images show our dimension design. > > > >
If you've set the usage type for the dimension to "Time", then you need to
associate the time members with each attribute. For example, if you have RetailQuarter as an attribute, associate it with the "QuartersofYear" property, RetailYear with the "Year" property, etc etc. -Tim [quoted text, click to view] "Manu Puri" <manup@online.nospam> wrote in message news:BD5893D8-12E2-4072-8080-D4C38A1983FD@microsoft.com... > Question from Retail ISV, your guidance appreciated: > > Questions: > > 1. Is it possible to create a time dimension with these extended levels > and > utilize the out-of-the-box functionality provided by SQL Server for time > dimensions? > 2. If it cannot be qualified as a time dimension, can we still add > business > intelligence to the cube itself to get similar functionality? > > Details: > > Retail Time Dimension: > > The time dimension definition that we need to work with, from our initial > research, does not comply with any of the four specialized calendars that > ship with SQL Server Analysis services. > > We have the following requirements for our time dimension: > > Year - may have 12 - 13 periods (user configurable, may change between > years), 52-53 weeks (every five years it may be 53 weeks) > Period - may have 4-5weeks, user configurable > Week - 7 days > Day > > The above levels are standard across all customers. Some customers however > may have additional levels like Quarters and Seasons. > > Seasons may range from 1 - 12 periods. > > Quarters may have 4 - 5 periods. There will always be four quarters in a > year. > > Each of these levels has a Start Date and an End Date. > > When we define a dimension with these attributes and try to generate a > relational schema out of it, we get this validation error: > "The DimCalendar dimension has the time type, but without time binding > information. The dimension will be treated as regular dimension". The > attached images show our dimension design. >
There are also MonthsOfYear and WeeksOfYears you can set your RetailMonth
and Retailweek to. You need a custom Time table (I'm assuming you're using one) that has these attributes in it. The templates above are not named precisely as they are in SSAS, but close enough where you should be able to find them. [quoted text, click to view] "Manu Puri" <manup@online.nospam> wrote in message news:A5A54B99-877F-4D6E-939D-F1FF10AEB18F@microsoft.com... > Thanks Tim! > > And how do you work with 13 months or 53 weeks a year, both of which can > default to 12 and 52 depending on the year? > > "Tim Dot NoSpam" wrote: > >> If you've set the usage type for the dimension to "Time", then you need >> to >> associate the time members with each attribute. >> >> For example, if you have RetailQuarter as an attribute, associate it with >> the "QuartersofYear" property, RetailYear with the "Year" property, etc >> etc. >> >> -Tim >> >> "Manu Puri" <manup@online.nospam> wrote in message >> news:BD5893D8-12E2-4072-8080-D4C38A1983FD@microsoft.com... >> > Question from Retail ISV, your guidance appreciated: >> > >> > Questions: >> > >> > 1. Is it possible to create a time dimension with these extended levels >> > and >> > utilize the out-of-the-box functionality provided by SQL Server for >> > time >> > dimensions? >> > 2. If it cannot be qualified as a time dimension, can we still add >> > business >> > intelligence to the cube itself to get similar functionality? >> > >> > Details: >> > >> > Retail Time Dimension: >> > >> > The time dimension definition that we need to work with, from our >> > initial >> > research, does not comply with any of the four specialized calendars >> > that >> > ship with SQL Server Analysis services. >> > >> > We have the following requirements for our time dimension: >> > >> > Year - may have 12 - 13 periods (user configurable, may change between >> > years), 52-53 weeks (every five years it may be 53 weeks) >> > Period - may have 4-5weeks, user configurable >> > Week - 7 days >> > Day >> > >> > The above levels are standard across all customers. Some customers >> > however >> > may have additional levels like Quarters and Seasons. >> > >> > Seasons may range from 1 - 12 periods. >> > >> > Quarters may have 4 - 5 periods. There will always be four quarters in >> > a >> > year. >> > >> > Each of these levels has a Start Date and an End Date. >> > >> > When we define a dimension with these attributes and try to generate a >> > relational schema out of it, we get this validation error: >> > "The DimCalendar dimension has the time type, but without time binding >> > information. The dimension will be treated as regular dimension". The >> > attached images show our dimension design. >> > >> >> >>
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |