| Dan's profileDan English's BI BlogBlogLists |  Tools  Help |
Dan English's BI BlogWelcome to my random thoughts in regards to Business Intelligence, databases, and other technologies |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Minneapolis Only
|
6/25/2009 Microsoft Reports using RDLCAfter working with Reporting Services since the beta product when it was code named ‘Rosetta’ I finally got my first look at using the Microsoft reporting feature that is part of Visual Studio. I have used the Crystal Reports option within Visual Studio in the past, so I was familiar with the development environment. The Microsoft local report files that are available for you to use within your .NET applications, Windows or Web based, are Reporting Service reports with the exception that they are RDLC files. Some of the things that are different compared to creating Reporting Services server based reports are the following:
It definitely took me a little while to get familiar with creating RDLC reports since I have been using SSRS 2008 and developing server based reports for so long. I missed having the preview option, but you can preview the data if you are using a .NET dataset.
The other thing that threw me for a loop was the configuration of subreports and drillthrough (jump to report navigation) reports. In order to get subreports and drillthrough report options to work this required you to setup additional event handlers. Initially when I was setting this up I was like ‘yeah, you can do subreports and drillthrough…no problem’. Then I actually setup the reports and tried to get these features to work and I was like…hmmm, not as straight forward as I expected. I guess I have really been taking the whole Report Server for granted over the years. Luckily there is an outstanding web site that is available that explains everything about the Report Viewer control and provides really nice code examples to take a look at – http://gotreportviewer.com. The thing to remember though is that if you want to use the local report option within Visual Studio is that this product gets released prior to SQL Server being released. That means that when you hear about new Reporting Services features like tablix, advanced visualizations, rich text support, etc. that these will not be available to you in Visual Studio 2008. This version currently supports the SSRS 2005 features. The next version of Visual Studio 2010 will have the updated Report Viewer control that will support SSRS 2008 features, but then when SQL Server 2008 R2 comes out you will not have those new features like the mapping capabilities. Other features that you will not have access to would be subscriptions, report execution history information, and ad-hoc reporting capabilities. For a complete list of the differences of the RDLC vs. RDL take a look at this online documentation – Reporting Services and ReportViewer Controls in Visual Studio. There are a couple of minor things I found that were either incorrect or missing in the documentation like Report Server no longer requires IIS with SSRS 2008 and also the fact that it now includes Microsoft Word rendering capabilities. If anyone talks about ‘Microsoft Reports’ in the future I will now know what they are referring to. I found it mentioned in the online documentation here – Microsoft Reports. I never knew what people meant when they said that before. I was always like ‘do you mean Reporting Services?’ Maybe they did, but I guess either way it is a form of Reporting Services. 6/3/2009 PASSMN June 16, 2009 Monthly MeetingThe next PASSMN Minnesota SQL Server User Group meeting is coming up. The meeting is on June 16 from 3:00 PM to 5:15 PM. You can visit the local site to get registered at http://www.mnssug.org or click the link to Register Here. This month the topics are: Analysis Services Dimension Creation Best Practices (Speaker: Brian Larson, Superior Consulting Services) - Having dimensions that are well structured and function efficiently is key to having performant cubes and an important factor when encouraging users to use cubes for ad hoc reporting and interactive analysis. This session will look at best practices to observe when designing and creating dimensions in Analysis Services.
Just a reminder that the group meets on the 3rd Tuesday of each month. If you are in the area and available to attend please join us and stop by and say ‘Hi’. 6/2/2009 Magenic Webinar Business Dashboards Follow-upSorry for the delay on this posting in regards to the webinar presentation that I did last week, Magenic Webinar May 28 – Business Dashboards. The recording has been made available on the Magenic site here in the Seminars and Webcasts - Presentations recording area. If for some reason you run into an issue when click on the webinar link you might need to try a different browser (like Firefox), or if you are still unable to access the recording contact Magenic at info@magenic.com. We had close to 60 people attend the live webinar and some good follow-up questions at the end. Some how my Agenda slides got marked as hidden in the presentation and I apologize for that. I should have uploaded the presentation as a handout too within LiveMeeting and I will make a mental note of that in the future. I was using the Microsoft BI VPC version 7 for the demos. If you are interested in downloading that to take a look at that you can check it out here - Microsoft Business Intelligence VPC Release 7.1. That posting includes the links for release 7 and 7.1 downloads. Some of the demos that were in 7 were removed in 7.1 and this VPC environment is still utilizing SQL Server 2005. If you want to see what is included in the VPC you can check out this posting - Microsoft Business Intelligence VPC Release 7. If you were unable to attend the presentation you can download and check out the recording here - Seminars and Webcasts – Presentations. Thanks to everyone that attended and please let me know what you thought so I can look at making adjustments for my future presentations. 5/31/2009 2009 Download Catch-up ListOver the first part of the 2009 year I have downloaded numerous training items, whitepapers, applications, and patches and just wanted to take a little time this weekend to put out a list of items to share that I believe will be useful if you haven’t come across them yet. Some of these maybe a little older, I might have posted about them already, or you might already know about them, but just want to put them out there just in case. Not all of these are strictly SQL Server or Business Intelligence related, so just want to warn you. You can share the information with someone else that might benefit from reviewing the material though. Training Items
Whitepapers
Applications (all FREE)
Patches
I think that covers the majority of it for now. Lots of different items and plenty of stuff to keep you busy for a few weeks. Enjoy! By the way, don’t forget to check out the Microsoft BI blog. There have been lots of useful postings added recently with videos in regards to mobile BI, Excel 2007 Data Mining Add-in, Gemini demos, etc. 5/26/2009 Windows Server 2008 and Vista SP2 is now availableJust a follow-up from my previous posting, Windows Server 2008 and Vista SP2 coming soon, looks like SP2 is now available. SP1 is a prerequisite, but Windows Server 2008 was shipped with SP1. OverviewService Pack 2 for Windows Server 2008 and Windows Vista (SP2) is an update to Windows Vista and Windows Server 2008 that supports new kinds of hardware and emerging hardware standards, and includes all updates delivered since SP1. SP2 simplifies administration by enabling IT administrators to deploy and support a single service pack for clients and servers. Please see the Windows Server SP2/Windows Vista SP2 page on TechNet/MSDN for additional details and documentation.
5/19/2009 PASSMN May 19 SSIS Presentation Follow-upI have posted this content on the PASSMN user site here - May 19, 2009 - Dan English: SSIS Team Development, Deployment and Configuration, but if you do not have access I have also uploaded the presentation and SSIS demo project files to my SkyDrive area and those are available below. Here is a link to the PASSMN May speaker content - PASSMN May 19, 2009 Monthly Meeting. Thanks for everyone that showed up and I really enjoyed the great questions everyone asked and making this interactive. Don’t forget to download and install the latest release of BIDS Helper if you don’t have it already. As I stated at the start of the presentation this content was from a presentation that Dave Pendleton and I did back in January if you want to check out the complete presentation I have that available here - SQL Server Integration Services – Enterprise Manageability Follow-up. I really would like to see these meetings to turn into more of a collaboration where we can discuss each others issues and work on solutions. I think that would be very valuable to our community for us to interact more and bounce ideas off each other. After all, we are all dealing with the same products and most likely have either experienced or read about the same types of issues. If nothing else we might be able to share some insight that might lead to a solution Here are a couple of other links of reference I made to the SSIS samples on CodePlex and also the Microsoft Data Warehouse Toolkit Book Website:
All of the resource links are included in the presentation download. Plus there are a couple of SSIS videos you can watch from last year's PASS National conference here: http://www.sqlpass.org/LearningCenter/SummitOnDemand.aspx Enjoy and let me know if you have any additional questions. Thanks. Don’t forget to check out the SQL Server 2008 R2 site and sign up to be notified when the CTP becomes available - http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx.
5/17/2009 Magenic Webinar May 28 – Business DashboardsI will be doing a Magenic webinar in a couple of weeks following up my recent presentation at the PeopleReady BI Summit that we had here recently in the Minneapolis area - PeopleReady Business Intelligence Summit Follow-up.
Thursday, May 28th 5/15/2009 Slicing Analysis Services (SSAS) Partitions and moreJust wanted to touch base on a couple of items with partitions referencing the ever popular Adventure Works 2008 sample Analysis Service project available to download from CodePlex here – SQL Server 2008 SR1 downloads – it appears that they have bundled the databases together now, used to be broken out by the operational and the data warehouse. The sample project file will be part of this download for Analysis Services and will be located in the following directory if you go with the default setup - C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project. Now if you open up the Enterprise version you will see that the measures groups have multiple partitions defined for them. One thing to note is that the counts are not properly updated and that aggregations have not been defined for all partitions in the cube.
If you have BIDS Helper installed you can go ahead and use the add-in to perform an update on the estimated counts for all of your objects.
Just a warning, if you are going against a very large database you might not want to perform this operation.
As a work around you could go ahead and simply update the estimated row count in the properties for the partition to provide Analysis Services an estimated row count when you define and generate the aggregations for the partitions (actually there is a Partition Count setting that you will see when you go through the aggregation design wizard that gets used by the algorithm when creating aggregations, so it is important to set these values).
After the estimated counts have been updated you will see that the counts are updated, but you would still need to design aggregations for the partitions where these have not been defined yet (you might need to save the cube file, close it, and reopen the file to see the updated counts).
To create the aggregations in SSAS 2008 you have to switch over to the new Aggregations tab in the cube. You can then select the group that you want to design the aggregates for and walk through the wizard.
And you can generate the aggregates for all of the partitions at once.
Ok, now let’s get back to the partitions portion. I am going to make a modification to the Internet Sales partition for 2004 and break this out into Q1 and then place Q2 in a separate partition. This really doesn’t need to be done for the Adventure Works data since the volume of data is extremely small, but in a real world scenario this could definitely improve query performance (plus reduce processing time if you are just processing the latest quarter instead of the entire year).
I went ahead and modified the query of the existing 2004 partition so that the cutoff was less than 20040401 instead of 20041231 for OrderDatekey. You need to be careful that you do not overlap the ranges, because there is no validation going on, so you could potentially include data that is already in an existing partition. After I had modified the existing partition for 2004 and added the new partition I went ahead and updated the estimated counts.
Now that this is done let’s run a query against the cube and take a look at what is going on. Here is the query that I will execute against the cube: SELECT {Measures.[Internet Sales Amount], Measures.[Internet Order Quantity]} ON 0, NON EMPTY([Customer].[Customer Geography].[Country]* [Product].[Category].[Category]) ON 1FROM [Adventure Works]WHERE ([Date].[Calendar].[Month].&[2004]&[4])
So the query executed and if we look at the Profiler trace we can see that the query actually touched multiple partitions for the measure group that we were querying information from. The reason for this is because we have modified the basic partition from a single year. If we would have left it at the year level it would have been fine, but since we are dividing this up into multiple parts now it does not know where to retrieve the data to satisfy the query. Lets go back into the partitions in the cube and set the ‘Slice’ property for the partitions. This is where you specify the tuple that defines the partition.
Now that we have this setup we will redeploy and run the query again. You will need to define the ‘Slice’ property on each of the partitions in the measure group.
UPDATE (5/16/2009): fixed this picture (before I had highlighted the aggregation, not the partition) Now that we have defined the ‘Slice’ for the partitions we see that our query only touches the partition that we are querying against and it was faster in response time That is it for now, hope you enjoyed this little tip and I want to thank Siva Harinath and Howie Dickerman from Microsoft for their presentation at last year’s Microsoft BI Conference Designing High Performance Cubes in SQL Server 2008 Analysis Services where they pointed out this item. 5/14/2009 SQL Server 2008 Developer Toolkit feedbackJust wanted to post some feedback on the new SQL Server 2008 Developer Toolkit that was released yesterday on the Microsoft Downloads here – SQL Server 2008 Developer Training Kit. I saw the demo of this I believe last year at the Microsoft 2008 Launch event. This is a real cool demo that shows of the spatial data, filestream, and data mining capabilities in SQL Server 2008. It also incorporates Silverlight technology for the video player. Demo includes user login, profile information, security, mapping functionality with pinpoints for video references, Silverlight video player, and advertisements.
Ability to upload videos and specify a location on map to add pinpoint reference. The videos are uploaded and stored in the database with the filestream functionality available in SQL Server 2008.
My video is now included
In order to upload videos you need to install the Expression Encoder which is part of the required software detection setup (provides link to download and install during this process). You will also need to modify the web.config file to set the UploadVideoEnabled appsetting to “True” to enable this feature. The advertisement portion is setup to randomly pull a advertisement from the database. There is also a data mining exercise that uses some data from the Adventure Works database (sample Excel file provided) that you can go through to determine what demographic types would be interested in certain products. You could then feed this information back into the application to perform some more strategic advertisements to the users. The only thing about the exercise is that it stops at using the data mining add-ins that are available to download from the SQL Server Feature Pack. It leaves it up to you to figure out how to push this information back into the database and leverage this in the application. Example of data mining output where you can see that customers with some high school education and lower income levels tend to purchase helmets.
A couple of other things to point out would be that you will need to restore the SQL Server database provided as well as the Analysis Service database. The SQL Server database is located in the following location: C:\SQL2008TrainingKit\Demos\AdventureWorksRacingCommunity\setup \scripts\tasks\bak\AdventureWorksRacingCommunity.bak In order to do the data mining exercise you will need to have Analysis Services available and that is not part of SQL Express with the Advanced Services – this is only available in the Standard, Enterprise, or Developer Editions of SQL Server. There are more demos besides the web one that I show above, so be sure to check out everything to find out about the other cool features that you may or may not be using already if you are on SQL Server 2008 like groupingsets, merge, tablevalued parameters, etc.
So if you are interested in diving into these new features and want to walk through some demos of these new features download the SQL Server 2008 Developer Training Kit for yourself. BIDS Helper 1.4.1.0 availableJust getting caught up on some of my blog readings this morning and came across a posting by Darren Gosbell stating that they have released a new version of BIDS Helper - BIDS Helper release 1.4.1. The one cool thing is that they have included a version notification into the add-in so that you will be notified now when a new release is available. Plus there is a feature to synch the description information from a SQL Server relational table or view into your SSAS dimensions (only works with SQL Server and not with named queries in the DSV). They have also included smartdiff with Reporting Services now too Here is a list of the changes that Darren points out in his posting:
This release also incorporates the following bug fixes and enhancements:
If you are not using this yet, do not wait, this is an invaluable addition into the BIDS tool and I highly recommend this for any developer using SSAS, SSIS, or SSRS. Thanks again guys for putting this together. One other thing to point out is that the URL references for all CodePlex items has changed so that what used to be http://codeplex.com/bidshelper is now http://bidshelper.codeplex.com. The old URLs are still working now, but just wanted to point this out, because this might not always be the case. 5/12/2009 SQL Server 2008 R2 Information and New SiteWith TechEd 2009 currently going on the announcements are starting to come. We have Windows 7 and Windows Server 2008 R2 coming out later this year and hopefully in the next couple of months the CTP of SQL Server 2008 R2 (‘Kilimanjaro’ with ‘Gemini’ and ‘Madison’ – along with other features) – actually they stated that it will be available in H2 2009. Microsoft has created a new site with more features coming (like being able to register to receive notifications when the CTP is available) - SQL Server 2008 R2 site. If you check out the site you can see some of the new features coming with brief descriptions and nice screenshots (click on the screenshots to enlarge them). Features like improved central management for your multi SQL Server environment, Master Data Services (MDS – not MDM – Master Data Management – leveraging the Stratature acquisition), self-service analysis with the Gemini add-in, enhanced data model capabilities for self-service ad-hoc reporting (I believe this is for the relational and not OLAP, so interested what will be made available for OLAP and Analysis Services), new mapping capabilities with reporting, and much more. These are just a few of the items and a couple of screenshots here. Check out the official site for more information.
Gemini – high volume data analysis and ability to create data mash-ups to deploy to SharePoint (new data cleansing components available also that we are waiting to see – possibly brought in from Zoomix acquisition, but not sure if that is already going to be leveraged yet)
Monitor Analysis Services through SharePoint Administration
Mapping capabilities to be made available:)
Ability to create and share components in SSRS with a library of items (apparently an ability made possible with the 90 Degree acquisition – Review by Chris Webb here Radius90 from 90 Degree Software) Stay connected with what is going on at TechEd 2009 with the following: 5/11/2009 Large Dimension Processing issue - File system errorA couple of weeks back I experienced an issue with a some what large dimension. The dimension had around 15.5MM rows and it continued to error while processing the key attribute within the dimension in Analysis Services (SSAS) 2008. I don’t believe this is a version issue at all and I was able to come up with a solution that resolved the processing issue which I will explain. What was happening was that all of the other attribute hierarchies were processing fine, but every time the key attribute was being processed it would continue to error out in the same spot (around 60K rows) and it would return this error message (I swapped out a few names to protect the innocent): File system error: While attempting to read information from disk, a read error occurred for physical file: \\?\D:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Temp\MSMDCacheRowsetBadRows_1932_16629_jm313.tmp, logical file: . Errors in the OLAP storage engine: An error occurred while the 'DimensionName Key' attribute of the 'DimensionName' dimension from the 'SSAS_DB' database was being processed. This processing was going against an Oracle data store, not that I feel that the issue was related to that, but just a disclaimer. I started in by verifying the drive space for the Temp directory that gets utilized during the processing to ensure that enough space was allocated. I also review the design of the dimension to make sure that options like hierarchies are not optimized and not ordered were being set properly. I also verified the SSAS server properties to make sure that the EnternalCommandTimeout was adequate to make sure that the queries would not timeout during processing. Unfortunately none of this seemed to make a difference and I continued to get the same error message. I even verified the size on the asstore file for the dimension attribute thinking that maybe I was bumping up against the 4GB limit, but it was only around 355MB. After searching the web and forums I came across an item that seemed like a potential game winner - Using ProcessingGroup Dimension property option ByTable vs. ByAttribute may error with string keys. At this point I had nothing to lose and based on the hardware – Dell 2950s with 32GB of RAM and 2 quad core processors, plus we were running x64 software - I felt that this was a good solution. I went ahead and switched the ProcessingGroup property on the dimension from the default ByAttribute to ByTable. So basically a massive query gets executed and the results get cached which is then used to process all of the attribute hierarchies. In running this process not only did the process complete successfully, but it actually shaved about 20% of the time off for processing the dimension. You definitely need to read up on this process, make sure that you have the hardware in place, and do some proper testing.
Some of the other references that I looked at during this to fix the processing issue were the following:
As always you should refer to the whitepapers that have been provided on the SQL Server Best Practices site and a few key ones to point out are the following: 5/6/2009 PASSMN May 19, 2009 Monthly MeetingThe next PASSMN Minnesota SQL Server User Group meeting is coming up. The meeting is on May 19 from 3:00 PM to 5:15 PM. You can visit the local site to get registered at http://www.mnssug.org or click the link to Register Here. This month the topics are: SSIS – Team Development, Deployment and Configuration (Speaker: Dan English, Magenic) Dan English - Dan is a Principal Consultant with Magenic and has been developing with Microsoft technologies for over 12 years and has over 5 years experience with Data Warehousing and Business Intelligence. He has been working with SQL Server since version 6.5 and now with 2008 looking towards the Kilimanjaro release. Dan has screencasts of SQL Server 2008 and PerformancePoint Server on YouTube and Soapbox ( keyword search - Magenic) and is an avid blogger (http://denglishbi.spaces.live.com). Dan is fully certified with MS SQL Server 2005 and 2008 Business Intelligence. He enjoys keeping in contact with the community at large responding to forum postings on the Microsoft forums and SQL Server Central areas. Dan is also part of the PASSMN 2009 Executive Board.
Just a reminder that the group meets on the 3rd Tuesday of each month. If you are in the area and available to attend please join us and stop by and say ‘Hi’. 5/2/2009 PerformancePoint Server 2007 Hotfixes Available and Build ListSince the release of SP2 for PerformancePoint Server 2007 back in December there have been quite a few hotfixes released. The latest hotfix was just released this past week and will now bring the build number to 3.0.4315.00. This hotfix includes a fix for my posting last month PerformancePoint Dashboard Designer ‘Unable to connect to server’ with SSRS 2008. So the wait is over for anyone that has been patiently waiting to be able to use Report Center mode when using SSRS 2008 in Native mode in Dashboard Designer. Since the SP2 release there have been quite a few fixes along the way and the hotfixes are cumulative, so if you install the latest one you will get the other fixes that have been made along the way. Here is a list of some of the build numbers and links for the major releases and the latest hotfixes:
For a reference to the online documentation of component versions you can find that here - Monitoring Server and component versions. Take a look at this article too just for a refresher - Known issues and best practices in PerformancePoint Server 2007. I did run into a few issues after installing this latest hotfix, but it might have been because we had the custom SDK filter installed (PerformancePoint Custom Dashboard Filter Experience). We didn’t have issues when we installed SP2 or hotfix for 3.0.4311.00, but this one did cause some issues. After I installed it it stated that the following when we tried to run and download the new update for Dashboard Designer: the manifest may not be valid or the file could not be opened. + Manifest XML signature is not valid. + No signature was present in the subject. Luckily this wasn’t a major deal for us since I had to setup the custom filter again so we could get the ODBC filter setup again to use with an Oracle data source. I just went through the steps to resign the manifest and application and all was good. Also ran into a minor issue in one environment after the new version of Dashboard Designer was installed. I tried the refresh to get the list of items available on the server and to connect to the server, but it stated that it was unable to connect. For some reason the pmservice.asmx file in the web service directory got corrupted and was not correct. When I tried to access the URL of the web service it stated this error message on the server: The page must have a <%@ webservice class=”MyNamespace.MyClass” ….%> directive. Inside the file it showed this: <?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><GetKpis xmlns="http://www.microsoft.com/performancepoint/scorecards" /></soap:Body></soap:Envelope> and it should have had this: <%@ WebService Language="c#" Class="Microsoft.PerformancePoint.Scorecards.Server.PmService, Microsoft.PerformancePoint.Scorecards.Server,Version=3.0.0.0,Culture=neutral, PublicKeyToken=31BF3856AD364E35" %> I swapped it out and this resolved that issue. So if you are having issues with Dashboard Designer and access the SSRS 2008 reports in Native mode download the latest hotfix 4/30/2009 Windows Server 2008 and Vista SP2 coming soonIt appears that the SP2 should be available really soon now, maybe even today or tomorrow. I see that the release notes were just made available on the Microsoft downloads today. With the release of Office 2007 SP2 (and there is a SP2 for the Office Servers like Project and WSS) on Tuesday this week there was an updated launch page Windows Server 2008 Service Pack 2 and Windows Vista Service Pack 2 released. There is some good documentation available that you will want to check out. A couple of the things to note is that Windows Server 2008 shipped with SP1 and with SP2 Hyper-V will be available natively and you will not have to download this separately anymore. There is a blocker tool that you can download if you do not want this service pack too.
So get ready for more updates this week. By the way, this is not a cumulative update, but Windows Server 2008 shipped with SP1. If you have Vista you will need to install SP1 prior to installing this update. |
some of my favorite blogs
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
This application also tracks viewing history, allows for comments to be added to videos, and for you to designate favorite videos. You will also notice in the screenshot below that it performs calculation on the pinpoint using the geography information to determine the distance between two points.
|
|
