Showing posts with label interesting. Show all posts
Showing posts with label interesting. Show all posts

Sunday, March 25, 2012

Dimension Hierarchy members processed incorrectly


I have an interesting problem with Analysis Services that I was able to resolve (sort of), but I would appreciate some feedback to see if this is known behavior, a bug, or if I'm just setting up my dimension incorrectly.

Basically, the intermediate members of a hierarchy are showing up incorrectly in my date dimension. For example, when I browse the dimension, I'll end up with a tree that looks like this:

All CY
- CY 1998
- Qtr 1 CY 1998
- January 1998
- Jan 1, 1998
- Jan 2, 1998
- etc.
- .....
- .....
- CY 2005
- Qtr 1 CY 1998
- January 1998
- Jan 1, 2005
- Jan 2, 2005
- etc.

As you can see, the years are correct and the lowest element (date) is correct, but the names of the intermediate elements (quarter and month) are incorrect. These intermediate elements are the same for each calendar year (i.e. it's always 1998, which is the earliest year we have data for in the table).

I've included the table/hierarchy structure below for reference.


So the way that I (sort of) resolved the issue was by using the calculated name values as the keys for the attributes instead of using the integer-based keys that I was using. It seems that you can't/shouldn't have key values that repeat for different higher-level elements (i.e. the month_si value of "12" (December) is present in the table for rows from CY 1998, 1999, 2000, etc.). I'm guessing here, but it seems that when it went to build the hierarchy underneath, say, CY 2006, it found key values of "1","2","3", & "4" for [quarter_si] (correctly so). It then pulled the value from [Column15] for those key values, but instead of limiting itself to data underneath CY 2006, it looked at the whole table, and thus pulled the name values for those quarters from the first records in the table, which happened to be "Qtr 1 CY 1998","Qtr 2 CY 1998" and so on. But since "Qtr 1 CY 2006" is only present in CY 2006, using "Qtr 1 CY 2006" as the key and name value gave me the correct hierarchy.

Unfortunately, this resolution doesn't work because the keys won't order correctly (i.e. "June 2006" comes before "May 2006" under "Qtr 2 CY 2006") and this is a show-stopper.

What's odd is that I converted this dimension directly from SSAS 2000, and this was not a problem we encountered in 2000. Is this issue resulting from something new introduced in SSAS 2005? Is there a property flag that controls this behavior?

Or do I just not have a good understanding of how dimensions work? ;)

I would appreciate anyone's feedback or comments.

Thanks in advance,
Jamie C.


TABLE/DIMENSION/HIERARCHY INFO:


The underlying table used for the dimension is "dbo.dci_Date" - the structure of the table (including calculated columns within AS) is this:

[dbo].[dci_Date]
[date_id_si] [smallint] NOT NULL,
[year_si] [smallint] NOT NULL,
[quarter_si] [smallint] NOT NULL,
[month_si] [smallint] NOT NULL,
[date_sd] [smalldatetime] NULL,


The following "virtual" columns are also present as calculated columns - I've included the formulas for reference:

[Column4] [WChar] = left("date_sd",11)
[Column13] [WChar] = 'CY ' + convert(char,DatePart(year,"date_sd"))
[Column15] [WChar] = 'Qtr ' + convert(CHAR, DatePart(quarter,"date_sd")) + 'CY ' + convert(char,DatePart(year,"date_sd"))
[Column17] [WChar] = convert(CHAR, DateName(month,"date_sd")) + convert(char,DatePart(year,"date_sd"))


Here's an example of what a couple of rows from the table look like (including the values for the calculated columns) - I've included the min & max as well:

[date_id_si],[year_si],[quarter_si],[month_si],[date_sd],[Column4],[Column13],[Column15],[Column17]
-729,1998,1,1,1998-01-01 00:00:00Z,Jan 1 1998,CY 1998,Qtr 1 CY 1998,January 1998 ** MIN
2495,2006,4,10,2006-10-30 00:00:00Z,Oct 30 2006,CY 2006,Qtr 4 CY 2006,October 2006
2496,2006,4,10,2006-10-31 00:00:00Z,Oct 31 2006,CY 2006,Qtr 4 CY 2006,October 2006
2497,2006,4,11,2006-11-01 00:00:00Z,Nov 1 2006,CY 2006,Qtr 4 CY 2006,November 2006
4270,2011,3,9,2011-09-09 00:00:00Z,Sep 9 2011,CY 2011,Qtr 3 CY 2011,September 2011 ** MAX


The hierarchy for this dimension (Calendar Year) is built with the following attributes:

Year -- key: [year_si] name: [Column13]
Quarter -- key: [quarter_si] name: [Column15]
Month -- key: [month_si] name: [Column17]
Day -- key: [Column4] name: [Column4]


