post<\/a>, I covered the new dimension wizard and mentioned there were options for creating time dimensions that I would cover later. Now I’ll explain those options further.<\/FONT><\/P>
\nTime Dimension Options in SQL Server 2005 Analysis Services<\/o:p><\/FONT><\/B><\/P>
\nLet\u2019s start with a quick review of what happens in SQL Server 2005 (referred to as Yukon hereafter). On the Select the Dimension Type page of the dimension wizard, you can choose Standard, Time Dimension, and Server Time Dimension.<\/FONT><\/P>
\nIf you select Time Dimension, you identify the time table in your DSV and then you map your time columns to the Analysis Services time properties. For example, you map a CalendarYear column in your time table to the Year property. This association of a table column to a property helps your MDX queries how to handle time-related functions like YTD or PeriodToDate. <\/SPAN>I admit I find this mapping process tedious, but necessary. When you use a time dimension table, you have to manage the processing of the Analysis Services dimension to add new time members if you incrementally add members to the table (instead of populating it well into the future as some people prefer to do). <\/SPAN>The benefit of this approach is the ability to include time attributes that mean something to your industry, such as a flag for a holiday or weekend versus weekday. You also can confirm inclusion of hierarchies based on the columns you map to time properties. I never liked the inability to change the hierarchy names here, but that\u2019s just a nit. You can, of course, add your own hierarchy or modify the hierarchy name later in the dimension editor.<\/FONT><\/P>
\nIf your time-related analysis is pretty simple and you don\u2019t want to manage a time dimension in your data source, you can create a Server Time Dimension instead. This is a pretty handy feature that lets you define a date range for the dimension, the type of attributes you want to include (year, quarter, month, etc.), the calendars to support (calendar, fiscal, etc.). You can confirm the default hierarchies just like you can with a table-based time dimension. The generated dimension includes several additional attributes, such as Day of Month and Day of Year, and there are other optional attributes you can add using the editor, such as Day of Week or Trimester of Year. When you want to add the Server Time dimension to a cube, you have to add it on the Cube Structure page of the cube designer because the cube wizard doesn\u2019t have a way for you to add it there. <\/SPAN>You still need to ensure the end date of the range of your Server Time dimension is equal to or greater than the maximum date in your fact table. <\/FONT><\/P>
\nThere is a third option available. You could generate a time table by selecting the option to build the dimension without a data source. Select the Date template and you\u2019ll get a similar interface as that for the Server Time Dimension. When you complete the dimension wizard, you have the option to generate the schema <\/SPAN>on the spot or you can run the Schema Generation Wizard at a later time. The Schema Generation Wizard lets you create a new data source view for your time table or select an existing DSV. You can even choose to have the wizard populate the time table or you can leave it empty. You\u2019re also given the opportunity to specify naming conventions. By the way, your credentials are used to create the database objects so you\u2019ll need to be sure you have the correct permissions on the data source. This is a nice way to get started with a time table but you\u2019ll need to keep it up-to-date with an ETL tool and you can\u2019t customize it to have more or fewer columns.<\/FONT><\/P>
\nTime Dimension Options in SQL Server 2008 Analysis Services<\/o:p><\/FONT><\/B><\/P>
\nAs I mentioned in my previous blog entry, SQL Server 2008 Analysis Services (which I\u2019ll call Katmai from now on) gives you four choices for creating a dimension: <\/FONT><\/P>
\n\u00b7 <\/FONT><\/SPAN><\/SPAN><\/SPAN>Use an existing table <\/o:p><\/FONT><\/SPAN><\/FONT><\/P>
\n\u00b7 <\/FONT><\/SPAN><\/SPAN><\/SPAN>Generate a time table in the data source <\/o:p><\/FONT><\/SPAN><\/FONT><\/P>
\n\u00b7 <\/FONT><\/SPAN><\/SPAN><\/SPAN>Generate a time table on the server <\/o:p><\/FONT><\/SPAN><\/FONT><\/P>
\n\u00b7 <\/FONT><\/SPAN><\/SPAN><\/SPAN>Generate a non-time table in the data source (using a template)<\/o:p><\/FONT><\/SPAN><\/FONT><\/P>
\nThe second and third options relate specifically to a time dimension. If you select \u201cGenerate a time table on the server,\u201d you get the same result as the Server Time Dimension in Yukon and an almost identical interface in the wizard. The exception is that the hierarchy confirmation page is missing in Katmai \u2013 which is fine for me as I\u2019d rather fine-tune the hierarchy in the editor anyway.<\/FONT><\/P>
\nThe new option, \u201cGenerate a time table in the data source,\u201d is the same as Yukon\u2019s option to build the dimension from the Date template. You run the Schema Generation Wizard to design the table and optionally to populate it the first time. <\/FONT><\/P>
\nSo what do you do if you have a time dimension in your data source? The only option is to choose \u201cUse an existing table.\u201d On the Select Dimension Attributes page of the dimension wizard, which you use to select the columns from your table to include in your dimension, you have the ability to change the attribute type. This is the equivalent of mapping columns to time properties in Yukon, although I must say the interface is not ideal for this task. In short, more clicks are required to set up your time dimension from a table, but you have the benefit of getting a table and all the attributes exactly the way you want them.<\/FONT><\/P>
\nConclusion<\/FONT><\/B><\/P>
\nSo, functionally, nothing has really changed much for time dimensions in Katmai apart from renaming of options and some slight interface adjustments. If you base your time dimension on a table, the interface changes make the process to create the time dimension in the Analysis Services database a bit more tedious, in my opinion. Fortunately, these aren\u2019t tasks that you have to repeat every day and, if you want to reproduce the time dimension in another cube, you can always script it out rather than build it through the wizard. –Stacia<\/SPAN><\/FONT><\/P><\/p>\n","protected":false},"excerpt":{"rendered":"In my previous post, I covered the new dimension wizard and mentioned there were options for creating time dimensions that I would cover later. Now I’ll explain those options further. Time Dimension Options in SQL Server 2005 Analysis Services Let\u2019s start with a quick review of what happens in SQL Server 2005 (referred to as […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-468","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/stacia\/wp-json\/wp\/v2\/posts\/468","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/stacia\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/stacia\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/stacia\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/stacia\/wp-json\/wp\/v2\/comments?post=468"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/stacia\/wp-json\/wp\/v2\/posts\/468\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/stacia\/wp-json\/wp\/v2\/media?parent=468"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/stacia\/wp-json\/wp\/v2\/categories?post=468"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/stacia\/wp-json\/wp\/v2\/tags?post=468"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}