![]() ![]() I was asked at one point why we stored the full text representation rather than a code. I had to create fiscal time slices and calendar type slices to solve my requirement, and the names became Month Calendar and Period Calendar. For our implementation we had two calendars, but there is nothing that says I could not create more if needed. The cal_nm or Calendar Name column is used to differentiate between the different types of calendars stored in the table. The type of quarter (monthly or fiscal) is determined by the next column. For example, QTD is Quarter To Date and FQ is Full Quarter. Each of these codes represents a time slice. The cal_tmslc_mn_val contains values like MTD, QTD, YTD, FM, FQ, and FY. I am going to save the reference date for last. I will talk about each of these columns in a bit more detail in the following paragraphs. The last four columns contain the starting and ending dates for each time slice the first two for the current year and the last two are the matching dates for the prior year. The next two columns are codes for the time slice and calendar type. If you don’t need the flexibility of redefining the “to date” value in your environment this can be removed. Every time I use this table I need to provide that date, either via a prompt or condition of some kind. The ref_cal_dt is the reference calendar date. All of these requirements can be solved with the following table structure: Column Name Finally, I want to be able to compare the current year values with the matching prior year values. I need to be able to provide any reference date as my “to date” or input date for each time slice. The actual dates will change based on whether I am looking at a fiscal quarter or a calendar quarter. Time Slice Calendar Table DefinitionĮach time slice has a start date and an end date. #SQL SERVER TIMESLICE DOWNLOAD#There is a link to download the file at the end of this post. This post will cover slides 23 through 25 from my 2008 GBN Conference presentation. Just to prepare you, this post is a bit longer than most that I write, and gets fairly detailed. After I talk about the table design I will cover how I use it in the universe, as well as provide a few pros and cons about this solution as I have outlined it so far. In this post I am going to cover the design of a special table that we built in order to support our solution for these requirements. The resulting SQL should be as efficient as possible.The process of splitting each time-slice time period into its own SQL statement should be completely transparent.Users can provide any possible date as the “to date” for the time slices.Users must be able to select the type of calendar during the refresh process calendar types include Monthly and Fiscal.Each report is expected to have multiple time-sliced measures.I also shared the design requirements for a particular project I was on. #SQL SERVER TIMESLICE SERIES#In the first post in this series I defined what time-sliced measures are and why they can be useful in a universe. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |