Hi All,
I want you an urgent respones, I want to add a column to a Dim_table and Fact_table, the Dim_table is sourcing from different database table and now the new column is going to source from another DB, thus without changing the structure of the table relationship can i add a column? To make it clear Dim_table has got Clomun1 from table1, Clomun2 from table2, Clomun3 From table3, Clomun4 From table4. Now I want Clomun5 From table5. How do i add and let it source from table5.
Thank you for your qiuck response.
Hi I have a fact table in which i have a String column now i want to show it in the mdx queries..when i add it as a measure it shows some numerical values in the cube i cannot even add it as a member propeties due to some datwarehouse design constraints..so can anyone out there help me please....it urgent Thankx in advance regards Hemant
I am building a health care application that marries transaction-level data (health care services provided) with person-level characteristics that have a time-dimension. The person-level characteristics are diseases that the person has (these disease all have a start and some have an end date). The diseases are stored in a table in which the foreign keys are a person-identifier, a time identifier (month/year) and a surrogate for the disease. Persons can have more than one disease at a time (the diseases are NOT mutually-exclusive). There are no measures in this table. The transaction table has a foreign key for person and time (month/day/year), a procedure code (the type of service rendered) and money (the cost of the services).
How do I answer the following questions:
What is the total cost of care (the sum of all service costs) last year for persons with "disease A"?
What is the total cost of care last year for persons with "disease A" AND "disease B"?
What is the total cost of care last year for persons with "disease A" OR "disease B"?
I've tried a factless fact table but can't get it to work. If anyone has the right solution and can communicate to me before I slit my wrists, I would be greatly appreciative!!!
What is the best way to move data from Online system tro data warehouse? I have created 3 dimension tables(product,date and customer tables) and I wanna create fact table and get foreign keys from dimension tables. What is the best method to do that in SSIS?
I am new at SSIS and I am trying to create a Datawarehouse using SSIS. I have the data files as flat files I have the Dimensional Model ready on Paper and Now I need to use the SSIS for the ETL process.
I am trying to figure out how to make dimension tables in SSIS? I mean I want to create the 5 Dimension tables and then create a Fact table out of it but I cant understand where to start? Can any one tell me how we create Dimesion tables in SSIS. Like one of the dimesion tables I need to create uses 2 flat files and is like a flattened dimension, How would I create this in SSIS?
Even if there is any tutorial which shows this step by step do let me know. I would really appreciate any guidance on this.
1. 2 Dimensional tables(Parent), DIM1 with 50000 rows, and DIM2 with 1000 rows
2. Fact 1 with 50 columns, 25 Million rows and with FK to DIM1 and DIM2
3. Fact 2 with 40 columns, and 25 Million rows and with FK to DIM1 & DIM2 tables.
Actually the fact 1 and fact 2 have same related data but since our Analysis cube person wanted the fact table not to have more than 50 columns we divided the tables into 2, but they have the same compound key.
Above said, I have a situation where I have to select all the columns, in both fact tables, and do a group by. I wrote the query and ran "Analyze Query in the Database Engine Tuning Advisor" for it. It gave bunch of recomendations about the statistics and indexes which I created. When I executed the query the result came up in matter of seconds, which was good.
In the query I had a condition having MarketName='Bridgeview' and DateID = 344 (FK of today-1).
When I wanted the data for last 30 days I changed to DateID in ( > FK of today -32 and < FK of today), the query responded and worked fine.
But when I changed the query to get MarketName='Aurora' (other than I used when I ran Tuning Advisor), the result returned is empty set. When I removed the MarketName condition, it is supposed to return all markets' data, but it returns only Bridgeview data.
I know the data is in the table for all markets, since reports are rendered from these fact tables for all of these markets(also ran queries to check the fact table data).
I am unable to point out the reason why the query behaves like this. It responds to the date change, but not to the MarketName change.
I really appreciate if anyone can help me point out the problem.
i have a fact table which loads through package,when i m trying to load this table by running the package,i m truncating the fact table and loading the fresh data,instaed of this without truncating the fact table i have to implement the incremental logic in this.
For this i can use SCD or Conditional split,but problem here is i have many source tables to load this fact table,so its very difficult to trace the changes in different source tables.
Hi there, my question is really simple. I want to setup an automatic task in SSIS that drops the tables in the target database and substitutes them with tables from the source database. We are talking about two or three dimension tables and one fact table. The dimension tables are pretty small. The fact table will contain, at maximum, 300,000 rows and 12 columns. I do not use delta or flag historisation btw. What tasks in SSIS would you suggest to use?
I have situation where I get data from SRC Flat file and have to load Dimensional table and also fact table, using same data flow(have no other choice since I have to unpivot some src data). Since I have to load both tables in same data flow, I have to have a way to put load ordering constraint (I know informatica allows that). Does any one have any idea on how this can be done in SSIS?
I have a csv file of quotes. I need to IMPORT the quotes into my SQL Server database. I created the table with an autonumber identity column, but the IMPORT fails because I can't INSERT NULL into my identity column. There is no option to autonumber or anything when I click the TRANSFORM button on the IMPORT wizard. So, I have decided to allow NULLS on the ID column and add the numbering later. I'd rather not have to manually number it, is there a way to do it with a query? I treid just setting it to not allow NULLS and to auto-incrememnt, but that didn't work....
Iam new to SQLl2005. Iam using DTS to transfer data from my source to the warehouse. I have a couple of tables in my source whein I have to join these to tables fields and insert the same in teh warehouse fact table. I have used a Join query in my Oledb source component, What other component needs to be used to insert the data into the fact table. I also need to extract same data with aggregation and insert the same into an another Fact table.
I have a fact table with one column storing string values. I need to use this column as a measure, but as different measure. Here's a snapshot of the fact ( with 4 columns)
#1 #2 #3 #4(value) 1 1 2007-12-20 Male 2 1 2007-12-20 French 1 2 2007-12-20 Female 2 2 2007-12-20 English
I have a dimension which is like id name 1 Gender 2 Language
Is there a way to get measures from column 4 in fact table - so that I can give reports like how gender graph during a time period or language usage during a time period. The design will have more than 2 dimension members, so fact column can hold more data like location, etc...
Can you please suggest a way to handle such kind of fact table?
When I try to create a measure based on that column (and 'no aggregation') - I get error like - "Error 1 Errors in the metadata manager. The data type of the 'value' measure is not valid because the data type of the measure is a string type."
I am modelling cube in SSAS. Cube has around 20 dimensions and 6 fact tables. Some of the dimensions are common among the fact tables. e.g. Time dimension. Fact_PNL has 3 date columns for those we have 3 role playing dimensions in the dimension usages.
Another fact table has 5 date columns for them as well we have separate role playing dimensions in dimension usage tab. We have a common dimension Company which is foreign key in all fact tables. We might need to combine the data from multiple facts to get final output.
Should i create 6 role playing dimension for each of the fact table or use the same dimension for all fact tables?
Role playing dimensions should be created when we have multiple columns pointing to the same dimension ?
I built my first tabular model and see that my fact tables are also appearing as dimensions. In Multi dimensional mode i could choose which are the dimensions. How do i do that in tabular model.
I created a Fact Table with 3 Keys from dimension tables, like Customer Key, property key and territory key. Since I can ONLY have one Identity key on a table, what do I need to do to avoid populating NULLs on these columns..
I'm currently setting up a Tabular Model to do some research between several fact tables. In this example i have two fact tables (table 1 and table 2) which I've created a 1 to 1 relationship on phone number. Typically I create a relationship between these tables to find common data between the two. However, in this case I am trying to figure out the best way to model the data so that I can easily surface data from one table that does not exist in the other. I would liken this to a LEFT JOIN or a WHERE NOT EXISTS in SQL.
Table 1 has all of the data and Table 2 Only has a subset of the data from Table 1. What I'm trying to do here is display what attributes in Table 1 may play a part in records not existing in Table 2. What is the best way to model this?
I have below tables in my power pivot.Fact 1 & Fact 2 - connected directly to Mainframes - Data is from the same table broken up due to size of the data.Date Table - Relation ship between both the fact tables.How do i create a summary pivot to get the number of tasks that have been completed in each month.
Say you have a fact table with a few columns that all reference the same key column in a dimension table, you want to write a view to return the information for those keys?
USE MyTestDB; GO SET NOCOUNT ON; IF OBJECT_ID ('dbo.FactTemp' ,'U') IS NOT NULL DROP TABLE dbo.FactTemp;
[Code] ....
I'm using very small data at the moment, and the query plan and statistics don't really say which way.
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
I need to create a table (Named as C) with a foreignkey column. That column should references with a primarykey column in table A and a primarykey column in table B. Is this possible?
I have 2 tables and I would like to somehow merge them and have an ID (primary key) automatically assigned while its being merged. Ive tried exporting the tables into MS Access and doing it there but these tables have a HUGE amount of data in them and it basically kills whatever machine its ran on.
Is there a way that you could get the column names for each table in a database using 1 query? something like: tbl colname t1 catID t1 catName t2 prodID t2 prodDesc t3 cartID ... ...
I know it would be long, but I would just be searching through the saved output for specific names.
I have a requirement where one column of a table is supposed to have two foreign key constraints i.e. it can be referring to two different columns in two tables.
