SQL Timestamp Incremental Load Issue
An incremental load is implemented based on timestamp, using a view to update a fact table. • The fact table contains multiple KPIs coming from different source tables. • Two main KPI tables are included directly in the view and drive the incremental logic (their timestamps change). • Additional KPIs come from other source tables, which are LEFT JOINed to the view. During an incremental run: • Main source tables are updated → timestamp changes • Other source tables are NOT updated → timestamp unchanged • Because of the LEFT JOIN, missing values from these tables are returned as NULL • These NULLs overwrite existing values in the fact table Example Fact table (before load) id app_number score 1 333 5 Source tables • source1, source2 → timestamps updated (drive incremental load) • source3 → timestamp unchanged Stored procedure logic MERGE fact_table tgt USING ( SELECT app_number, ISNULL(score, 0) AS score FROM vw_main_kpis v LEFT JOIN source3 s3 ON v.app_number = s3.app_number ) src ON tgt.app_number = src.app_number WHEN MATCHED THEN UPDATE SET tgt.score = src.score; Result (incorrect) id app_number score 1 333 0 Existing data is lost.
Pet project on SQL Server 2022 platform
Hello, world! I would like to share my pet project on SQL Server 2022 platform. I have created a DWH solution that includes many MS's best practices and interesting features such us: ETL process with data cleansing and MDM that easy expand Documentation CI/CD Functional ETL test Ready analytical templates Time intelligence New & returning customers Cluster customers based on spending volume Product ABC classification Basket analysis Events in progress https://dev.azure.com/zinykov/NorthwindBI Unfortunately in SQL Server 2025 will be no DQS & MDS...
SQL Query
Hi All, I have 2 tables ConfigurationTable and Data table. I require combine value like below output Source tables: Target Output: Sql Scripts: CREATE TABLE CONFIGTABLE(Productcode VARCHAR(10), Linkedvalue VARCHAR(10)) INSERT INTO CONFIGTABLE VALUES ('A', 'PEN') ,('C', 'PENCIL') ,('B', 'BOOK') ,('M', 'MOUSE') CREATE TABLE DATATABLE (FIELDVALUE VARCHAR(50), ORDERID INT,NAME VARCHAR(20)) INSERT INTO DATATABLE VALUES ('321', 9, 'COMPUTER') ,('THIS PEN IS', 1, 'A') ,('Country', 5, 'BOOK') ,('Great village in a', 4, 'B') ,('MINE', 2, 'PEN')
How can I optimize this query for better performance
Hi , I have this query and it is taking a long time. If there are more than 10k rows, it takes more than 5 minutes. Is there another way to speed up the process? SELECT ROW_NUMBER() OVER (ORDER BY CreationDate DESC) AS RowId, Id [Id], transactionsId [TransactionsId], amount [Amount], AccountId [AccountId], dbo.Account_FirstBalance(CreationDate, AccountId, 161, CompanyId) [FirstBalance] FROM p_Ledger WHERE CreationDate >= '2024-11-01' AND CreationDate <= '2025-02-11' AND CompanyId = 117 AND branchId = 161 ALTER FUNCTION [dbo].[Account_FirstBalance]( @TransactionsDate DATETIME, @AccountId BIGINT, @BranchId INT, @CompanyId BIGINT ) RETURNS FLOAT AS BEGIN DECLARE @credit FLOAT; SELECT @credit = SUM(CASE WHEN T.transactionStatusId = 1 THEN T.amount ELSE -T.amount END) FROM dbo.Transactions T WHERE T.Approval = 1 AND T.CompanyId = @CompanyId AND T.AccountsId = @AccountId AND T.IsDeleted = 0 AND T.transactionsDate < @TransactionsDate AND (@BranchId = 0 OR T.branchId = @BranchId); RETURN ROUND(COALESCE(@credit, 0), 2); END;
unable to connect analysis service database
hi .. i am connected to analysis service database like this after connecting i create new empty database .. then i restore adventure.abf database into adve empty database by click right ---- restore database by clicking ok i got an error so after searching i grant permission .. and create new role DBAdmin and give all rights still no success.. how to resolve this issue help needed
