I had been waiting for Amazon to send my copy of Joy Mundy's, Warren Thorntwaite and Ralph Kimball's new book and let me tell you after going through it for the past few days, I am extremely excited of the potential all customers that I work with will have as they work on their own DW/BI system initiatives.
If you ever wondered after reading a theory book how am I ever going to apply this theory to the tools that I have to work with? Well, this book answers that exact question as it takes the best of the conceptual insights from Ralph Kimball's Data Warehouse Life Cycle toolkit and walks through how to use those insights with all the components of SQL Server 2005 including ETL practice with SSIS, data modeling, OLAP, APPLIED data mining, reporting and BI applications.
Here is a link to save you the trouble to finding it on Amazon :) http://www.amazon.com/gp/product/0471267155/sr=8-1/qid=1143313605/ref=pd_bbs_1/103-4596045-3657420?%5Fencoding=UTF8
-Raheel
One of the major differences in working with Analysis Services 2005 as compared to Analysis Services 2000 is that there is a specific developer environment to develop your cubes, data mining models etc and then there is a seperate interface for managing your Analysis Services 2005 server; SQL Server Management Studio.
This is different compared to AS 2000 as there was just one tool for developing and managing your cubes Analysis Manager. Therefore is some ways if you are used to the older way of doing things you would create your security roles using Analysis Manager. But with seperate interfaces with SSAS 2005 which tool do you use for that purpose?
Well, I have been fielding questions on dissappearing security roles within your SSAS environment. If you used the BI Dev Studio to create your cube and deployed it to the server and then used the Management Studio to assign security but then you went back to the BI Dev Studio and rebuilt your project; guess what everything on the server is rebuilt and therefore it overwrites your security assignments. AHA..
You either assign security roles withing your BI Dev Studio package so if you ever rebuilt your cube the security structure is rebuilt or you dont always rebuild the structure of the cibe just the data ensuring that the security setting always stay on the server..
-Raheel
I just installed beta version of Excel 2007 and there is a lot of interesting integration with Analysis Services 2005. For a list of enhancements to Pivot Tables check out the Excel Blog at
http://blogs.msdn.com/excel/archive/category/11544.aspx. I will have additional posts as I learn more.
If you have not worked with the Report Packs now is the time. These Report Packs provide you with out of the box Reports for your reporting needs such as reporting on Reporting Services logs itself, Integration Services as well as Exchange and other Microsoft Dynamics components.
The SQL Server 2005 Report Packs can be downloaded from two external destination sites:
MS Downloads URL – Download SS2005 Report Packs
GotDotNet CodeGallery – : http://codegallery.gotdotnet.com/sqlrptpack (free membership)
The Report Packs are NOT supported like other Microsoft products. If you have technical questions, you are encouraged to visit the GotDotNet CodeGallery – : http://codegallery.gotdotnet.com/sqlrptpack (membership is free).
Enjoy!!
There is a new capability within Reporting Services that allows ad-hoc reporting for end users directly. That tool is called Report Builder and can be invoked through the Report Manager web interface. In order to deliver this capability though to the end user first thing you need to do is provide the user with a Report Model. A Report Model is a very intelligent semantic model that can currently be built on top of multiple SQL Server or Analysis Services sources. The goal of the Report Model is to provide a subject area definition of the data that a user has access to along with friendly names and description of data for easier Report Creation.
The Report Model is a specific type of a project available in the Business Intelligence Development Studio ( BI Debv Studio), the development environment shipped with SQL Server 2005.
A lot of customers are excited about this capability and rightly so. Report Builder is a fantastic tool and can add a lot of value to the overall BI stratgey of an organization. The question people usually ask me is how do I build a model on top of Analysis Services.
Well, if you try and create a new Report Model in the BI Dev Studio, you will notice that it only allows you to create a model on top of a SQL Server source. It does not allow you to create one on top of Analysis Services. The primary reason is that an Analysis Services cube already has a lot of the model capability built in it, like understanding of hierarchies, attributes etc that the model can leverage. Therefore in order to create a Report Model on top of Analysis Services this is what we need to do:
1) Open the SQL Server Management Studio. This is the management tool inlcuded with SQL Server 2005 that manages all the components of SQL Server. This includes the relational database, Analysis Services, Reporting Services, Integration Services and SQL Server Mobile databases. Register your Report Server in the Management Studio. TIP: To register your Report Server with the Management studio provide name like : http://<servername>/reportserver
2) Once registered you will be able to traverse the objects in Reporting Services like your Data Sources folder, ReportS folder etc. Rigtt click on the Data Sources Folder and make a new connection to your Analysis Services cube.
3) Once the connection is made, right click on the connection and one of the options you will now see is to Generate Model. Thats it.
Once the model is generated a user can use Report Builder to use all the rich hierarchies and attribute availability to build their own reports.
Meta Data is an area where a lot of customers are looking for a solution. SQL Server 2005 components specifically Integration Services, Analysis Services and Report Model capabilities in Reporting Services provide a mechanism to bridge multiple backend sources for either data integration purposes, analysis purposes or reporting purposes. The question comes when a need to understand data lineage or dependencies between the data is required. In this case, this recently posted Metadata Samples Toolkit aims to provide that information to the BI team.
I have not had a chance to look into it in detail yet. I do plan to very soon and will share the details as soon as I do.
http://www.microsoft.com/downloads/details.aspx?FamilyID=11DAA4D1-196D-4F2A-B18F-891579C364F4&displaylang=en
Happy New Year everyone!!
Raheel
So I just came off a mini project working with a customer who is trying to solve a common problem. Integrating data across multiple sources for better insight to the business user. The difference: the customer prefers Oracle as their underlying database platform and any component of Microsoft technlogy stack should a) not include the SQL Server relational database and b) provide seamless integration of the 2 main Oracle databases that this customer has.
The answer of course is Analysis Services 2005. I was very very impressed as I worked through the proving but at the same time I learnt a lot. Here are the things I learnt and plan to share each learning in a subsequent blog:
1) Installing all the Microsoft components. Which order, what are the dependencies and what are the steps. I found there is a need for a little bit more simplification of the steps. For example, Installing Business Scorecard Manager, making sure the right client tools are installed for Oracle, getting Reporting Services and Sharepoint on the same machine etc..
2) KPIUTIL.EXE rocks!! Trying to centralize Key Performance Indicators in Analysis Services 2005 (BTW I plan to start using SSAS for Analysis Services ) so that many querying tools can access those KPI"S is a phenomenal idea. And if you are using Business Scorecard Manager (BSM) to deliver this KPI u want to use the KPIUTIL.EXE. I was very impressed how it just took a KPI in SSAS and published it to the BSM server. I will plan to write more about that.
3) Mappoint rocks!! Again, connecting to SSAS to deiver geographical data. Awesome.
Raheel
Hi there,
Well, this is the place where I plan to share a lot of what I have learnt over the years helping customers with their data and intelligence challenges. I do work for Microsoft, so helping customers has always been in context of helping them evaluate whether Microsoft technology would allow them to achieve what they are struggling with.
With the release of SQL Server 2005 and all of its capabilities that can help organizations get over the hump of providing valuable insight into the data that seems to be growing every single day, I believe there is a lot to share. Integrating Share Point technologies, Office specifically Excel and the new Business Scorecard Manager and not to forget the upcoming Office 12 release which will also complement what the SQL Server team has delivered in SQL Server 2005; there is a lot to discuss in terms of practices, how - to's and many insights as I work with customers who are looking to Microsoft technology stack to deliver a comprehensive and integrated solution.
I hope the insights I share with you are valuable as you are solving your organization's Business Intelligence needs.
Raheel
