Running at the same time instead of in sequence can sometimes be one of the biggest gains in performance you can make to your job.
Imagine you have 3 data flows of 10 minutes each. If you run them sequentially it will take 30 minutes, but if you run them at the same time it would only take 10 minutes in total.... provided of course that your job server has sufficient resources to run all 3 at the same time.
Now you can either go about working out on your own which data flows can be run sequentially or in parallel, or you can let Data Services work it all out for you.
To get Data Services to do the hard work for you, read my blog post on developing Data Services jobs using components and how the execute only once option helps you out.
Monday, March 31, 2014
Friday, March 28, 2014
Improving Performance - Part 2 - Use the database
One of the clever things that Data Services can do, is it can send processing down to the source database rather than doing it itself on the job server. We call this pushdown SQL, because the query is pushed down to the database.
Why is this a good thing?
Imagine a simple query of 2 tables. 1 table has 1 million rows, this is joined to a second table with 10 rows, and then you put a filter on the 10 row table. So maybe the large table is sales figures, and the small table is cities that you do business in, and you only want sales from 1 city.
Without pushdown SQL Data Services would first have to retrieve all 1 million sales records from the database and that data would have to be moved over the network to the job server. Then the 10 city records would be retrieved and it would have to filter the 10 city records to the city you want, and then would have to join that back to sales to get the 100,000 or so sales records you actually want.
With pushdown SQL, the query will be sent to the database where the database will run the query to retrieve the 100,000 records required and just those will be sent over the network to the job server.
Now lets say you were just going to move those records to another table in the same database, well in that case DS can create an insert query that will get run on the database directly, so no data at all needs to be moved over the network.
So how do you know whether data services is pushing the query down to the database or not? Well in the data flow go to the menu heading Validation and then the sub-menu Display Optimized SQL.
So what are some of the techniques you can use to make sure that as much as possible is being pushed down to the database?
Well first of all, don't create unnecessary datastores. I've often seen people create datastores for different groups of tables that all come from the same database. When you do this DS thinks the tables are in different databases and doesn't realize that it can join them together on the source.
If you are using Oracle, just create one datastore per database, that way you can take advantage of cross schema joins.
Also consider using schemas in SQL Server to logically separate out pieces of your database such as having an stg, and a dm schema instead of placing them in separate databases.
Anything which enables to DS to contain as many of your tables in one datastore as possible, will improve its ability to push down sql to the database.
Within your datastores you can also tell DS that you have linked databases setup, this way DS can take advantage of any linked server you have setup as well. Just go to your datastore, click edit, and the last field under advanced is where you tell it that you have a linked server setup to another datastore.
Also look at how you are building your dataflows. If you have a long complex dataflow that joins lots of stuff together, then does a bunch of processing, and then joins in another large table; consider splitting this out into two dataflows. Send the data back to the database mid-stream and then pick it up again. Often it turns out that having a few smaller dataflows that can be pushed down to the database runs a lot faster than having one large complex dataflow where DS is pulling all the data onto the server.
Now that you've got as much as possible running on the database, you can take full advantage of all those indexes and partitions my interviewees were telling me about in part one!
Why is this a good thing?
Imagine a simple query of 2 tables. 1 table has 1 million rows, this is joined to a second table with 10 rows, and then you put a filter on the 10 row table. So maybe the large table is sales figures, and the small table is cities that you do business in, and you only want sales from 1 city.
Without pushdown SQL Data Services would first have to retrieve all 1 million sales records from the database and that data would have to be moved over the network to the job server. Then the 10 city records would be retrieved and it would have to filter the 10 city records to the city you want, and then would have to join that back to sales to get the 100,000 or so sales records you actually want.
With pushdown SQL, the query will be sent to the database where the database will run the query to retrieve the 100,000 records required and just those will be sent over the network to the job server.
Now lets say you were just going to move those records to another table in the same database, well in that case DS can create an insert query that will get run on the database directly, so no data at all needs to be moved over the network.
So how do you know whether data services is pushing the query down to the database or not? Well in the data flow go to the menu heading Validation and then the sub-menu Display Optimized SQL.
A window will then open showing you the actual SQL that will be run against the source database.
Well first of all, don't create unnecessary datastores. I've often seen people create datastores for different groups of tables that all come from the same database. When you do this DS thinks the tables are in different databases and doesn't realize that it can join them together on the source.
If you are using Oracle, just create one datastore per database, that way you can take advantage of cross schema joins.
Also consider using schemas in SQL Server to logically separate out pieces of your database such as having an stg, and a dm schema instead of placing them in separate databases.
Anything which enables to DS to contain as many of your tables in one datastore as possible, will improve its ability to push down sql to the database.
Within your datastores you can also tell DS that you have linked databases setup, this way DS can take advantage of any linked server you have setup as well. Just go to your datastore, click edit, and the last field under advanced is where you tell it that you have a linked server setup to another datastore.
Also look at how you are building your dataflows. If you have a long complex dataflow that joins lots of stuff together, then does a bunch of processing, and then joins in another large table; consider splitting this out into two dataflows. Send the data back to the database mid-stream and then pick it up again. Often it turns out that having a few smaller dataflows that can be pushed down to the database runs a lot faster than having one large complex dataflow where DS is pulling all the data onto the server.
Now that you've got as much as possible running on the database, you can take full advantage of all those indexes and partitions my interviewees were telling me about in part one!
Thursday, March 27, 2014
Improving Performance - Part 1 - Only process what you need
I've interviewed quite a few people for positions as Data Services consultants and one of my standard questions is -
"Say you have a slow job, what are some of the things you could do with Data Services to improve its performance?"
So many people start off by telling me about indexes and partitions and query optimization, and almost never talk about the way the data flows themselves are designed and the huge impact that can have on performance.
So this series of posts is going to be about how to get the best performance out of your DS jobs using functionality available to you in Data Services, starting with the things you can do that will have the most impact on performance.
I have found that the number one thing you can do to impact your jobs performance is to make sure you are only bringing though the data that you need to process in that load. I have seen so many data flows that reprocess entire tables every time they are run. Often they are this way because when the jobs were first created the source systems were fairly small, so the impact on performance was minimal. But as the source systems have grown over the years DS is having to process more and more data, and the impact on execution time becomes immense.
So how do you make sure you are only processing what you need to process? Well the first thing is to look for an insert and update datestamp on your source system data. Then use this to make sure you are only bringing though the data you need for that load. To do this, keep a record of the max update date of each load, and then only bring through data with a more recent update date the next time you run the job. This is often referred to as doing a delta load.
To give you an example of the impact this can have, just yesterday I reduced a single data flow's run time from 20 minutes down to 4 minutes by adding a check to only bring through data amended or added since the last job execution.
What if there is no update date in the source system? Well in that case you may have to look for alternatives such as keeping track of the highest primary key since last run, but this only works on inserts. Another option is to use CDC (Change Data Capture). There is documentation that comes with Data Services that explains how to use this, but basically, the database keeps a log of all the changes that occurred, and then you use the Map CDC Operation transform to load them into your warehouse. Its not entirely straightforward, so if an update date is available on the table, I always go for that option first.
"Say you have a slow job, what are some of the things you could do with Data Services to improve its performance?"
So many people start off by telling me about indexes and partitions and query optimization, and almost never talk about the way the data flows themselves are designed and the huge impact that can have on performance.
So this series of posts is going to be about how to get the best performance out of your DS jobs using functionality available to you in Data Services, starting with the things you can do that will have the most impact on performance.
I have found that the number one thing you can do to impact your jobs performance is to make sure you are only bringing though the data that you need to process in that load. I have seen so many data flows that reprocess entire tables every time they are run. Often they are this way because when the jobs were first created the source systems were fairly small, so the impact on performance was minimal. But as the source systems have grown over the years DS is having to process more and more data, and the impact on execution time becomes immense.
So how do you make sure you are only processing what you need to process? Well the first thing is to look for an insert and update datestamp on your source system data. Then use this to make sure you are only bringing though the data you need for that load. To do this, keep a record of the max update date of each load, and then only bring through data with a more recent update date the next time you run the job. This is often referred to as doing a delta load.
To give you an example of the impact this can have, just yesterday I reduced a single data flow's run time from 20 minutes down to 4 minutes by adding a check to only bring through data amended or added since the last job execution.
What if there is no update date in the source system? Well in that case you may have to look for alternatives such as keeping track of the highest primary key since last run, but this only works on inserts. Another option is to use CDC (Change Data Capture). There is documentation that comes with Data Services that explains how to use this, but basically, the database keeps a log of all the changes that occurred, and then you use the Map CDC Operation transform to load them into your warehouse. Its not entirely straightforward, so if an update date is available on the table, I always go for that option first.
Wednesday, March 26, 2014
5 Myths about SAP BI
1. You need SAP ERP to use SAP BI
This is one of the most pervasive myths out there and it just isn't true. In the 15 years I have been working with SAP BI, I have only once used SAP's ERP system as a data source. In fact SAP BI can connect with over 160 different data sources from big data solutions like Hadoop, through the traditional databases like Oracle and SQL Server, right down to a basic Excel spreadsheet. In fact on of its greatest strengths is that it can connect to so many different data sources, and offers the ability to integrate those sources without having to do any ETL.
2. It's just a reporting tool
For years Business Objects, which SAP bought in 2006, primary tool was a report writing application with excellent ad-hoc and highly formatted report capabilities, and that tool still exists today in the form of Web Intelligence. But SAP BI is so much more the Web Intelligence. It offers data discovery in the form of Explorer; highly formatted reports built with Crystal; its dashboard builder is world class and its incredibly easy to deploy any and all content to mobile. All of the reporting tools sit on top of a unified and consistent data layer, allowing for the same interface to be used for data access across the entire tool-set. Throw in SAP BI's ETL and Data Quality tool Data Services, and its impact and lineage capabilities in the form of Information Steward, and you have an end-to-end complete BI and Data Management solution.
3. It's too expensive for small to medium sized companies
SAP has offered the Edge version of its BI tools for a number of years now. This provides a very cost effective solution for small to medium companies, while at the same time provides for something that is very scale-able. Some tools, like Lumira, are even free and come with cloud storage and access.
4. It won't integrate with Office or SharePoint
SAP BI comes with Live Office that allows you to share any report table or graph within Outlook, Excel, Word or PowerPoint. You can also create a query directly in Excel against a Universe. A SharePoint integration kit is also available for full integration with SharePoint.
5. It's difficult and expensive to deploy
Pre-built cloud solutions are available making it very easy to get an installation up and running. A standard installation can be a against a single server running a Windows OS. Having said that, a big advantage of SAP BI is that you can also install it in a clustered environment running on your choice of OS from Windows through to your favorite Unix\Linux flavor. And don't forget that Lumira Cloud is ready to go and free, you just need to create an account.
Subscribe to:
Posts (Atom)