 | | |
| | |
|
Spaces home  Dan English's BI BlogProfileFriendsBlogMore |  Tools   |
Dan English's BI BlogWelcome to my random thoughts
|
||||||||||||||||||||||||||||
|
Thanks for visiting! Minneapolis Only
|
May 07 Did you know? SQL Server 2008 includes AuditingSQL Server 2008 includes Auditing (server and database):
· Check out a screencast about the new Audit feature in SQL Server 2008 here. · Read more about this in the SQL Server 2008 BOL ‘Understanding SQL Server Audit’.
April 26 BIDS Helper on CodePlex.comI blogged about the new release back in December here when version 1.2.0.1 came out which fixed a few issues with the SSAS buttons in the calculation tab and with the SSIS highlighter. In talking with colleagues and clients recently though it seems like quite a few people are surprised when I talk about BIDS Helper. I thought everyone already knew about it or was using it, but I guess not. I really feel that this is an excellent add-in for anyone doing SSAS or SSIS development that I had to post another blog posting. I know that my blog is starting to get more coverage thanks to the BI Blog aggregator http://biglogs.com and now that my blog is mirrored on the Magenic blog site at http://blog.magenic.com/blogs I wanted to post another entry promoting the BIDS Helper which is available here. So what is so cool about BIDS Helper and why would you want to use it? Well the main features that I like are the options to deploy the MDX script in the calculations tab of the cube, aggregation manager, update estimated counts, column usage reports, expression and configuration highlighter, expression list, and smart diff (all of the features are cool and helpful, but these are some of the key ones I use). If you go to the site on CodePlex you can get detailed information about each feature with screenshots. It appears that this add-in currently only works with the English version of BIDS and that it probably only works with 2005 and not 2008 versions of Visual Studio. I haven't actually tested it in VS 2008 yet, but according to some comments on the site there appears to be some issues. Here is the complete list of features that are currently included with the BIDS Helper along with a picture from the site that highlights a few of the features: Features
I did take a quick peek at the Source Code section for this Project and it appears that version 1.3.0.0 should be available anytime now. Some of the new features according to the comments are the ability to sort the SSIS packages by name (like you can do in SSAS), VS 2008 support, SQL Sever 2008 support, smart diff for SSAS objects, and more performance improvements Microsoft Business Intelligence VPC Release 6Yesterday I received an email from my colleague, Kory, asking me if I had heard of the new BI VPC version 6 (April 2008 release) that was available. I stated that the only one that I knew of was version 5.1 which I posted on my blog back in December here. I quickly queried the Microsoft Download Center here and I didn't see a new version available. I asked him where he saw this mentioned and he pointed me to a new BI blog that I had not seen yet by Peter Koller over in Norway here. Now I had to know where Peter discovered this so I left him a comment and he pointed me to the hidden gem buried on Microsoft's site here (look down in the lower right for the download links - 7 files). So what is different. Version 6 includes new demos and presenter scripts for different verticals. When you extract the files it creates an additional demos folder which now contains all of the scripts so you don't need to try and pull them off the VPC at all. It still has all of the same programs installed and configured which is very nice I didn't see a readme file associated with this, so if you are looking for the password it is the typical Microsoft demo one and the same one that was used on the last VPC, pass@word1. So let the downloads begin! Enjoy the VPC and I got to thank Kory and Peter for pointing this out and sharing this information with the community, thanks guys! April 24 Did you know? SQL Server 2008 includes GROUPING SETSSQL Server 2008 includes GROUPING SETS:
Read more about this in the SQL Server 2008 BOL ‘GROUPING SETS’. Check out a screencast about the new GROUPING SETS feature in SQL Server 2008 here.
April 19 SSAS MDX Round = Banker's RoundingA couple of weeks ago I was working on a project where I had to implement some Ranking calculations within an Analysis Service (SSAS) cube. I went ahead and added in the calculations and then the client came back to me and stated that there was some additional business logic that we needed to implement. At different levels of reporting they utilize some rounding into the calculation that is used to determine the Ranking. When viewing the report at a store level then we need to round the calculation to two decimal places and at all other levels the value will be rounded to four decimal places to be used in the Ranking function. I thought to myself 'no problem' and I went ahead and added in this additional logic and put the Round function into place to handle this logic. Done. Well was I wrong. The client came back to me and said that stores were not being ranked properly and why if these had the same calculation score, 93%, did they not have the same rank. I was like 'what? all i did was add the business logic you requested'. So I went ahead and started digging into the issue and this opened up a whole can of worms that took me a couple of days to sort out and it was a nightmare -- rounding is just plain bad. What we were seeing on the report was a score of 93% and two scores would have values of say 92.75% and 92.50%. Now back from my school days using the basic arithmetic round that we all used we would say that these should both be rounded up to 93%, well that 'depends'. It turns out that there are quite a few methods of rounding and unfortunately the Microsoft products all differ in how they use the round function and I found a good article explaining that here. It turns out that in this case Banker's Rounding was implemented where if the value is at say .5 it will simply round to the nearest even number. And sure enough this is what was happening; here are some examples that show this (replace cube name with a valid cube name):
This was not what we wanted, so now I was on a quest to resolve this. I thought I could just try and utilize the vbamdx.round call, but that returned the same thing. I went ahead and tried using the system.math.round call in .NET code to see what that returned to see if I could possibly just implement an SSAS stored procedure (functions are what I call them), but same thing. I opened up my trusty Excel and tried the round function in there and it worked the way I expected it to. I was like it is about time...so I thought I would simple use the excelmdx.round in my calculation, but it turns out you actually need to install Excel on the server (found reference to this in the MSDN forums here) for this to work and it will fire up an instance of the excel object when you use this. So this was not a solution either since we were not going to do that. I found another solution in the MSDN forums here that stated that you could just use the following logic -int(-(VALUE*100)) and sure it works for my .9250 value, but this logic is basically just returning the CEILING and this is not what I wanted for all of my values (like .9210 would display as 93 -- not what we wanted). What I ended up doing was implementing some crazy CASE logic that evaluated the value to determine if the last numeric value on the right was a 5 then use the CEILING function that I just listed, otherwise just go with the regular round function. So now I was done, right? Wrong. Now we were still getting a few stores that were still not lining up where the report would show 93%, but what was being used by the Ranking was still 92%, so now what was the problem. It turns out the calculation was returning .9250, but it was actually .92499999, so why was the calculation returning .9250? I didn't have any type of formatting in the calculation, this was just a straight calculation returning the underlying value. This was driving me completely insane. How did I go about resolving this additional rounding issue, good question. I ended up doing the following: int(value*100000)/100000 and using this value instead of just value. So now I was done, right? Wrong again. Now we were still seeing some incorrect values. This is what we ended up seeing and shows the different values returned by int and cint.
Incorrect calc: int(round(value),2)*100) = 57 Fixed calc: cint(round(value),2)*100) = 58 I went ahead and changed my references of int to cint and finally I had victory! I was actually done solving this crazy rounding issue. It wouldn't have been that bad if we were just rounding and displaying values; this could have basically been handled by using some formatting in the calculation, but when you need to actually use the result in a Ranking function it is a completely different story. I am really hoping that there was an easier solution for this, but being under the gun and needing this implemented by the deadline this is what I came up with. I was able to finally get a good night sleep. April 15 Did you know? SQL Server 2008 includes Cache Transform screencastSQL Server 2008 includes Cache Transform: You can configure the Cache connection manager to save the data to a cache file (.caw). The cache file can be populated once and reused throughout the ETL process within multiple Lookup transformations for performing surrogate key lookups and populating the data warehouse.
Read more about this in the SQL Server 2008 BOL ‘Cache Transform’. Check out a screencast about the new cache transform transformation feature in SQL Server 2008 here.
Video: Did you know? SQL Server 2008 includes Cache Transform
If you are interested in additional information in regards to this topic check out these other blog postings and sites: http://www.sqlskills.com/blogs/stacia/2007/10/17/SQLServer2008LookupTransformationAndCaching.aspx You can also check out a video on YouTube done by Jamie Thomson in regards to this new transformation here. The last time I viewed it there wasn't any audio, just video. You can download the SSIS samples from CodePlex and take a look at a sample utilizing the cache transform here. April 06 Did you know? SQL Server 2008 includes Data Profiling Task screencastSQL Server 2008 includes Data Profiling Task: The Data Profiling task is a new task in the Integration Services toolbox. You can use this task inside an Integration Services package to profile data that is stored in SQL Server. The information provided by the profile helps you identify potential problems with data quality. The Data Profiling task provides profiles that help identify data quality problems within individual columns and with column relationships: Profiles that help identify problems within individual columns · The distribution of lengths in the column values. · The percentage of null values. · The distribution of values in the column. · Column statistics for numeric columns. · Regular expressions that match string columns. Profiles that help identify problems with column relationships · Candidate key columns. · Functional dependencies between columns. · The inclusion of the set of values in one column in the set of values in another column. Read more about this in the SQL Server 2008 BOL ‘Data Profiling Task’. Check out a screencast about the new data profiling task feature in SQL Server 2008 here. If you are interested in additional information in regards to this topic check out these other blog postings: http://www.sqlskills.com/blogs/stacia/2007/11/27/SQLServer2008DataProfilingTaskTheQuickVersion.aspx OR SSIS: Data Profiling Task: Part 1 - Introduction Data Profiling Task: Part 6 - Column Value Distribution SSIS: Data Profiling Task: Part 10 - Parsing the output and if you want to know how to dynamically profile tables check out this blog posting which includes the code here. Did you know? SQL Server 2008 includes Intellisense screencastA new thing we are trying internally at work is sending out weekly 'Did you know?' (DYN) emails about new features that are included in SQL Server 2008. We are trying to spread awareness and excitement about the new version of SQL Server 2008. The emails include information about a new feature, links to the topic in BOL, a internal discuss in regards to the topic, and SQL Server 2008 information (main page site, trial download site, learning portal, webcasts, and virtual labs). The other item that we are including is a screencast of the new feature. So far I have done two of the DYN topics and I thought I would start sharing them with the rest of the community. Here is the screencast for Intellisense and I will post the other screencast on Data Profiling task next. SQL Server 2008 includes T-SQL IntelliSense: Read more about this in the SQL Server 2008 BOL ‘Using Intellisense’. Check out a screencast about the new intellisense feature in SQL Server 2008 here. April 05 Heroes Happen {Here} SoftwareJust another thing in regards to attending one of the local launch events, free software Don't miss a chance to attend if you haven't already missed it in your local area -- Heroes Happen {Here} Registration. One other thing, stop by the Microsoft Business Intelligence booth and pickup a copy of the Microsoft BI Resource Kit if you haven't gotten one already. This was release at the Microsoft BI Conference last year, but it is loaded with lots of useful white papers and webcasts. Hereos Happen {Here} Hands on Lab ManualsIf you went to one of the launches there was a hands on lab setup with some great exercises that you could take to take a look at some of the features that are available in Windows Server 2008, Visual Studio 2008, and SQL Server 2008. I did some of the PowerShell labs for Windows Server 2008 (a few minor issues in the labs, but nothing that I couldn't figure out) and Change Data Capture (CDC) and Table Partitioning (not a new feature in 2008, exists in 2005 - Enterprise Edition) in SQL Server 2008. If you haven't attended a launch yet I would highly recommend checking out the virtual lab area, but if you don't get a chance or missed out here is the link to the site that has all of the lab manuals you can download. TechNet BI Virtual LabsJust wanted to point out the Business Intelligence virtual labs that are available on the TechNet web site. There are three labs in regards to PerformancePoint server too Business Intelligence
Don't forget to check out the ones in regards to SQL Server 2008 and get ready for the new product release and take the new version for a test drive. There are two locations for these virtual labs, there are some on MSDN and some on TechNet. March 03 Book Review - The Rational Guide To Planning with Microsoft Office PerformancePoint Server 2007Just finished another book by Nick Barclay and Adrian Downes titled The Rational Guide To Planning with Microsoft Office PerformancePoint Server 2007. This book was a quick read and very informative. This covers the third portion of the PerformancePoint Server product, Planning. This was another excellent read and a great addition to the other book they wrote, The Rational Guide To Planning with Microsoft Office PerformancePoint Server 2007. It is concise and has really good tech tips, notes, and caution items. This book basically included a HOL to walk you through a proof-of-concept using the AdventureWorksDW data. It tells you how to install the software, setup the planning application and models, populate the staging and application databases with the included scripts and examples, add business rules, create form templates (and make them dynamic based on system parameters), and more. The bonus chapters wrap-up the exercise pushing the results to the monitoring and analysis portion of PerformancePoint Server to complete the cycle and tie everything together. After reading their book I really feel comfortable with what the planning portion provides and have a complete understanding of how to setup the product and populate the databases. The examples for scripts that Nick and Adrian provide were very helpful (and it is always interesting to see how other people write their T-SQL and MDX statements). They explain the tables and stored procedures that are setup in the databases that you can utilize to get information out and how everything relates. I could go on-and-on about the book because it was jam packed with information (and it was only 250 pages long -- plus the bonus chapters that you can download and read). The content in the book is right on and the examples are very thorough and cover nearly every portion of the application to some extent (in some areas they simply refer you to the documentation since they cannot cover everything in as much detail as they would like). Obviously this book could have been at least three times the size to cover everything in detail, but Nick and Adrian include all of the necessary information needed to use the software. I would give this book a 5 out of 5 stars. I would definitely recommend adding this book to your collection. Check out my review on their other book for PerformancePoint Server here.
March 01 Using Reporting Services (SSRS) with SSAS dataOver the past few weeks I have had to work on a project to generate Reporting Services 2005 reports with data from an Analysis Services (SSAS) 2005 database. I decided I would go ahead and blog about some of my discoveries and discuss the different options that are available along with any findings (gotchas). I cannot cover every single option in detail, that would require writing a few chapters in a SSRS book (which I would be more than willing to do), but I will go into some detail into what I experienced. I basically explored four different ways to reference the SSAS data in SSRS. Two of the ways utilized the Microsoft SQL Server Analysis Services type of data source and the other two used Integration Services (SSIS) 2005 and SQL Server 2005 stored procedures. With using the SSAS data source within SSRS you can either use the new SSAS designer within SSRS and drap-and-drop to create your report and parameters or you can edit the dataset and enter in your own query string. If you go with the SSAS designer you can switch from design mode to see the generated MDX statement by click on the icon in the toolbar (see picture below) and when using this designer you need to keep the Measures in the columns.
Here is a quick walk through of what the SSAS designer looks like within SSRS 2005 in BIDS with the AdventureWorks Report Sample Pack that you can download from CodePlex (this is the Sales Reason Comparisons Report):
The designer is really nice and a major improvement over SSRS 2000. The other option when using the SSAS data source would be to just enter in your query string by editing the data source, but by doing so you don't get the nice drag-and-drop functionality or the parameter generation. The other thing you doing get is the auto generation of the dataset fields which isn't a straight forward exercise to setup if you are not familiar with XML and the design of the cube structure. Here is a preview of what is setup with the ProductData fields: If you want to enter in the query string by not using the designer or the MDX window that comes with it I would use the designer initial to generate the field list and then put the MDX generated statement into the query string window and modify it accordingly to get your field list generate for you. The one thing to point out in regards to using SSAS data is that hierarchies get flattened out and you can see that above in the Date Fiscal Year dataset. Another option of getting at SSAS data would be utilizing a Report Model and using Report Builder to create ad-hoc reports, but that is beyond the scope of this blog posting and that also could entail a complete chapter in a book. If you are interested then you can view this in the SQL Server BOL. Getting on to the other two options with SSIS and SQL Server stored procedures. Using SSIS as a data source for SSRS in BIDS requires you to modify the RSReportDesigner.config and once deployed to the web server a modification to the RSReportServer.config. This is fully documented here. Now within SSIS you will need to setup the Data Flow Task to generate the DataReader Destination which will be used by the SSRS report. To get the data from SSAS in SSIS you can use two different Data Flow Sources, either the DataReader source or the OLE DB source. If you use the DataReader source you will create a ADO.NET connection to use to connect to the SSAS dabase and by doing this you will be able to bypass this annoying message that you get with using the OLE DB source when you try to preview the results of the MDX statement:
If you click 'OK' it will still display your flattened out data as expected with some long column name references which you will want to modify the names of in the output columns so they are more easily readable and can be referenced appropriately in other Data Flow Transformations (like the Derived Column).
Setting up the DataReader source and the OLE DB source is a little different. I won't go into great detail to keep this posting somewhat short, but the two main differences are that the DataReader doesn't have a preview button and it uses DT_NTEXT as the source data type and OLE DB has a preview button and uses DT_WSTR as the data type (which produces the warning message above). The other thing to point out is if you are going to use the OLE DB source you will need to modify your connection string to include the following Data "Format=Tabular;" (without the quotes) otherwise you will get an error message when you try to use this SSIS package in SSRS. Once you get these sources setup and the output columns renamed appropriately then you can add in any additional business requirements and logic needed to produce the data to be used by the DataReader destination. Here is a screenshot where I had a requirement to combine two sets of data from SSAS and inter-mix the output columns from the data and I had to add a third data source eventually to combine that had data based off a different hierarchy that did not align with the first two sets of data. An odd request, but none the less a real world example that needs to be solved (nothing is ever easy). I had to create a nice column header column to unflatten the date hierarchy and establish level and row sorting information to inter-mix the two sets of data to be easily utilized in a SSRS matrix report item.
Once you get this all setup the one thing that you will need to do before you are ready to reference this SSIS package in SSRS is to go into the DataReader destination and actually select the Input columns that you want to Output for SSRS (an easy thing to overlook when setting this all up). The other thing to note here is that the Error Output was setup for the source of the data and this was needed for some reason because I was receiving an error message, which of course I cannot reproduce now to display, but to overcome this error message I added the Error Output and this solved the issue (I used the Trash Destination which you can download from here).Once this is saved you are ready to setup your SSIS data source in SSRS to utilize the SSIS package. Here is a screenshot of how the data source is setup and setting the query string to be the name of the DataReader destination that is in the SSIS package being referenced.
If everything is setup properly and working then your fields will be populated within the dataset. If you need to utilize parameters and pass them to the SSIS package to set variables then a good blog posting to reference is by Russell Christopher here. Now for the final option that I will be talking about to get at SSAS data is to use SQL Server stored procedures. This was the preferred method that was used since the client wanted to be able to manipulate the data so that it was in a format that would easily be plugged into SSRS (just like the end-result of the SSIS package) and they would be more able to support the stored procedure than the SSIS package. To get this to work you need to perform to setting changes, one in the SQL Server Surface Area Configuration to enable 'Ad Hoc Remote Queries' and the other in SQL Mgmt Studio in Server Objects to the MSOLAP Linked Server provider to 'Allow inprocess'. Here are a couple of screenshots that show these changes (and these do not require you to restart the SQL Service):
Once you have this setup you are ready to create the stored procedures that can be referenced by the SSRS report. Only members of the sysadmin role can utilize the OPENROWSET command, so for security reasons you will most likely end up using the OPENQUERY statement which will require you to setup the Linked Server using the MSOLAP provider. You can then setup the security settings if you are going to map a SQL login to a valid NT domain account that has access to the SSAS database.
Now that this is setup you can generate your OPENQUERY statements. If you need to parameterize these statements at all, which most likely you will, you will need to generate the OPENQUERY statement into a variable, execute the variable statement, and then if any additional modifications are needed to the dataset or if you want to get more intuitive column names populate this into a temp table (table variable or temp table - if table variable then this will need to be included into the statement that you are generating before you execute it). Here is a sample stored procedure going against the Tutorial SSAS database for AdventureWorksDW that is passing in a single Product Category ID to return the dataset for the SSRS report that is using the linked server setup previously: CREATE PROCEDURE [dbo].[usp_DataSet] ( SET NOCOUNT ON; DECLARE @sql_data nvarchar(max) --define the temporary table for the data --statement --populate the temp table, return nothing if there is an error in MDX --return the dataset DROP TABLE #DataSet
Once this is setup then you can use a SQL Server data source in the SSRS report and use the stored procedure type and pass the parameter. I know this was kind of a lengthy posting and I could have gone into much more detail, but I wanted to highlight each option and provide a little insight into each one. This could easily be a few chapters in a book or a white paper to provide more screenshots and to provide more specifics about each option, but this will give you a general idea of what you have available. I hope you enjoyed this and if you have any questions or want more detail feel free to drop me a line (dane@magenic.com). February 28 Microsoft's WorldWide Telescope videoJust received a posting from a colleague and had to check it out. Microsoft unveiled a new technology they have been working based on the work of the late Jim Grey and it is amazing technology. Sometimes we get caught up in our everyday lives and don't realize just how small we really are in respect to the entire universe. It is amazing technology and the images are breathtaking and I can't wait to download this in the Spring. Way to go Microsoft! Watch the video for yourself and read more about it and download it from the site.
February 27 MSDN Reader download nowA few weeks ago I downloaded and installed a project from the new MDSN Code Library called MSDN Reader. This application uses the | |||||||||||||||||||||||||||
Invite as friend
. This is cool stuff and you got to thank 
. Here is everything it contains and I highlighted the new items (and this VPC will expire on 8/17/2009 - maybe just in time for PPS v2). 
