database design - Approach to implement TIME SPAN/PERIOD dimension for SSAS cube -


we have huge traffic database (call data records). due amount of records , hardware limitations cant’ use source detail records reporting. summarize records in relational database using 15 minutes intervals , truncate source data immediately. these summarized records (events on time period) stored in warehouse , used fact table (molap). time spans/periods stored in sys_time_slices dimension table, simplified versions is:

create table [dbo].[sys_time_slices] (   [id] [int] identity(1, 1) not null,   [datetime_start] [datetime] not null,   [datetime_end] [datetime] not null ) on [primary]

first records are:

datetime_start      datetime_end 01-jan-13 00:00:00  01-jan-13 00:15:00 01-jan-13 00:15:00  01-jan-13 00:30:00

now putting cube , not sure how according best practices. have checked “step-by-step” book, internet tutorials on time dimension , related bi, still no clue. marking time_slices dimension time produced weird results. marking datetime_end dateended type produced more strange results.

i relatively new ssas have 15 years of experience dealing sql , customers reports, understand want , how in regular sql.

we have provide reports hour, day, week , month granularity. can done manual modification , configuration of attributes time_slices dimension hierarchy involved (w/o ssas special magic). have bells & whistles of time business intelligence (day on day growth , on).

more important old data (6+ months) never updated , source warehouse table being archived. in cube need old data day level of detail saving space on server. somehow achieved partitions – not sure time specifics.

given considerations above there recommended/common approach time span/period dimension? hints? books on subject? should change our warehouse logic?

this related ssas - facts happened on time range, there bit different question. using 2008r2 can upgrade 2012 needed.

in cases, better use 2 separate dimensions date , time. time in case have 96 (24 * 4) members. these aggregated hours, , depending on reporting/user needs other time ranges shift names. use integer key this, maybe "speaking key" 1415 time span 2:15 pm 2:30 pm (i. e. hour of start * 100 + minutes of start). make easy calculate foreign key in fact table datetime_start column. can assign meaningless integer surrogate keys each record.

the date dimension contain days granularity, , have attributes day of week holiday yes/no, month, year, quarter, ... depends on reporting necessities. better prepare more fewer attributes here, makes life reporting easier. , dimension table has few records, there no need restrict number of attributes much. can use similar structure integer primary key of table, e. g. 20131009 october, 9, 2013. again, arbitrary integer surrogate key well.


Comments

Post a Comment

Popular posts from this blog

c++ - CryptStringToBinary API behavior -

i seeing strange behavior cryptstringtobinary cryptography api. please see below code (config: x64 debug): #include "stdafx.h" #include <windows.h> #include <strsafe.h> #include <iostream> #include <exception> void main() { dword dwskip; dword dwflags; dword dwdatalen; //lpcwstr pszinput = l"jaaaaaecaaadzgaaakqaagdnnl1l56bwgfjdgr3rpxtqqqn6daw3usv2emkjym4t"; //this works fine lpcwstr pszinput = l"mytest"; //doesnt work, api returns false,error code 0x0000000d // determine size of byte array , allocate memory. if(! cryptstringtobinary( pszinput, _tcslen( pszinput ) + 1, crypt_string_base64, null, &dwdatalen, &dwskip, &dwflags ) ) { dword dw = getlasterror(); //0x0000000d: data invalid throw std::exception( "error computing byte length." ); } byte *pbytebyte = null; try { ...
Read more

java.util.scanner - How to read and add only numbers to array from a text file -

i'm learning java , have question regarding reading file want read numbers file contains strings well. here example of file: 66.56 "3 java 3-43 5-42 2.1 1 and here coding: public class test { public static void main (string [] args){ if (0 < args.length) { file x = new file(args[0]); try{ scanner in = new scanner( new fileinputstream(x)); arraylist<double> test = new arraylist<>(); while(in.hasnext()){ if(in.hasnextdouble()){ double f=in.nextdouble(); test.add(f);} else {in.next();} } catch(ioexception e) { system.err.println("exception during reading: " + e); } } my problem add 66.56,2.1 , 1 doesn't add 3 after "3 or ignores 3-43 , 5-42 can tell me how skip strings , add doubles here? thanks all said 3 ie; "3, 3-43 , 4-42 strings either u read string , split , check number @ " , - or put in space between characters , integers. jvm after compilation tre...
Read more

iphone - Three second countdown in cocos2d -

i trying have 3 second countdown display when level beat in game. did: -(void) gamesegmentbeat { [self pauseschedulerandactions]; // overlay game opaque background ccsprite *opaquebg = [ccsprite spritewithfile:@"background1.png"]; opaquebg.position = screencenter; [self addchild:opaquebg z:10000]; // these 3 labels 3 seconds cclabelttf *three = [cclabelttf labelwithstring:@"3" fontname:@"helvetica" fontsize:100]; three.position = ccp(screencenter.x, screencenter.y); cclabelttf *two = [cclabelttf labelwithstring:@"2" fontname:@"helvetica" fontsize:100]; two.position = ccp(screencenter.x, screencenter.y); cclabelttf *one = [cclabelttf labelwithstring:@"1" fontname:@"helvetica" fontsize:100]; one.position = ccp(screencenter.x, screencenter.y); // secondspast specified in update method secondspast = 0; if (secondspast == 1) { [self addchild:thr...
Read more