data warehouse - How to populate fact table with Surrogate keys from dimensions

Could you please help understand how to populate fact table with Surrogate keys from dimensions.I have the following fact table and dimensions:ClaimFactsContractDim_SKClaimDim_SKAccountingDim_SKClaimNbrClaimAmountContractDimContractDim_SK (PK)ContractNbr(BK)ReportingPeriod(BK) CodeNameAccountingDimTransactionNbr(BK)ReportingPeriod(PK)TransactionCodeCurrencyCode(Should I add ContractNbr here ?? original table in OLTP has it)ClaimDimCalimsDim_Sk(PK)CalimNbr (BK)ReportingPeriod(BK)ClaimDescClaimName(Should I add ContractNbr here ?? original table ...Read more

data warehouse - better design for fact table where each row has a Start & End Date

My fact table contains details for clients who attend a course.To ensure i can get a list of clients registered on any particular day, I have not related the date dimension to the fact table.Instead i created a measure that does basic between logic (where startDate <= selectedDate && endDate >=SelectedDate)This allows me to find all clients registered on one single selected day. There are a few drawback to this however:-I have to ensure the report user only selects a single day, i.e. they cannot select a date range.-I cant easily do...Read more

data warehouse - Can non-additive facts be part of transactional fact table

I know there are three types of facts, and I've read that Transactional-Fact tables have fully additive facts which are the most useful type, but can non-additive facts be there as well? Or even semi-additive facts for that matter.I'm asking this because my teacher had this in one of her presentations:"While non-additive facts are not stored in fact tables, it is important not tolose track of them. For many processes, ratios are criticalmeasurements without which a solution would leave much tobe desired. Non-additive facts should be documented ...Read more

business intelligence - Data warehousing principles and NoSQL

with MongoDB, CouchDB and related technologies we can get faster querying so is this still valid?“A copy of transaction data, specially restructured for queries and analyses.” (R. Kimball The Data Warehouse Toolkit, 1996I mean, do we really need to restructure our data to an OLAP scheme to query it for analysis purposes? More specifically can drill-down, slice and dice and other reporting for analysis purposes be achieved with NoSQL (NOT necessarily with OLAP modelling)? Also could we overcome the "data subset" querying limitation of OLAP and r...Read more

data warehouse - Is it a must for a dimension table to have a primary key?

I have a question for you all. I searched first for any existing same question but only found related ones but not specific to my question. So here it goes:Is it important that my dimension tables have a primary key? I am asking this because the way I designed my data warehouse is that I managed my surrogate keys on the Normalised Data Store. Then the surrogate key is just passed along to the dim tables. Any updates from the source systems will reflect first on the NDS (type 1 or override). So basically, I do not keep track of the historical va...Read more

data warehouse - Understanding Slowly Changing Dimension Type 2

I am having difficult time understanding how to use slowly changing dimension type 2, in my scenario.I have gone through different tutorial websites but they don't fit.I have an employee dimension table containing:+-----+---------------+------------+------------+| id | employee | designation| Location |+-----+---------------+------------+------------+| 1 | Ola | CEO | Newyork || 2 | Ahmed | DEVELOPER | California || 3 | Ola | Manager | California |+----------+----------+------------+------...Read more

data warehouse - Star schema [fact 1:n dimension]

I am a newcomer to data warehouses and have what I hope is an easy question about building a star schema:If I have a fact table where a fact record naturally has a one-to-many relationship with a single dimension, how can a star schema be modeled to support this? For example:Fact Table: Point of Sale entry (themeasurement is DollarAmount)Dimension Table: Promotions (theseare sales promotions in effect when asale was made)The situation is that I want a single Point Of Sale entry to be associated with multiple different Promotions. These Promotio...Read more

business intelligence - How to handle denormalization in B.I data model

As far as I know, normalization is done to avoid inconsistency in the database. By normalizing we: reduce data redundancy, andprotect data integrity.That's why most OLTP databases are in 3NF. Different databases from OLTP come together in a data warehouse. (DWH, OLAP). DWHs are denormalized (1FN), and is obvious it has to be like that, because the main table of a DWH has hundreds of columns.From that DWH we can build several data marts that we would later use for doing analysis with a BI reporting tool (Cognos, QlikView, BO .. )The problem is ...Read more

data warehouse - Surrogate Keys in Datawarehouse

I want to understand how surrogate keys are leveraged in real-time DWH environments. I get that they add the benefit of not being dependent on source-generated data to store each dimension key and also avoid having composite key built out of natural keys from dimensions in the fact, For eg, (prod id + cust id+ time id)But does it not add the complexity of having to maintain the lookup of (natural key, surrogate key) while we load data into facts. I have been working in BI/DW teams for last 3 years and we do not maintain any surrogate keys in ou...Read more

data warehouse - Business Intelligence

I need please some clarifications about the BI architecture. According to what I understood, the first step is to gather data from different data sources, clean it, and load it to a data warehouse through an ETL. The Data Schema of the datawarehouse shouldn't be relational, and should support fast business operations (ex. Star schema), then finally we have some reporting tools such as qlick, Tableau ...etc. My question is, what is OLAP and in which step does it come to existence?thx,...Read more