Enhance Your SQL Server Skills at SQLBits 2008: Key Insights and Innovations

1. Platinum Learn & Enjoy [Put your phone on Vibrate!] www.sqlbits.com Group BY: [Remember get your Tickets for Entry, Coach, Drink] Gold Feedback Forms: [Fill these out at the end of each session please] Silver Lunch Time Sessions: [Once you eat, learn some more; Grok Talks in Chicago 1 and 2 Quest are in Menphis, Idera are in Everest] Ask The Experts[Sessions need to finish on time, take questions to the ATE area]

2. What’s New in Analysis Services 2008? Chris Webb Crossjoin Consulting Limited / Solid Quality Mentors chris@crossjoin.co.uk

3. The short answer… …nothing much!

4. Agenda • Making development easier • MDX • Engine performance improvements • Manageability • Data Mining

5. Best Practices Built-In • The best practices seen in SQL2005 BPA have now been built into BIDS • This means that if you make a questionable design decision, BIDS will display a blue squiggly line • Luckily this can be turned off

6. Attribute Relationship Designer • It is now much easier to design attribute relationships in dimensions, and much more obvious that you should do so • There is a new Attribute Relationship designer tab in the dimension editor, which allows you to visualise and edit relationships • Install BIDS Helper if you want something similar for AS2005

7. Aggregation Designer • Similarly, it will now be possible to view and design aggregations manually within BIDS • Although again this is currently possible with BIDS Helper or the Aggregation Manager sample app • BIDS will be able to validate manual aggregation designs to make sure that you don’t do anything stupid • The Aggregation Design Wizard algorithm will also be improved

8. Minor MDX Changes • Several minor changes have been made to MDX to fix omissions in AS2005 • The CREATE MEMBER and CREATE SET statements now allow you to specify • Display folders • Associated measure groups • There’s also a new CREATE KPI statement for declaring KPIs

9. UPDATE MEMBER Statement • You can now change the definition of calculated members defined with session scope using the UPDATE MEMBER statement • Note this does not work with calculated members defined on the cube • As a result, of very limited usefulness

10. Dynamic Sets • You can now declare named sets which are evaluated on a per-query basis in the context of the WHERE clause and subselects • They are not fully dynamic though – they are only evaluated once per query • This limits their usefulness, although they mightstill be ableto improve the performance of some calculations

11. Programmability • ADOMDServer namespace has some cool new personalisation features • You can get a reference to the current connection and user name • You can access events such as SessionOpened, SessionClosing, CubeOpened, CubeClosing • You can create calculations for the current session

12. Block Computation • Block Computation, aka Bulk Evaluation, is a more efficient way for the AS Storage Engine to retrieve sparse data from a partition • AS2005 can only use it some of the time, for example when Non_Empty_Behavior is used • AS2008 will aim to use it almost all the time, so queries that are currently slow will (hopefully) be faster

13. Scale-Out Scenarios • It will be possible to connect a single read-only database to multiple AS instances • Will make scaling out much easier – no need for synchronising servers

14. Improved Backup Performance • Backup times for AS2005 databases increase exponentially for sizes over 20Gb • AS2008 backup times should grow in proportion to the size of the database

15. MOLAP Writeback • Writeback in AS2005 involves allocating values down to fact table granularity then writing rows back to a relational table • This is often slooooooooow • Even PerformancePoint doesn’t use AS writeback • AS2008 should be able to write values back direct to a MOLAP partition, which will be faster

16. Data Management Views • DMVswill expose information currently available through XMLA commands as relational structures • Make it easier for DBAs to see who is connected, look for runaway queries etc • Also should be easier to get at this information in tools such as SSRS and SSIS

17. Data Mining • Time series algorithm is now optimised for long term as well as short term predictions • You will be able to automatically split data into testing and training data sets • You will also be able to filter data within a single data mining structure

18. Platinum www.SQLBits.com[Conference Web site] www.sqlbits.com www.SQLBlogCasts.com[Becoming the premier Blogging site for SQL professionals] Gold www.SQLServerFAQ.com[UK SQL Server Community Website] Silver UK SQL Bloggers cwebbbi.spaces.live.comsqlblogcasts.com/blogs/simonssqlblogcasts.com/blogs/tonyrogerson Remember :: Feedback Forms!!