Hi I have a loan application accumulating snapshot table which when changes creates a new record with valid from/to dates The PK can either be a) LoanId + ValidFromDate or b) A surrogate key whic...
https://kimballgroup.forumotion.net/t3508-accumulating-snapshot-table-and-bridge
Hello, I have a DW that contains data from our managed support department. One of the facts that I hold in our data warehouse is support incidents. Another is a fact table for server availability...
Hello, I am fairly new to this, and hope that someone may have some insight on a project our company is looking into. We have acquired several thousand mortgages from a handful of originators, an...
https://kimballgroup.forumotion.net/t3504-data-warehouse-for-mortgage-tracking-need-some-advice
I need to model a scenario where a person can be a primary account holder and can have some dependents sharing the same contract. The business wants to look at the total revenue generated every �...
https://kimballgroup.forumotion.net/t3505-data-with-different-granularity
I have a scenario where I have two dimension tables to hold the diagnostic related grouping information. One for the previous version which is Medicare Severity Diagnostic Related Grouping(MS) ...
https://kimballgroup.forumotion.net/t3506-modeling-a-dimension-table
I am creating a fact table for every interview slot that's created. If the interview is re-arranged, a new interview slot is created. There is a status on the interview. So in this scenario it wo...
https://kimballgroup.forumotion.net/t3493-is-it-acceptable-to-update-fact-row-with-latest-status
Hi everyone, i hope you are doing well. i have to find a way for modeling the following case : i have to be able to retrieve the weights (my measure) of a portfolio and it's constiuants having th...
https://kimballgroup.forumotion.net/t3494-many-to-many-relationship-help
Hello, our main Dimension has the following structure: Company Dimension: Division_Key Division_Name Company_Name We have now a single case, where this is not sufficient. Our HR data fact table ...
https://kimballgroup.forumotion.net/t3492-one-to-many-as-bridge
I have three facts that use their own distinct statuses for each process. Should I have all of the statuses in one dimension and have a status type to identify, or should they be in 3 separate di...
https://kimballgroup.forumotion.net/t3481-role-playing-dimension-or-seperate-dimensions
Dear All, We have a FACT Table which is recording data for each staff each day. The measure stored is the number of hours worked. The report produced is a list of staff who have not completed 12 ...
https://kimballgroup.forumotion.net/t3486-aggregating-data-at-different-levels-of-granularity
I am designing a fact table to track Student Applications which has a grain of application and status, with a new row when the status changes. Some applications have a free text notes field. I ha...
https://kimballgroup.forumotion.net/t3487-update-dim-id-in-fact-rows-or-create-new-fact-row
Hi, I need to deliver some balance sheet reporting from the warehouse. I current already have a GL Transaction fact, which holds data by GL Account, Posting Date, Reporting Date, Customer etc. Is...
https://kimballgroup.forumotion.net/t3464-balance-sheet-fact
Hello, I have a purchase order headers, lines, and distributions table. There are cases where a header doesn't have a line and/or distribution but the users want to report on all pos regardless m...
https://kimballgroup.forumotion.net/t3488-reporting-at-all-levels-master-detail
I'm currently designing a star schema for a business process called "Activities". Employees of my company have to keep track of their work for customers by writing activity reports. First they ha...
https://kimballgroup.forumotion.net/t3484-problem-with-multi-valued-dimension
Hello, I have a question on modeling about different versions of budgets. During the year (every quarter) the budget is adjusted with new values (for the rest of the year). B4 for the whole year ...
https://kimballgroup.forumotion.net/t3477-budget-and-different-versions
My transactional fact table is tracking status change. To enable me to do point in time analysis, I need to store the source row updated date. I know normally all date/times in facts will be sepa...
https://kimballgroup.forumotion.net/t3483-datetime-or-dateid-and-timeid-in-transactional-fact-table
I have the need to track changes on certain pricing/lock aspects of a loan. Our lock desk is allowed by the source system to change things like Lock Expiration Date, Loan Program Name, Loan Sell ...
https://kimballgroup.forumotion.net/t3482-using-a-dimension-table-to-track-changes
I am designing an Admissions data mart for a college. Each application has a stage which needs to be tracked (App Received, Interview, Offer Made, Withdrawn etc.). I have designed a fact to be ab...
https://kimballgroup.forumotion.net/t3478-do-i-need-multiple-fact-tables-or-dimensions
I have a situation where i need to take a monthly snapshot for payments , scheduled future payments and expanses and calculate variation at point of time. We pay lump sump amount to external orga...
https://kimballgroup.forumotion.net/t3480-snapshot-summary-fact
Hi I have a individual demographics dimension which holds all possible values of the following attributes: age-group(5), religion(15),ethnicity(20),Gender(3), sexualOrientation(5) which gives 225...
https://kimballgroup.forumotion.net/t3479-modelling-multi-attribute-dimensions
I am confused as to what's best design option for the following scenario. Insurance business. I have a monthly fact table say for Premium trnasactions (Grain is at the rider level) which is conne...
I was reading through some Kimball design tips for data modeling and would like to get more information on dimension and fact table design tips. The current situation that am in right now is, I h...
https://kimballgroup.forumotion.net/t3475-dimension-and-fact-table
Dear Everyone, I have modelized a Star from an operational of Bugs Tracker ( Mantis) I have two options. 1) M:N 1 user have many bugs and 1 bug have many users 2) The relation M:N has been denorm...
https://kimballgroup.forumotion.net/t3473-many-to-many-relationships
I'm fairly new to dimensional modeling. I have an existing model that already has an employee dimension for people who work at the company and a contact dimension for people who do not. I now nee...
https://kimballgroup.forumotion.net/t3472-how-to-model-a-generic-user
I am trying to model a set of dimensional tables for conference events. I am not sure how to properly handle all the permutations of event/sessions/leader/registrant elements. Here are the main r...
https://kimballgroup.forumotion.net/t3471-help-with-complex-one-to-many-relationships
I'm breaking up a process I'm modeling (a "Bid" in this case) into various dimensions. I will be modeling the bid as a factless-fact simply identifying the existence of a bid. After breaking up a...
https://kimballgroup.forumotion.net/t3439-junk-dimension-looking-more-like-a-header-dimension
Hi, I need a short economic example from the process data --> ODS --> RDL --> DDL --> frontend. I don`t know a good example for this. I hope you can help me. Thank you
https://kimballgroup.forumotion.net/t3469-economic-example-data-ods-rdl-ddl-frontend
I am trying to create a data mart around training completion/non-completion for our company's learning and development program. An obvious candidate dimension would be 'course', but there are two...
I have a client that wants to do detail reporting from an EDW The reporting information will include a lot of degenerate data such as notes, phone number, status and other header information. The...
Hi All, I have a situation here, we get two type of claims from hospital (establishment) . One set of data is at Episode level - Unique ID for each visit including clients details, cost per visit...
https://kimballgroup.forumotion.net/t3466-same-data-at-different-granularity
Hi, I am designing a dataware house for a Survey comp, where am trying to figure out what can be considered a fact. All i have in my current sql db is inspenction data in the form of Q&A. w...
Hi, There is great emphasis placed on ‘Conforming dimensions’ when designing a DW. I’m a little confused, as having conformed dimensions seems the most obvious thing do and I struggle to an...
https://kimballgroup.forumotion.net/t3465-conformed-dimensions
Currently, I am building fact table base my ODS tables and dimension tables. My ODS Table like 1.dateWiseData sid_date total_impressions revenue 2.devices(Mobile PHONE,PC,etc) sid_date device_id ...
https://kimballgroup.forumotion.net/t3461-datawharehouse-fact-table-geneate
Apologies if I have the terminology wrong and don't explain things very well, as I am relatively new to this and am getting confused on a design decision and just wanted some advice. I work in th...
https://kimballgroup.forumotion.net/t3462-dimensional-model-with-different-granularity-to-cube
TENDER
https://kimballgroup.forumotion.net/t3463-creating-facts-and-dimensions-from-raw-data
Hi all, i need your help about the design of a dimensional model about the employeed cost. I particular, i have the following dimensions: - center of cost - type of job contract (full time, part ...
https://kimballgroup.forumotion.net/t3460-help-in-design-about-employees-cost
Hi, We have "party transaction table", for which lets say grain is one row per party/customer/trans id/trans amt/ trans time stamp. My user req is to flag to all those parties on daily basis, whe...
I have a reporting need where we have the following scenario. Every year there is an operational risk review done for business processes/applications. The life cycle goes as follows: Creation of ...
https://kimballgroup.forumotion.net/t3452-timespan-accumulating-fact-table
I am trying to develop a data model and tried looking up online for naming standards for conceptual, physical and logical data models. Couldn't find any helpful links so far, and wondering if any...
I am working on building a data model for claims data. The business want to know the complete history of transactions that happen on a claim along with current view. I was thinking of having this...
https://kimballgroup.forumotion.net/t3444-accumulating-snapshot-fact-table
I am in process of designing a customer dimension for our sales DW and I need some advice. Here is our customer data example. In our customer data, let's just say we have 2 GE subsidiaries as ind...
https://kimballgroup.forumotion.net/t3449-customer-dimension-modeling-question
Hi all, I have question regarding ragged hierarchy and bridge table. We have product dimension with over 1 Million products and 'n' number of product category with different hierarchy level. Ever...
https://kimballgroup.forumotion.net/t3446-ragged-product-category-hierarchy-and-bridge-table
HI , we have few Date columns as Fact .Null's are replaced by default dates '1899-12-31'. Our users are using tableau for self service reports now and they are finding it pretty hard to repl...
https://kimballgroup.forumotion.net/t3448-null-dates-in-facts
Hi I am creating a dimensional model for Home Loan Applications where each home loan application that can have multiple loans associated with it and also multiple applicants There will be lots of...
https://kimballgroup.forumotion.net/t3373-home-loan-application
Which methods you prefer in following two cases Q1: Consider the following hierarchy (Parent - Child): Company -> Department -> Project Design1: Create "Flat dimensions" with parent attributes as...
https://kimballgroup.forumotion.net/t3445-dimension-hierarchy-and-repeated-attributes
http://sqlblog.com/blogs/jamie_thomson/archive/2009/11/28/debunking-kimball-effective-dates.aspx
https://kimballgroup.forumotion.net/t1771-did-anybody-read-this-article
Hi, I need to integrate three live core systems and currently modeling the customer dimension. During the physical design I repeatedly ask myself do I need to create a source system ID (surrogate...
https://kimballgroup.forumotion.net/t3443-customer-dimension-from-multiple-systems
can we have a datetime column in a fact table?in which scenario's is this correct to have a datetime in a fact table. In my Facttable i stored date as int.They are just the milestone dates repo...
https://kimballgroup.forumotion.net/t3442-datetime-fields-within-a-fact-table
THis is how my Org Dimension table looks like.This table keeps getting updated like once in two months.As the levels keep changing in an organization or so.I am pretty new to DM and been asked to...
https://kimballgroup.forumotion.net/t3440-org-hierarchy-type-2
Is it typical to have two separate layers in data warehousing environments? One is for ETL layer and the other is for presentation(access to business users) and reporting needs. The data in the t...
https://kimballgroup.forumotion.net/t3436-presentation-layer