Hello. You must assure that each attribute in a dimensions has an unique key. Like month, if you represent this with a month number like 1 to 12, this is not unique over years. To solve this problem you simply go to the key property of the attribute and change that to a collection by adding each year to column. By clicking to button to the right for the key column you will see the "Data Item Collection editor".

I would recommed to use numbers or collections of numbers as keys for year, quarters and months. Change the name column to something for informative instead, like a text based description. Check that the attribute is ordered by key, not by name in the properties for each attribute.

HTH

Thomas Ivarsson

|||

Thanks Thomas! I had a feeling that was the case, but it never hurts to be sure.

Best,

Jamie C.

Sunday, March 11, 2012

Differential Backups

Hi Folks!

I did a few searches and found some interesting threads but was not able to understand much. :)

I recently jumped from support to a DBA position and am learning about SQL Server 2000.

My dilemma: We have a SQL 2000 server with about 500GB's worth of databases that has some jobs from the app locking up due to tape backups. I am performing Full nightly and 1/2hour transaction SQL backups. We run daily differential and weekly full tape backups. It is taking 14 hours to run tape backups daily, causing some of the application jobs to fail. So to test it, I stopped the tape job to run one night and none of the App jobs failed.

So, I am hoping that if I start running nightly differentials, weekly fulls, and 1/2 hour trans, it should work. However, I am not very familiar with Differential jobs as till today i have been using maintenance plans. :confused:

My ideal scenario is this: Differentials start on Sat morning and stop before the weekly backup runs on Friday and then delete the older file when starting a new one. Is this possible? how?

C= OS, D=Backup (Raid 0), G=Data (Raid 5), E=Logs(Raid5).

Any help would be appreciated, and obviously if I have missed some piece of information, please feel free to ask.You're plan seems reasonable. So what's the problem, can't you choose differential backups in your maintplan?

To give you an alternative...
We had similar problems as we're backing up several TB of databases each day. We eventually solved this by using multiple tape units and decreased the size of the backups by using a compression backup tool.|||Haha.. the problem is this:

I can setup a differential job to create a diff file and then append to it till Friday evening. But Sunday I would want a new one to start OR 'clean' the old one and recreate it OR delete the old one and start a new one. You know what i mean? like the maintenance plan does for tlogs/full backups..

Does that make sense? We do not have a third party compression tool and we're cheap .. heh.

oh btw, can't create a maintenance plan for differentials in SQL 2000. :)|||Do the backups need to be appended to an existing one? You can create seperate files for each full, diff and tx backup.

This also has the advantage that you can restore a smaller volume from tape when, say, you wanted to restore until a certain monday.|||Hi Lexiflex,

Please help my dumb ass understand this right.. Heh..

So, i will have 3 jobs right?
1. A weekly full backup that runs on sat and after done deletes the older file
2. Transaction logs that run every 1/2 hour and delete files from 24 before after done.
3. This is where the confusion is..

A differential job that creates a nightly file or appends to it and then when the new week starts, it starts from scratch...

So when it is time to restore, I restore the weekly full then the appended diff file or the diff files from each night after the weekly and then transaction logs upto the time of failure.. right?

I am short on space on the server so that's why i need to delete the older files..

Makes sense?|||Step 3 should be:
3. A differential job that runs nightly from sun - fri and deletes the previous diff backup

I get the feeling your confusion is how to tell the differential backup when to start anew. Don't worry about this because SQL remembers when the last full backup was made and starts from there.

As for the other question:
So when it is time to restore, I restore the weekly full then the appended diff file or the diff files from each night after the weekly and then transaction logs upto the time of failure.. right?
Not quite, a differential backup in SQL 2000 consists of the data that has changed since the last FULL database backup. So when restoring:
1. Restore the weekly full
2. Restore the diff closest to but before the failure
3. Resore the tx backups until just before the failure

But, as always, don't take my word for it but also look it up in the BOL. Search for differential backups, it also suggests some strategies.

Hope this helps.|||Sweet!

It makes sense now.

BACKUP DATABASE 'mydb' TO DISK = N'D:\MSSQL\Backups\mydb.bak' WITH INIT , NOUNLOAD , DIFFERENTIAL

This is it right? Then I scheduled it to run nightly.

Thanks for your help!|||This way you're effectively overwriting (WITH INIT) the previous backup. Make sure that one is securely on the tape!

We always put a date, time and backup type in the file name so we can tell them apart and order them easily. But that's a matter of choice.

Before committing to this solution I recommend doing some tests with backing up and restoring this way (not on you production environment!). You will get a feel for doing it and if you document along the way you'll have your emergeny backup plan 75% finished :cool:

Afterwards make it a point to test restoring (tape to disk to SQL Server) once every few months to check if everything is still in working order (believe me, I've experienced tape units merrily messing up backups for month without the slightest hickup).

Have fun! :D

BTW. NOUNLOAD works only for tape backups.