I'm trying to get one cube to display two different grains of data:
1) Vehicle measures - such as total time and total miles.
2) Trip measures - such as total trip time and total trip miles.
How would I accomplish this? I assume that the measures have to be in the same fact table?
Thanks for your time!
I have two tables in my data model,
1. Fact table with sales by customer, Territory ID etc
2. Table with targets per Territory ID
I want two combine them in one pivot table.Â
Pivot: Territory;Sales;Target
What I'm getting is the correct column with sales, but the overall sum of all territories in column for targets.Relationship is established between two tables via Territory ID
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 only see a need for a PK in a dimension table, not a fact table. Do you agree?
View 4 Replies View RelatedI would like to know how to use a fact table so that when I insert or update a row with a word that the table will reference the fact table to make sure that the word I'm using is correct.
for example I have a table with column Fulltext and Abbreviation
in the fulltext column I have a a word "Windows Server 2008" now in the abbreviation I would like to abbreviate this to "Win Srv 08" Now the Fact table would have to columns Fulltext and Abbreviation under Full text the full words would be in it like Windows, Server, and 2008 and under the Abbreviation column Win, srv, and 08
So I want it so that everytime the word Windows comes up and I need to type an Abbreviation for it that it will reference the fact table which is using the Abbreviation Win. To avoid different ways of abbreviating the word windows.
Is there a way to do this automatically so that I don't have to manually go back and forth between the fact table and the table that I'm updating?
Hi there
Is there functionality in SQL 2005 to update only new records, e.g. only records from yesterday. I've seen functionality for dimension tables to only get new records but nothing for fact tables.
Thanks
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
Hi,
I am writing a BI solution for a recruitment company. In their
business, the can be n number of participants from different
dimensions linked to the same fact record. For example, a client can
be sent the CV of 50 candidates. That's my first problem. My second
problem is the variety of dimension participant types for a given fact
record. This results in the need for nullable dimension FK's - which
I'm trying to avoid. For example, consider the following two business
events. In the first one, a candidate fills a job. Easy, we have a
record in the fact table where the fact table has the following
columns: DateKey, EventType, CandidateKey, VacancyKey. No nullable
columns, great. But there are other events that I want to store in
the fact table too. Let's go back to my first example: The client is
sent CV's of 50 candidates in one transaction. So there is one client
linked to the fact, but 50 candidates. So now I need to extend the
fact table and add another column: CandidateGroupKey (which links to
and Intermediate Fact Table). But in this case there was no vacancy
involved. So do I now have to make the VacancyKey column nullable?
That doesn't seem like a good idea...
Or do I have to go for a completely different approach and have
different fact tables instead of just one?
Anyone have any suggestions?
Hi!
can any body tell me how to populate the fact table from base tables.
databse name: bookorder
Realtions:
customer (customerid,l_name,f_name,city, district,country)
cust_order(orderid,orderdate,customerid)
order_detail(orderid,itemno,isbn,quantity)
book(isbn,title,edition_no,price)
author(authorid,name,surname)
book_author(authorid,isbn,authorseqno)
Now populate the datawarehouse name: book_orderdw
having fact table & three dimension tables given below from the above bookorder database
fact table
factsales(customerid,timeid,isbn,unit_price,discount_price,sales_quantity,sales_amount)
dimension tables
dimcustomer(customerid,l_name,f_name,city,district,country)
dimtime(timeid,orderdate,dayofweek, month, year)
dimbook(isbn,title,edition_no,price)
Now I have to populate the fact & dimension tables by writing sql scripts.
Now I have already populated the dimension tables by writing sql script,
But I have to populate the fact table taking into account, here I am facing problem in wriring sql script
(i) unit_price is taken from the book base table with reference to the isbn
(ii) sales_quantity is taken from the order_detail.quantity with reference to table cust_order(via orderid & orderdate)
(iii) discount_price is determined dependent on the quantity. if the quantity > 20 then discount 20 %(i.e discount_price = 0.8 * unit_price). if quantity < 10, no discount i.e normal price. if quantity between 10 and 20, discount 10%. Note that the quantity is determined based on each order of each customer, thus if the same book appears at multiple positions in an order, those positions shall be grouped together. This could happen because the pk of the order_detail table is order_id + item no, not order_id + isbn
(iv) sales_amount is sales_quantity * discount_price
A few questions:
1) We have numerous fact tables with surrogate keys which reference just one dimensional surrogate key. How does this work?
2) Are the ‘facts’ feeding data TO the ‘dimensions’ (back end warehousing)? Or are the ‘Dimensions’ feeding facts to the ‘facts’ tables for lookups!?
Nb: Im very inexperienced at database design.
Im really also using this thread to get contacts for future harder questions!
Thanks kindly
I have an existing table that i would like to add a uniquidentifier toeach record of the table. I have already create a column for theuniqueid. What sql script could I run to actually place a value forthe newly created column for each record?thanks for your help ahead of time
View 2 Replies View RelatedHi,this is easy with OLAP tools, but I need to do it just with MS-SQLserver:fatTableyeartypeval97a197b297c398a498b598c6....yeartype_atype_btype_c971239845699...The problem is number of different types - not just 3 like a,b,c butmore than 100, so I don't want to do it manually likeselectyear, a.val, b.val, c.valfrom(select year, val from factTable where type='a') afull join (select year, val from factTable where type='b') bon a.year = b.yearfull join (select year, val from factTable where type='c') con a.year = c.yearis it possible somehow with DTS or otherwise? I just need to presentthe data in spreadsheet in more readable form, but I cannot find anyway how to export the result from MS-SQLserverOLAPservices to Excel...Martin
View 2 Replies View RelatedHi, everyone,
l've a fact table DEVICE with following structure,
DEVICE_NAME VARCHAR(50)
DEVICE_DATE DATETIME
DEVICE_NUMBER INT
Where DEVICE_NAME and DEVICE_DATE form a PRIMARY KEY
So l would like to import a text file with same information into this table.
My problem is, text file contains records which will violate my primary key constraint. In that case, l would only insert the record with DEVICE_NUMER not equal to ZERO and discard and log the others.
In case of the records violtae primary key constraints have DEVICE_NUMBER not equal to ZERO, discard both and log it.
So anyone has good suggestion on this?
Hi
I have delta loaded all the dimension tables now and each dimension table is related to fact table through a surrogate key, How do i further load a fact table. Please tell me I am stuck up here.. :( .
If any one has an example to refer please do tell me
Hi,
I am relatively new to SSIS/SSAS. I have searched the forums but cannot find an answer to my question.
I created a cube in SSAS and have deployed it. Now I am trying to use SSIS to populate the cube. I have setup a DS that points to the SSAS instance - it uses OLEDB Provider for Analysis services 9.0.
When I try to use a data flow task OLE DB source to truncate the dimension/cubes I do not see the DS in the list to select?
I am finding it hard to get into the SSIS way of organizing the processing.
Any Ideas?
Hi All,
I have to load my fact table using data from my stage table and joining it against dimesnsions, most of my dimesions are straight joins that I can implement using Lookups , howevere in one join I am using something like this
SELECT T.Dim_Time_ID ,
SUM (Measure)
FROM [dbo].[Stage_Table] S
INNER JOIN Dim_Time T
ON SUBSTRING(T.MonthYear,1,3) = SUBSTRING(S.Time,1,3)
AND SUBSTRING(T.MonthYear,6,2) = SUBSTRING(S.Time,6,2)
Currently I am using a copy column transformation to make a copy of this column, and then using substring function in my SQL code and the Lookup Transformation joining the data to get the desired output, was wondering if there is any other (better) way of accomplishing this inside the data flow.
Thanks
2 questions, actually:
I am new to database design and a lot of things never made any sense to me regarding relationships and such. I have been working on a very large design that started out well enough, but as tables were added a lot of organization fell by the wayside. Now that I am getting closer to the end, I am finding a lot of places where there should be Foreign keys, maybe some triggers, etc (I have the same data item in 5 different places, when it is deleted in one place it must go from all). Assuming that the datatypes and sizes are identical for the duplicated bits of data, can I go about making FK-PK relationships and such now that there is a lot of stuff in the database, or do I have to start from scratch and rebuild the whole thing.
The other question is much more simple:
How do I make multiple rows "unique". I have a primary key, and an identity column, but I can't add a secong primary key, and Enterprise Manager only lets me make 'int' datatypes identity columns. I have tried the "add constraints" but it asks for an expression and I have no idea what the syntax might be.
Any help is appreciated.
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....
View 3 Replies View Relatedhello,
Iam trying to build OLAP cubes in MS SQL Server 2000.But all the tutorials/docs mention about fact tables & dimensions.
Can I get some good tutorials on how to create fact tables to build OLAP cube ?
Also, which OLEDB provider to be used for MS SQL Server while creating OLAP Datasource ?
Thanks in advance & wishing u a prosperous new year too.
i face a problem to create a new cube with the fact table without numeric field as a measure. can i use others data type of field as a measures?
anyone can provide me a solutions to solve it? thank you ..
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!!!
Hi
I have two tables, the first table contains the actual time value, and the second table contains the time master. The time master is a dimension table and it has the records of all the date with 1 hour time interval. In other words, the for a given day, there would be 24 rows with one hour time interval. I want to update the timeid in table1 with the corresponding timeid in table2
how to write an update statement to acheive this. i have given the sample two tables.
Regards
Jtamil
Table 1
RID LogTime TypeTimeID
12006-10-14 11:13:42.297Success6876
22006-10-14 11:14:00.530Error6876
32006-10-14 11:14:00.530Success6876
42006-10-14 11:14:23.670Success6876
52006-10-14 11:14:29.467Error6876
62006-10-14 11:14:50.420Error6876
72006-10-14 11:14:50.480Success6876
82006-10-14 11:25:39.950Success6876
92006-10-14 11:25:42.560Error6876
102006-10-14 11:34:58.357Error6876
Table 2
TimeID the_datethe_day
68752005-10-14 11:00:00.000Friday
68762005-10-14 12:00:00.000Friday
I have heard from my client that they are facing duplicate data issue on one of the fact table.
Basically there is a view built on fact table and client access the data through the views.
There warehouse is loaded daily through SSIS packages. The duplicate records issue is only when the views are queried during the data loading process. The duplicates are gone when the data load is completed.
I have a cube with 2 fact tables and I would like to create a calculated measure that uses values from both tables like:
fact1.measure1 / fact2.measure2
I can't see how to do this?
I have a fact table that has terminations. Fields include EmployeeName, TermDate, TermReason, and HireDate, et al.
I need to make EmployeeName available to drillthrough, and since it's a varchar field I can't make it a measure, so it has to be a dimension attribute. My question is, should I leave the fact table as it is and use SSAS to create a dimension that contains only EmployeeName and the link to TerminationID? Or should I redesign the OLAP tables so that EmployeeName is in a separate table?
Dear all,
Now I create datawarehouse for my client, I have SSIS a lot for ETL process, I a problem that some fact table need to be updatetable and there is a lot of data of this, I need some efficent way to load this data to data warehouse.
I have read your article about SCD in SSIS (Slowly Changing Dimensions in SQL Server 2005).
I think the purpose of SCD for Dimension table. If I have some fact table that need rows to be updatetable can you give me an example, best practice, the efficient way or fastet way to load fact table that can be updatetable?
If you have link or link about this problem please reply my email. Thanks
My datasource from ORACLE and my datawarehouse in mssql2005
Regards,
Hendrik Gunawan
Hi,
Please help me out in loading the fact tables
I had used lookup on DIM table to get my SUK and if I use union transformation to get the out put from each lookup and then loading the data with some condition the data in my fact is not loading in a proper format.
The union transformation is splitting the out put in to different records
Please do inform me about which transformation should be used to get the data from lookup tables.
Or please do inform me the approach to load the fact table in SSIS.
I€™m basically INFORMATICA resource and I€™m implementing in terms of INFORMATICA
Hi,
I have 2 fact tables and 2 dimensions (one of them is time).
fact1 ---- pk-fk ------ fact1-details-table
(date) |
| Dimension.
|
TimeDim
Is there a way to define measure group on fact1-details-table using TimeDim. Date info is only in fact1 table and not in details table.
Is namedquery joining the 2 fact tables the best solution in this case? There is so much redundancy using one fact table, so underlying sqldb uses 2 tables.
Thanks,
Chandra.
Hi
I am strugglinh since last 2 days .SSIS is giving me torrid time
I am getting error while loadding the fact table
[Destination Fact Table [1099]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[Destination Fact Table [1099]] Error: The "input "OLE DB Destination Input" (1112)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (1112)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The ProcessInput method on component "Destination Fact Table" (1099) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Please help
I have already googled for this error and appied whatever tips were given.
Hi All,
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?
thanks
Greetings all,
For entertainment purposes, I've been reading some articles on SQL Injection Attacks and there should be a cover charge to read these articles. (excuse the sarcasm)
Most defense is based on the use of stored procedures or read only settings on the tables.
I'm looking for practical opinions and possibly some code that would convince me personally that this is indeed a real threat.
Also, if possible, please post remedies that are solid and not open ended.
Thanks all,
Adamus
Is it correct to say that for each cube you can have only one Fact Table? I am having a funny dispute just now.Â
According my experience I never built cubes with more than one fact table, if I want take data from more than one table I write a view and I use it as Fact table...but a cube with two or three fact tables? Never tried..