data warehouse - Handling change of grain for a snapshot fact table in a star-schema -


the question

how handle change in grain (from weekly measurement daily measurement) snapshot fact table.

background info

for star-schema design want incorporate results of survey fact (e.g. in week 2 of 2015 80% of respondents have responded 'yes', in week 3 76% etc.) survey conducted each week, , have access result of survey (% of people saying yes week) , not individual responses.

based on (my interpretation of) christopher adamson's "star schema: complete reference" believe should use snapshot fact table these kind of measurements.

the date dimension fact should on week-level, , conformed rollup of more fine-grained date dimension other facts in other stars take place on daily basis.

here comes trouble

now decides want conduct these surveys daily instead of weekly. best way handle this? of options i'm considering:

  • change week dimension daily one, , fake old facts if happened on last day of week.
  • change week dimension daily one, , add 7 facts each weekly one.
  • create new star, daily fact , dimension , treat old 1 aggregate.

i'd appreciate input. please tell me if logic off, or question not clear :)

i'm not convinced snapshot. each survey response represents "transaction".

with appropriate date dimension can calculate yes/no percentages, rolled week.

further, enable show results "surveys issued on sunday night more responses", or "people respond on friday more answer 'yes'". (contrived examples)

following clarification, periodic snapshot. example of bank account balance used describe similar scenario.

a key feature of periodic snapshot every combination of every dimension should present. if grain monthly, every month record fact, if has not changed previous month.

i think key problem. knowing grain may change weekly daily, make grain daily. mean you'll repeating weekly value on every day of week, true representation of knowledge of fact; on wednesday knew value same monday.

if design etl right, won't need make changes when daily updates begin.

your second option 1 i'd choose in place.


Comments

Post a Comment

Popular posts from this blog

java - Spring Data JPA: Why findOne(id) executing delete query internally? -

i have 1 application using @manytomany annotation link 2 different entities 1 parent entity. not sure if have implemented properly. when execute .findone() on parent entity's repository find it, @ first fetch data along executing delete queries internally delete record mapping table (a table jpa creates after adding @manytomany annotation.). following generated sql queries , code snippet: java class @entity @table(name = "inventory") public class inventory { @id long id; // many other properties @manytomany(fetch=fetchtype.lazy) private list<stream> streams; @manytomany(fetch=fetchtype.lazy) private list<subject> subjects; } there no other annotation i've put in stream , subject entity class link inventory class. when run app, create following tables: inventory stream subject inventory_stream inventory_subject (inventory_stream , inventory_subject, 2 tables created automatically, don't k...
Read more

python - Mongodb How to add addtional information when aggregating? -

i beginner , wrote line pipeline works, wanna add other information output, screen name , or number of tweets.i tried add under $group gave me syntax error everytime here pipeline: def make_pipeline(): # complete aggregation pipeline pipeline = [ { '$match': { "user.statuses_count": {"$gt":99 }, "user.time_zone": "brasilia" } }, { "$group": { "_id": "$user.id", "followers": { "$max": "$user.followers_count" } } }, { "$sort": { "followers": -1 } }, { "$limit" : 1 } ]; i using on example : { "_id" : objectid("5304e2e3cc9e684aa98bef97"), "text" : "first week of school on :p", "in_reply_to_status_id" : null, "retweet_count" : null, ...
Read more

java - Incorrect order of records in M-M relationship in hibernate -

Image
i have 4 records in bridge table in mysql database. using @manytomany in hibernate, accurate order of employees incorrect incorrect of projects. following required material figure out doing mistake. this source. @entity @table(name="project") public class projectbean { @id @generatedvalue @column(name="project_id") private int projectid; @column(name="title") private string projecttitle; @manytomany(mappedby="projects") private collection<employeebean> employees; public projectbean() { employees = new arraylist<employeebean>(); } //getters & setters } @entity @table(name="employee") public class employeebean { @id @generatedvalue @column(name="employee_id") private int employee_id; @column(name="first_name") private string ...
Read more