November 2010 - Posts
This is a live post from the session of Eran Sagi at Tech Ed Israel 2010 .
SSIS 11 CTP1 still runs on Visual Studio 2008
An easy and quick zoom on the flow
Custom toolbox:
A special toolbox for SSIS
Grouping by functionality (like before) and by favorites, common
Information on each component and a small help icon which will be online and specific for that component with videos and How To for that component.
A new data source (and destination) wizard to assist you in building the data source more easily.
Each task takes its own Meta Data. We're no longer dependant in one component by the ones that came before it. We don't need to reconfigure it if we deleted something before it.
In case of a problem in the Meta Data, we can use mapped columns, whether with auto match or by choosing on our own.
An ability to group components to helps you understand more easily the package visually. (Does not affect the way the components run).
Undo support and redo support
In SSIS 11 CTP1 we have a project as a container for a few packages which we can move parameters between them. We can now have better and easier administration of our packages.
Parameters getting their value from outside and then become unchangeable inside the running of the package. Server name is a string batch number is int32. We no longer necessarily need a configuration file which we should change from the environment of the development to that of the production.
Variables can change during run time. Parameters cannot. A variable can take the value in the parameter (server name) and we can use it in the variable to add something to it (a certain folder in that server). That way we can use the data in a parameter in an expression.
We can define a parameter in the scope of the whole project and not just for the specific package.
The SSIS server - improving the deployment and management for the SSIS packages.
No more need for dependency on the login name and password of the package administrator or the SQL Server Agent. Administration is now through the server.
We can migrate from SSIS older version to newer version and we can also decide if we want to downgrade.
Environments - sets of values that can be supplied to the packages.
Entry point package - a few packages in one project can be the entry point in that project. We have an easy way to identify them and give them the parameters.
This is a live post from the session of Guy Glantser at Tech Ed Israel 2010 .
Top - Down:
Performance Monitor:
%Processor Time
% Disk Time
Page faults \ sec
Buffer cache hit ratio
Full scans \ sec
SQL complications \ sec
Resource Governor:
Managing SQL server workload and system resource consumption
In essence, we check how much each query, application etc. require from our different resources. We can check by groups, resources and so on.
We can then decide how to limit the use of the resource.
Dynamic Management Objects:
Querying DMVs to check query statistics, usage of buffers, partitions, indexes.
All the data we get is from the last time we did a restart to the server. We need to consider whether we have enough information to have relevant statistics. At the same time, this enables us the investigation of a recent problem.
The numbers we see should be viewed in relation to your system and server. A certain number of scans can be considered high or low - considering your specific situation.
Data Collector:
New to SQL Server 2008. Including data on disk usage, query statistics and server activity. We get pre-defined reports which we can drill down on.
Extended Events:
In the next version of SQL Server the profiler will be based on this tool.
This is a live post from the session of Henk van der Valk at Tech Ed Israel 2010 .
This session has great tips and tricks for you in SSIS and is in English (just in case you're reading this blog and don't understand Hebrew :))
Checking for problems when test loading data:
Select * from sys.dm_os_wait_stats
Number 1 wait: pageiolatch_up
Solution: add more spindles
So - just add more hardware?
Number 2 pagelatch_up
Solution: add more database files
Configure 1 to 4 files per filegroup to get 200+ KB writes IOs.
Use Soft Numa - assign a specific port for it
Use money data type instead of integer
Use Fast Parse
New to SQL Server 2008 R2:
Support for 64+ cores
Enterprise class SSD (Solid State Disks)
Unisys SQL Server SSD Solution codename SQL PowerRack
Bulk Insert file in SSIS - how can you speed it up?
Use a conditional split + modulo to double the speed (increasing the throughput)
Reading from a table as fast as possible
Read the data from 3 sources (all are the same table) using the union all to connect the data inside a new table. Use maxdop 1 for each OLEDB source.
Table partitioning with hashing speeds up reading from both Disks and Memory. Tested for spindles. In SSD we get even better results.
Page lookup \ sec and IO Read bytes \ sec to see how many pages are being read and whether they are from disk or from memory. Perhaps your problem isn't in SQL Server, but rather in your connectivity.
Backup with compression in SSD - for better performance, backup to multiple files rather than just one. Same for restore - restore from multiple file and limit it to Maxdop 1. Do not oversize the log file.
Activity monitor - check for response time in SQL Server 2008. In SQL Server 2005, look at sys.dm_io_pending_io_requests and sys.dm_io_virtual_file_stats.
For more information, check out Mr. Van Der Valk's blog (link above) and the paper on Data Loading Performance Guide.
This is a live post from the session of Asaf Bar and Erez Avital at Tech Ed Israel 2010 .
SharePoint 2010 enables us getting a BI solution with a workflow, social computing and an office integrated solution.
Tools for BI in SharePoint 2010:
PerformancePoint (is the main tool for BI in SharePoint)
Excel Services
PowerPivot
Visio Services
SQL Reporting Services
We can use Lync to connect to the employees in the workplace directly from the SharePoint Dashboard.
We can add a comment to the values in the scorecard to communicate with each other.
Dashboard Designer in SharePoint 2010 opens as a click once application.
We can create different parts for our dashboard and collect them together in a specific folder (which we define in the property of the part).
We can connect several data sources in our dashboard using a shared dimension to both cubes (assuming we are using cubes). We also need to remember to connect the time intelligence to both sources.
We can also re-use parts from other dashboard if they also use the same cube.
We can define in the active directory that a certain person is connected to a certain product. We can then create a SharePoint list which gets its data from the active directory. We can then connect that as well to our analytical grid for sales of that product. Then, if we see something for the sales of the product we'd like to investigate further, we can connect directly the responsible employee.
This is a live post from the session of Ronen Chen at Tech Ed Israel 2010 .
New to report builder 3.0 - we can be in local mode or server mode. We can check it in relation to performance on the server and save in directly on the server (rather than on my PC).
Zooming and moving my map is only possible during the design of the report. We cannot zoom in on it after running the report. A zoom in is possible through an action in the report which will create the zoom in a new report.
Avoid embedding maps as images in your report as that makes the rendering longer and inefficient.
Use the report gallery to store maps for your clinets to re-use.
Downloads for maps which connect to Reporting Services 2008 R2:
Shape2SQL
Free shapes files at http://www.census.gov/
New to report builder 3.0 is sparklines and indicators.
Data bars help us understand values for the different members in a group and sparklines can help us understand the trend over time of that member.
Lookup is a new function to report builder 3.0 which enables us to return a single value from a different dataset. We can return a set of values with the function lookupset.
Best practice is to bring everything in one dataset, but these functions enable us to connect several datasets.
In visibility, we can define whether to render an object when we save a report to excel (or any other file).
Don't know if you're aware of it, but Microsoft just rocked your world!
I remember interviewing Mr. Ariel Netz, Product Unit Manager for SSRS. I wanted to hear from him what's planned for Report Builder 1.0 as I didn't feel like Report Builder 3.0 covered all the functionality that was in 1.0. I also wondered what sort of answer we will get for ad - hoc reporting against SSAS in SSRS. Mr. Netz said as much as he could I guess, as things were still under NDA. And now the future of Reporting Services is here...
Project Crescent was just presented in the keynote PASS Summit going on in Seattle (to view the keynote you need to register to PASS. Registration is free and well worth it. Demo starts at 1 hour 24 minutes into the keynote. Earlier, at 1:13:00, starts the presentation of SQL Server 2011 - codename Denali). Project Crescent gives you ad - hoc reporting with great visualizations based on Silverlight and running in the browser. Project Crescent gives us one environment for building and seeing the report (no more switching between design mode and run mode). So, a great new tool for reporting? A definite and very big YES. But... As Mr. Teo Lachev pointed out in his blog, Project Crescent is now "the fifth report designer after BIDS, Report Builder 1.0 (not sure if RB 1.0 will survive SQL 11), Report Builder 3.0, Visual Studio Report Designer". Also, as the next day of PASS Summit ended, more details came about. Apparently, again from a post by Mr. Lachev, Project Crescent only works against Business Intelligence Semantic Model (BISM) - what's that?
This is where SSAS in SQL Server 2011 jumps into the picture. BISM is a new layer added to cubes in SQL Server 2011 and was first received in mixed feelings as described in Mr. Chris Webb's blog (in a very interesting post that I recommend you read). A post from the SQL Server Team blog published later, started to clear up a bit more. In that post, BISM is described as "a relational (tables and relationships) model with BI artifacts such as hierarchies and KPIs". As Mr. T.K. Anand also points out in that post, "we envision that the BI Semantic Model will offer the choice of MDX as well, but this will likely come in a release after Denali." In the meanwhile, it supports only DAX. BISM will run side by side to the UDM in SSAS, and you choose which to use.
I feel like we're looking at another big change. Possibly one that's even bigger than the move from cubes as we knew them in SQL Server 2000 to the new shape in SQL Server 2005. And this one doesn't just affect SSAS 2011, but encapsulates everything. BISM is powering PowerPivot for Excel and PowerPivot for SharePoint, and is the new model to query in Project Crescent. I feel like BISM is the new face of BI in SQL Server 2011.
Another big announcement is the integration of the VertiPaq in-memory column store into SSAS. In the Keynote demo, you could see just how quickly you could get results with VertiPaq in comparison to the old engine, and how this now gives new way to ROLAP in SSAS.
So, there's a lot going on and I recommend you go into all the links I gave you above for the complete picture, as I have only given you the bullet points.
All in all, I think this means that now will be a good time to start learning DAX!
And this is only the beginning for SQL Server 2011...
Well actually, I'm sure Tech Ed is ready. After all, they've been working on it in Microsoft Israel for so long... :)
I opened my email one day, about a week ago, and looked at a few mails. Going back into the inbox, I saw it. I couldn't help but let out a little yell. "What happened?" my boss turned to me, (I guess it was a little yell, though strong enough to hear). With my hand still over my mouth, all I could do was point at my PC screen. He came over to look at the title of the email and said "Wow!". Which was exactly what I was thinking too!
The title of the mail read "Guess who's coming to Tech Ed as a guest of Microsoft? You are!"
I was just soooooooooooo excited. I went and told my friends in the office, called my brother, then my parents...
This will be my first time in Tech Ed and I'm really looking forward to it. Except for learning new things in BI, I'm also interested in learning more about BI in SharePoint and seeing the previews of what's expected in SQL Server 2011 - codename Denali.
At this point, I would also like to say a very big thank you to Michal Gonen from Microsoft who invited me.
Stay tuned for the end of November, when I'll be posting from Tech Ed 2010 in Eilat. I hope to see you there!
