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.
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 have a transaction table having about 40 crore rows in source. It don't have timestamp and unique key columns. It have only Bill_month and Bill_Year columns. Actually for loading this table into staging I have added a new datetime column by adding default bill_date as 01. Then
* First we delete last 3 month data from staging tables.
* Get last 3 months data from source table.
* Load that 3 months data from source to staging table.Â
We do this because we only get update for last three months data. Now I have to include this transaction table as Fact table in DW. What will be the best practice for loading the fact table by picking data form staging table. Also we have to look up with dimensions for Foreign Keys.Â
* Should I implement the same method of deleting last 3 months records and loading them again.Â
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
I have a Fact Table with a ID column as Primary key and clustered index is created. And also I have 4 dimensions FK's of data type INTEGER. And finally, I have one aggregation measure in the Fact Table.
Now, my situation is How can I improve the speed of querying the fact table by creating any of the below indexes?
1. XML
2. Spatial
3. Clustered
4. Non-Clustered
I am using a WriteBack Partition to receive data from various inputs and appends any new data that I add to the WB partition.Â
I am able to read the data immediately in the WB partition through a Fact partition query. This is working at this point as desired.
Eventually I want to move the data from the WB partition into Fact Partition. How can I do this, manually and through automation.Â
How do I correctly populate a fact table with the surrogate key from the dimension table?
View 4 Replies View Related
Hello,
I have developed some packages to load data into "Fact" tables in the data warehouse.
Some packages are OK, other ones not. What is the problem?: some packages load fact tables with lots of "Lookup - Data Flow Transformation" into the "data flow task" (lookup against dimension tables) but they are very very slow, too much slow to be choosen as a solution.
Do you have any other solutions to avoid using "Lookup - Data Flow Transformation"? Any other solution (SSIS, TSQL and so on....) is welcome to speed up the Fact table loading process.
Thank in advance
Hi,
I use lookups to map surrogate of level 1 dimensions to my fact tables in SSIS.
But how to handle a level 2 dimension with a ValidFrom and a ValidUntil date field?
I do not use an IsCurrent column, because this could problem with late arriving facts.
- In dts I used an SQL statement like this:
update SA
SET SA.DimProdRef = Dim.RecordID
FROM SAWarenEingang SA, DimProd Dim
where SA.ProduktNumber = Dim.ProduktNumber
and SA.ArtikelkontoBewegungsdatum between Dim.ValidFrom and Dim.ValidUntil
Now in SSIS I want to handle the whole thing in the data flow without using a staging table:
- Using Lookups: I would have to pass the date column for each inside the fact table into the lookup. That does not work.
- Using Execute SQL in the data flow: would be very slow, because the statement will be executed for any line in the dataflow
Any ideas?
Best regards,
Stefoon
I'm working on a web app that needs to be able to take a row in the database and duplicate it, creating a new row in the same table with the same data except for the ID field and reference field.So basically: table1.row1 references table2.row1. I need to duplicate the data in table1.row1 (creating table1.row2) with the same reference to table2.row1.Is there any easy way to do this in SQL? I'm just looking for some ideas or a framework to accomplish this.
View 1 Replies View RelatedWe need to move duplicate data from this sheet to other table also having issue that sometime verifiedmemberID is null as well as verifiermember name is null and also having the values in BCP authorisationcode as well as FPoveridecode but transactionmode/bcpmode is 'n' and also having condition that transactionmode/bcpmode is 'y' but bcpauthorisationcode is blank.
MemberStatecodeMemberDistrictCodeURNCompanyCodeHeadMemberIDHeadMemberNamePatientID
PatientNamePatientGenderPatientAgeTerminalIDHospitalCodeRegistrationNoBlockingUserDateUnblocking
InvoiceNoDischargeInvoiceNoDischargeDescDischargeUserDateAmoutClaimedTransactionMode/BCPMode
UnspecifiedAuthCodeUnspecifiedAuthDateBCPAuthorizationCodeBCPAuthorizationDateFPOverideCode
[code]....
i am having 3000 records in table. now i want take out the duplicate from that table.
for example: i want to find out the duplicates of 'CompanyNames'.
help needed to write query for this operation.
I have an table with duplicate data. I need to delete the duplicate records based on the RequestId.
I want to delete the records based on the RequestIDÂ 1001, Delete the duplicates in 1002, 1003 .
DECLARE @table TABLE ([Employee_SID] [int] NOT NULL,
[CalculatedTotalSalesYear] [numeric](19, 2) NULL,
[CalculatedTotalSalesYearAnnualized] [numeric](19, 2) NULL,
[RequestID] [int] NOT NULL)
[Code] ....
Expected Result
select * from @table
i am having three tables
test
testId  testName  totalQuestion
Questions
QuestionId  fktestId  QuestionName
Now i am trying to insert a duplicate copy test by passing testId..Here is my sp
if not EXISTS(SELECT testName from tblTest WHERE UPPER(@testName) = UPPER(@testName))
BEGIN
INSERT INTO tblTest SELECT @userId, testName,duration,totalQuestion,termsCondition,0,GETUTCDATE(),GETUTCDATE() from tblTest WHERE id=@testId
SET @insertedTestId=@@identity
INSERT INTO tblTestQuestion SELECT @insertedTestId,question,0,GETUTCDATE(),GETUTCDATE() from tblTestQuestion WHERE testId=@testId
END
how to insert in answer table as one question can be multiple answers.
I have the following two tables:
CREATE TABLE [MailBox].[Message](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SenderId] [bigint] NOT NULL,
[Message] [nvarchar](max) NOT NULL,
[SentDate] [datetime] NOT NULL,
CONSTRAINT [PK_MailBox.Message] PRIMARY KEY CLUSTERED
[Code] ....
I'm building a messaging functionality in to my application, I'm able to insert a message into the database and this message then appears inside the other users inbox. The issue I have it when I click on this message to view the conversation I make a call to the following sp as shown here:
@UserId bigint,
@SenderId bigint
AS
BEGIN
SET NOCOUNT ON;
[Code] .....
The problem with this is I'm trying to connect to the user photos table to return their profile picture, but for some reason even though I have specified IsProfilePic I get all the photos returned, instead it should be two photos, one for the @UserId and the other for the @SenderId, its equivalent to me doing this:
Select *
From [User].[User_Photos]
where (UserId = 1 or UserId = 2) and IsProfilePic = 1
And this returns me the correct information.
Hello Everyone:
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
How do I fix this?
TIA
I cannot create a measure that returns results for dates that do not exist in the fact table despite the fact that the components included in the measure contain valid results for these same dates.Creature a measure that counts the number of days where the "stock qty" is below the "avg monthly sales qty for the last 12 months" (rolling measure).Here is the DAX code I have tried for the measure (note that filter explicitly refers to the date table (called Calendar) and not the fact table):
StkOutCnt:=CALCULATE (
COUNTROWS ( VALUES ( Calendar[DateKey] ) ),
FILTER (
Calendar,
[Stock qty] < [Avg Monthly Sales Qty L12M@SKU]
)
)
Below you can see the sub measures (circled in red) are giving results for all days in the calendar.Highlighted in yellow are dates for which the StkOutCnt measure is not returning a result. Having investigated these blank dates, I am pretty confident that they are dates for which there are no transactions in the fact table (weekends, public holidays etc...).why I am getting an "inner join" with my fact table dates despite the fact that this is not requested anywhere in the dax code and that the two sub measures are behaving normally?
I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above). When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer.Â
Is there a way to build hierarchies within my Customer dimension based on the same Customer table? What I want is to view the data in Excel and see the Customer dimension. Within Customer, I want four hierarchies.Â
We need to Insert/Update a Fact Table from staging Table. currently we are using a SP which update Fact Table for Each region. this process is schedule, every 5 min job is run and Update fact table.but time of Insert and Update too long from staging to Fact, currently we are using merge statement for Insert and update.in my sp we are looping number how many region we need to update and at a time single Region we are updating using while loop in current SP.
View 7 Replies View Related
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them.
each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field.
Ideas?
Thanks,
Ad.
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,
we have a problem with "one-to-many relations between fact table and dimension table". Take the example of table "LOGGEDFLAW" which is related one-to-many to the table "LOGGEDREASON. "LOGGEDFLAW" includes the column "FLAWKEY" and "LOGGEDREASON" includes the column "REASONKEY" and essentiallay the column "FLAWKEY" as foreign key. Now assume that we have the following records in there:
LOGGEDFLAW
1) FLAW1
2) FLAW2
LOGGEDREASON
1) REASON1,FLAW1
2) REASON2,FLAW1
3) REASON3,FLAW2
Now assume, that "LOGGEDFLAW" is the facttable and "FLAWCOUNT" is the measure with the source column "FLAWKEY" in which we want to count the number of FLAWs. As you see in the example the number of FLAWs is 1 for "FLAW1" and "FLAW2". Microsoft Analysis Server generates the value of 2 for the number of FLAWs "FLAW1" because of the one-to-many relationship to the table "LOGGEDREASON". In the attached ZIP File you find :
- a MDB File with the described example
- a screenshot from the cube constructed in AS
- a screenshot from the result table generated with AS.
The question: How is it possible to calculate the measure "FLAWCOUNT" correctly, ignoring the records generated by the one-to-many relationship?
Best regards,
Thorsten
I have picked an exmple from this forum, to help me explain my current problem...
"I'm looking for a solution to import data from a flat file into an normalized data modell. To explain it a little simpler think about to following:
The Data Souce is a CSV-File with FirstName, LastName and Category. Sample data could be
Dirk; Bauer; sailing
Peter; Bauer; fishing
Marc; Bauer; reading
In my data modell I have defined the 2 tables "Person" and "Category":
Table "Person"
----------------
[PersonID] [int] IDENTITY(1,1) NOT NULL
[CategoryID] [int] NOT NULL
[FirstName] [nvarchar](50)
[LastName] [nvarchar](50)
Table "Category"
----------------
[CategoryID] [int] IDENTITY(1,1) NOT NULL
[CategoryName] [nvarchar](50)
Now I like to read my first row from the source and lookup a value for the CategoryID "sailing". As my data tables are empty right now, the lookup is not able to read a value for "sailing". Now I like to insert a new row in the table "Category" for the value "sailing" and receive the new "CategoryID" to insert my values in the table "Person" INCLUDING the new "CategoryID".
I think this is a normal way of reading data from a source and performing some lookups. In my "real world" scenario I have to lookup about 20 foreign keys before I'm able to insert the row read from the flat file source.
I really can't belief that this is a "special" case and I also can't belief that there is no easy and simple way to solve this with SSIS. Ok, the solution from Thomas is working but it is a very complex solution for this small problem. So, any help would be appreciated...
Thanks,
Dirk"
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=74752&SiteID=1
Could someone help me creating the dimension table?
Thanks!!
I have a large flat file that comes to me. I first import the flat data in to a SQL table for ease of use. Then i put it into a more permanent table with the proper references to dimension tables. I want to build a dimension table out of information from my flat file. I have a dimension table with columns, [Org Client], and [Client#] where [org client] is the name of the client. Both of these columns appear in my flat file but i want to use only the client# in my permanent table. How extract distinct values of client # and [org client] into a dimension table?
My idea was to select distinct values of client# and use some type of foreach loop to go through each client# and use a query to select the TOP(1) values of [org client] where client# = x. Would this work and if so how do I go about setting this up?
I'm really hoping there is a simpler way than this. Thank you all for your time.
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
Hi,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
hello,
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 ..