what etl tool are folks moving toward?
- Steve Jones - SSC Editor wrote:
Express is free, but no SSIS/SSRS with this. I don't think there are free replacements for them
I might aim for a VM in the cloud I can shut down. Storage is rarely expensive.
it does have SSRS (with limitations) - https://learn.microsoft.com/en-us/sql/reporting-services/reporting-services-features-supported-by-the-editions-of-sql-server-2016?view=sql-server-ver16
no SSIS though - but that is better off being replaced with something else anyway.
-
i just remembered phil, if i remember correctly, the concept of directories isnt available under workspaces. at least not with pro licenses and maybe no deeper than one level with higher than pro. that would also be a deterrent to moving our paginated reports to pbi. we often go 2 or more levels deep in ssrs.
-
They're called folders and what you suggest is now possible, even with just Pro, as far as I can see:
-
thx phil, can they go deeper than 1 level? i mean the folders.
- stan wrote:
thx phil, can they go deeper than 1 level? i mean the folders.
I just set up five levels, and there's no end in sight, so that's a resounding 'yes'.
-
I'm not moving towards any "ETL Tool". We've been doing major imports of millions of rows of data using BULK INSERT and we do exports using BCP. The exception to either of those is spreadsheets and we use the ACE drivers there. Even with WebMethods, we call stored procedures that use BULK INSERT. They work fine, fail safe, and drain to the bilge and we've been doing it the same way for better than 14 years now.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Bump to overcome the new-page post missing bug with this site.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thx Jeff. You must be supporting a lot of code. I’m picturing bulk insert from a traffic cop sp on our server where I believe enough drivers and linked servers exist to do the job. And a table driven tier type algorithm ingesting dimensions and facts in a logical way. Probably a big investment at first but I think I can see it.
-
I am currently doing dual development in SSIS for our data warehouse on SQL Server running in an Azure VM (not Azure SQL Server) and MS Fabric. Our production system is the SQL Server data warehouse and Fabric is a test bed for the newer technologies. I've been learning Power BI, Fabric Admin., Data Engineering and Analysis with data flows, data wrangler, pipelines and notebooks, oh my!
@Phil, the third time is the charm. I felt that ADS was, I don't know, kind of clunky? Microsoft has made good progress over the last year. I've seen it grow from a group of tools into the integrated set available now. I find myself leaning towards Fabric ETL/ELT tools instead of SSIS for the flexibility, ease of use and deployment. (SSIS has been my 'go-to' for a very long time, since it was called DTS, it is what I'd think of as my comfort zone).
-
thx jkunkel, do you mean adf? the concern there is that its a big data tool. if you want ssis you call it from there but why bother. and im guessing its very expensive, probablly requiring licensing higher than pro and all that cu stuff that ive never gotten my head around.
-
Yes. Azure Data Factory, is what I meant. Thank you for the clarification. I have not used Azure Data Factory in production for the exact reason you mentioned. But, I have 'experimented' through several MS Learn topics with ADF to come to that opinion. Perhaps it could have been a personal block. We were also informed it was geared more for big data and costly as well.
However, in Fabric it doesn't feel like you have to navigate through different tools to work through data sizing concerns. I feel that Fabric shouldn't be categorized as a big-data-only toolset, just an opinion. As far as cost, it is scalable to what you need.
For our use, a Fabric environment to mirror development efforts, (keep in mind I'm at a small company) is great to experiment with and to learn about the new platform. For us, it has been an easy to use, low-code platform, even at the itty-bitty F2 capacity. Best part, is that if we decide to use Fabric over SQL Server for warehouse data, it takes but a few clicks to scale up Fabric.
We are also including our Analysts, who end up using the data models in the end, as part of the development team. They are used to using Excel, Power Query and Power BI to put together data for their reports. So, Fabric almost comes naturally to them for developing Data Flows as well as the Power BI experience.
-
thx jkunkel. to be clear, i believe adf is a subset of fabric and that is probably what you intended to convey.
either way let me ask a question that nobody anywhere ever offers an answer on. we ingest about 3.3 million facts per day into our > 50 million row corp warehouse. it takes about 2 hrs but sometimes we rerun small portions. there are currently about 13 erps feeding the job. some erps are sql , some db2, some netsuite, some sap, some oracle etc etc. as we buy other companies, more time zones enter the picture so we may have to run the same job once or twice more at different times of the day.
so with what i presume would be a necessary license higher than pro (we only have pro for every employee in the company) , and the ability to create datasets in fabric because we dont want to call ssis and adf doesnt know how to plumb to lower volume dbms's (i think), and what i presume would be some level of cu whose horsepower allows those 2 hrs of cpu and more, what do you think it would cost us (maybe per mo) to start using adf and datasets etc etc to replace our current use of ssis just for that one job?
-
Thanks Stan. I agree, that is what I was trying convey.
I'll give it a shot! Just a heads-up—I'm still fairly new to Fabric and learning more every day.
Performance can vary depending on your storage setup, the time zones of your data sources, and how many Capacity Units (CUs) you're consuming in your Fabric subscription. Here's a link to Microsoft’s current Fabric pricing page for the different CU tiers in the U.S. Microsoft Fabric - Pricing | Microsoft Azure
With that, I'll try to layout an general idea of what we are doing. We have the F2 capacity. 2 CUs. The smallest one, and we purchased the Reserved subscription to get the discount pricing (annual subscription, if I remember correctly). Our monthly costs for our Fabric subscription are around $200/month.
We're currently running nightly data integration jobs that pull from SAP HANA, SQL Server (both on-prem and Azure), Oracle databases, and a few text files. Our main fact table—over 5 million rows—is refreshed nightly in both our Fabric Lakehouse and SQL Server data warehouse.
Across both environments, we ingest about 9-10 million rows each night. In SQL Server, we use SSIS to truncate and reload recent partitions (typically the last 3–4 months, around 600K–700K rows) from SAP HANA. This process takes about 20 seconds within a virtualized SQL Server on an Azure VM.
In Fabric, the job to load the full 5M-row fact table from SAP HANA and use delta-parquet files for efficient updates takes just under 5 minutes on F2 capacity. We also run cleanup jobs in Fabric every other night, and I’m currently exploring incremental loading with Dataflows. My initial workover from POC to Fabric Lakehouse was rough, and will get some 'best practices' attention soon.
With that in mind, I have not had any overages in the F2 capacity, not yet.
You mentioned all users have 'pro'. Can I assume that is Power BI Pro, or is that a reference to a 'pro' version of a different tool? We have very few people licensed for development use of Power BI (Pro & higher) and with either Fabric or Office 365 (I'm not certain which, someone else can probably clarify which) most users can access the content without Power BI license. Most of our end users <u>want </u>to see Power BI reports & dashboards versus develop them. Then they usually want to be able to export into an Excel file for further "personalized " analysis, Power BI on Fabric allows this.
If you haven't yet, give the Fabric Trial a go. It think it is good for 30 or 60 days. I would go into it with a very specific proof of concept in mind. In my opinion, it feels easier to work with than SSDT. Then again, I have run into pitfalls and brick walls trying to do things from SSIS not available in Fabric's tools (recursion being one of them to flatten a hierarchy). I have found some workarounds with Spark to move past some of those hurdles. I suppose Fabric has an environment that allows a variety of tools for ETL processes, that feel like they are integrated into one.
Apologies for the very long reply, I hope it is useful. Cheers!
-
thx jkunkel. so $200 per month is about what you would expect. i think you are saying for a low cu which would include the ability to do datasets and other good things (eg adf) in fabric that we cant do with just a pro license in pbi. i think but am not sure after looking that there is no notion of licenses in this space, which is what you said. im going to read the links you provided. and reread your post. and ask our tenant guy if he can dumb it down for me. just the complexity of this marketing nightmare makes me want to go to something else but i think we need to understand it. our ms negotiator might not go for dev practicing but with our new BI leader i might be able to make a case.
-
im sitting with ms tomorrow to estimate what our cost would be in just ms charges if we were to migrate our ssis to adf presumably using smaller data objects like datasets.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply