New answers tagged

5

Azure SQL Data Warehouse has limited support for UDFs. It does not yet support the syntax SELECT @var =. Instead you must use DECLARE @var int = or SET @var =. SQL DW UDFs also do not yet support queries on user tables. Please use our feedback page to vote for new features.


4

Functions in Azure DW don't support select statements that access tables like in your use case, see CREATE FUNCTION (SQL Data Warehouse): function_body Specifies that a series of Transact-SQL statements, which do not reference database data (tables or views), define the value of the function. Could you double check that function is created in DW? ...


1

Transportation is just moving data from one place to another - in ETL, from source system to either staging area, data warehouse or data mart. Transformation is changing data structure so that it meets data warehouse needs - i.e. star schema, denormalization, aggregation, calculations, data cleansing, data enrichment etc. Both happen while ETL, but the ...


3

You can use ORA_ROWSCN pseudocolumn to find rows that have been changed since last update. By default it tracks changes by database block, not by row, but it can be used to limit the amount of data transferred between databases. I would suggest a following procedure: Find the current Oracle SCN (System Change Number) select ...


1

You might be able to get a performance improvement by using OPENQUERY from the SQL Server side to the Oracle database set up as a linked server. Then you could use the SQL Server MERGE function to merge in any new rows from Oracle to SQL Server, that is, any rows that do not match, column for column, with what you already have in SQL Server. Something like ...


2

You have delivered a solution that uses SSIS. SSIS requires an installation of SQL Server Standard, BI, or Enterprise Edition (depending on components used). At this point, your client will need to spend ~ 8k per core (SE) to 23k per core (EE) for a license of SQL Server for their desktop. That's probably not what they're expecting. What can you do? ...


0

Usually surrogate keys are implemented based on dimension tables and external data sources in order to have coherent and valid data records; the ETL process, which in charge of extracting data from different sources, transforming and finally delivering data into the data warehouse, must ensure this condition (i.e., surrogate keys refer to the right record). ...


0

you can split a large text to smaller chunks, in the rows and load them in foreach loop container, Declare @maxRowLength int = 50; with x as ( select 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry''s standard dummy text ever since the 1500s. Over the years, sometimes by accident, sometimes on ...


1

I highly recommend the 1st option of a bridge table. If you make sure the you capture the historical change on all three tables that would work fine, though I'd agree with you that the JOIN'ing is an issue. Just make sure that (in the JOIN clause) that the DateInserted and ValidTill match the ones that are in the fact table. More about bridge tables in here ...


1

If you want to capture historical changes on the three tables you need change hierarchies tables (i.e. [Area], [Region]) into sub-dimensions of the dimension table (i.e. [Location]) by adding an effective_date and expire_date (in the three tables )and you can track changes in dimension and sub-dimension attributes in order to report historical data using ...


4

Based on your sample data it looks like there are 6 columns in total. COL1, COL2, COL4, COL5, COL6 = All fixed width . COL3 = This is a fixed width for a given day, but it is variable day to day (based on my understanding). You should be able to calculate COL3's width each day based on the length of any given line. You know COL1,2,4,5,6 are all fixed ...



Top 50 recent answers are included