Skipping the weekend again.
Day 12: The unfortunately-named sperm report project, blowing out data from OLAP cubes with “Spermline visualization”
Essentially with these four reports you can create 400 user reports (actually as many as you like) Change what is displayed on rows, columns, filter or time by simply creating a new "linked report" and setting a new set of paramaters.
Day 9: An automated build tool for SQL RS
RSBuild is a deployment tool for SQL Server Reporting Service. It currently supports two types of tasks: executing SQL Server scripts and publishing SQL Server Reporting Service reports and shared data sources. From version 1.1.0 onwards SQL Server 2008 Reporting Services is supported as well as all previously supported versions of SQL Server Reporting Services.
Day 9: Auditing in SQL from the SQLCat team (and Denny Lee)
This project is a place for the SQL CAT team to share their expertise with the community and collaborate with other community members outside Microsoft who wish to contribute code samples to enable more productive use of the SQL Server platform for high-scale,enterprise customers
Day 8: RSToolKit, a tool for creating, moving, documenting, and exporting reports.
This could be very useful in a distributed, farm or DRP environment.
RSToolKit allows users to administrate different SQL Server Reporting Services with one application and has different methods to deploy reports. It supports all SQL Server Reporting Services versions which are using the ReportService2005 and the ReportExecution2005 SOAP API
Day 7: A framework for navigating an Analysis Services cube from SSRS
Basically the user-friendliness of the interface is achieved by allowing users to drill up/down on the different hierarchies by simply clicking columns or rows, and applying filters by clicking on icons in rows or columns.
The performance is achieved by only retrieving the necessary data from the cube.
Day 6 – A debugger for Reporting Services
Wouldn't it be nice to be able to peek under the hood and see what APIs Report Manager or a custom application calls? This is exactly what the Reporting Services Tracer (RsTracer) sample was designed to handle. RsTracer intercepts the server calls and outputs them to a trace listener, such as the Microsoft DebugView for Windows. RsTracer helps you see the APIs that a Reporting Services client invokes and what arguments it passes to each interface. It also intercepts the server response. Armed with this information, you can easily reproduce the same feature in your custom management application.
And a utility mentioned above from my favourite Windows utility guy Mark Russinovich
Day 5 (sorry I skipped the weekend) – Report Generator for Integration Services ETL
As the name says it, this "Control Flow" custom component can be used render your SQL Server Reporting Services in files withe the following formats: PDF, Word, Excel, HTML 4.0, MHTML, CSV, XML
Day 2 – SSRS Buddy
A quick fix to upload multiple report definitions to Reporting Services.
SSRSBuddy enables deployment of multiple reports and report models onto a SSRS2005 instance, using shared datasources
Here’s to a start of 25 days of Reporting Services tools from Codeplex
Day 1 – The Metadata Toolkit
SQL Server Metadata Toolkit
MSDN's SQL 2005 tool kit updated to 2008 for managing metadata in SQL Server Integration Services, Analysis Services and Reporting Services using built-in features including data lineage, business and technical metadata and impact analysis.
A complement to the upgrade adviser and best practices analyzer.
Microsoft Gold Certified Partner, Scalability Experts, has announced availability of its Upgrade Assistant for SQL Server 2012 (UAFS) tool. As reported on ASP Free, with the UAFS tool, users can automate the process of application compatibility testing to determine any issues that may arise from upgrading to SQL Server 2012 from SQL Server 2008.
SQL Server 2012 RC0 was put out a couple of weeks ago. It includes a tool called PowerPivot, which Microsoft is touting as the replacement (ahem, augmentation) for Analysis Services cubes.
(The) shiny features of PowerPivot V2, which are:
- Hierarchies
- KPIs
- Security
- Perspectives
- Sort-by-Column
- Measure-definition through PowerPivot window
- Data view
- New DAX-functions
SQL Server 2012 RC0 and PowerPivot V2 RC0 - Blogs
PowerPivot removes some of the complexity and design decisions from a developer and places them in the hands of an end-user. Pulling in data from various cloud, relational (and cube) sources, the PowerPivot add-in for Excel 2010 (now 2012?) builds an Analysis Services cube in the background. The cube is stored in-memory (or perhaps paged to disk, but still in-memory technically), until it is saved to disk as an XLSX zip file with an ABF Analysis Services Vertipaq-style backup inside.
Before Analysis Services, Pivot tables were something mainly power Excel users dealt with. Analysis Services started bringing Pivot tables to the foreground, with all of their limitations. Corporate users looked to 3rd-party tools for cube browsing, until IT budgets were cut and Excel became “good enough”.
As users needed more data in their pivot tables, they had to find the 1 person in the company with knowledge of these “cubes”, and bring with them budget to change the corporate data structure. Now they need to find one of the 10 users in their company who are power Excel gurus and know about this thing called Powerpivot.
It will be interesting to see what happens when PowerPivot is truly embedded in Excel, without the add-in feel.
Sharepoint 2010 is a complex beast of an application, with many facets and configuration options. Taking the exams should be a prerequisite to any upgrade or deployment.
These notes are the best I have seen so far to help prepare for the configuration exam.
How I passed SharePoint 2010 exam 70-667 (Part 1 of 4) - Tales from a SharePoint farm
A useful tool to generate reports based on Powershell outputs.
Plug this into SQL Server Reporting Services to create reporting based on your PowerShell scripts. The PowerShell DPE transforms your PowerShell output into a DataSet that can be consumed by SSRS.
PowerShell Data Processing Extension for SQL Server Reporting Services
Need to run a query or update multiple times, or even a sql job?
EXEC sp_start_job ‘JOB_NAME’
GO 100
DOS scripts at their best. DOS better be in Windows 8…
Here is an interesting one. I found a way to take the %date% environment variable, and turn it into a valid string for a filename – without any extra programs or scripts.
For the longest time I used a little utility I created to do this. The problem with that is the utility needs to be around if you want to send the batch file to someone.
What I didn’t know that was that you can use this character combination ‘:~’ to pull a substring out of an environment variable. That is when I realized you could use this to pull out parts of the current date (or time).
Here is how it works. Lets take the %date% variable and print it out
Create a date and time stamp in your batch files | Remote Administration For Windows
Here’s an example including time that backs up a projects directory to the C: drive under a Backup timestamped directory.
@echo off
set YYYYMMDD=%date:~-4,4%%date:~-10,2%%date:~-7,2%
set HHMMSS=%time:~-11,2%%time:~-8,2%%time:~-5,2%
echo %HHMMSS%
c:
md \Backup_%YYYYMMDD%%HHMMSS%
echo Backing Up files to \Backup_%YYYYMMDD%%HHMMSS%\
xcopy /s C:\Projects\*.* \Backup_%YYYYMMDD%%HHMMSS%\
echo Files backed up to \Backup_%YYYYMMDD%%HHMMSS%\
Goto Quit
:Quit
Microsoft has just announced SQL Server 2012 Editions information on official SQL Server 2012 site.
SQL Server 2012 will be available in three main editions:
- Enterprise
- Business Intelligence
- Standard
The other editions are Web, Developer and Express.
Here is the salient features of each of the edition:
Enterprise
- Advanced high availability with AlwaysOn
- High performance data warehousing with ColumnStore
- Maximum virtualization (with Software Assurance)
- Inclusive of Business Intelligence edition’s capabilities
Business Intelligence
- Rapid data discovery with Power View
- Corporate and scalable reporting and analytics
- Data Quality Services and Master Data Services
- Inclusive of the Standard edition’s capabilities
Standard
- Standard continues to offer basic database, reporting and analytics capabilities
There is comparison chart of various other aspect of the above editions. Please refer here.
Additionally SQL Server 2012 licensing is also explained here.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
One way to copy database diagrams between databases.
INSERT INTO dbB.dbo.sysdiagrams
SELECT[name],[principal_id],[version],[definition]
FROM dbA.dbo.sysdiagrams
Vizubi is a Microsoft Powerpivot-like add-in for Excel 2003, 2007, and 2010. It’s greatest feature is, well, compatibility. PowerPivot only works with Excel 2010, and the best version of PowerPivot isn’t even released. PowerPivot will be targeted towards new Office and Sharepoint adoption vs. supporting legacy platforms. This probably means that the majority of customers and partners will have at least a 2-3 year sales cycle ahead of them.
Vizubi’s in beta now.
Other than that, it has a laundry-list of interesting features on the site. It seems to play in a similar space to QlikView and other in-memory column-oriented databases. It even supports the QlikView file format.
From Syntes, a team that brought features to Cognos, Business Objects, Board, and QlikView. It’s primary products before this were NPrinting and NScheduler, plugins for Qlikview.
Denny Lee is a BI advisor at Microsoft. He goes through the joys of configuring security, Kerberos and delegation around Sharepoint and Powerpivot.
There are many blogs on this subject, which to me indicates a failing in Microsoft’s security deployments, and the overcomplexity of Sharepoint when it comes to the Windows security model. Users accessing PowerPivot within Sharepoint may notice that they don’t have access, when the same level of accounts for other users do.
This scenario works out well when a VP can’t get into their director’s PowerPivot workbooks.
Sometimes the issue comes down to plumbing within the Active Directory infrastructure, where years of upgrades have caused legacy issues to creep up. New users will be assigned default security permissions where migrated users may not have these permissions.
Following the lifetime of a security token isn’t my idea of fun, but it is definitely a challenge that will keep security consultants employed for the next few years…
Delegation, Claims, Active Directory…Oh My!…Aw Crap! « Denny Lee
This is one of my favourite Excel add-ins when dealing with Analysis Services cubes. It can pull the MDX out of a pivot table, search cubes, add in calculated measures on the fly, and allow users to share measures.
The latest releases fix some bugs and provide a very useful feature of clearing a pivot table’s cache. The pivot table cache, a monstrosity of XML code, is known to blow up many an Excel workbook.
Show properties as a caption allows captions to be exposed as real members in the workbook.
Worth downloading if you use OLAP cubes regularly.
GO 365! Now that’s a good way to load test your stored procs! 
Five ways to generate Date Tally Table:
A number of SQL Azure (Cloud SQL) videos available at Codeplex.
Hosted at Microsoft Showplace.
Did he say 512 GB of RAM? Yes, yes he did.
Recently we conducted a Performance Proof of Concept for a large customer using the new 8 Intel Nehalem-EX E7540 8-core processor HP DL980 G7 server. This blog discusses some of the configurations and tuning conducted during the PoC. One HP DL980 with 512GB of RAM was used for SQL Server and 9 x 2 Intel Nehalem-EP 5670 processor were used as application servers.
Two Microsoft Access databases and an Excel workbook make up Contoso’s sample dataset for PowerPivot available at the link below.
PowerPivot Sample Data and Resources - Download: PowerPivot for Excel Tutorial Sample Data-v.2
Project Description
Project show how to prepare MS Dynamics NAV data for analyzing in PowerPivot for Excel. Project include Data Warehouse demo database, sql procedure to transfer data from Navision to DW and Excel example.
Happy BI for NAV.
Apple has tried to limit the use of cross-compiler technologies to allow developers a develop-once, deploy-all solution. They want Cocoa and Objective-C to be the platform for mobile computing.
Unfortunately developers usually go towards the path of least-resistance, and even though HTML5 is just another dog with the same fleas, it is becoming the platform of choice.
By adopting HTML5, it opens the market up for Android and, to a lesser extent, Windows Mobile. Not to mention any PC or Mac with an HTML5 web browser. Though those HTML5 features again vary depending on the browser and platform.
Did you know Google Chrome can run C++ apps natively inside the browser? Google Chrome is it’s own HTML5-based operating system.
The catch with Windows Mobile and the new Windows 8 Metro is that HTML5 has always been a lesser player at Microsoft. They tried to block the path of least resistance with Microsoft Silverlight. Developers and companies were eventually adopting that standard over Flash and Flex, until MS proposed HTML5 and *cough* javascript as a first-generation language.
Even with Metro, HTML5 is still treated as a proprietary Microsoft thing. If you want to ensure that your code is not “view-sourced”, your main alternative to straight HTML5 and JS is to work with a WinRT component.
The walls of the fortress just look a bit different in Redmond than they do in Cupertino.
I wonder if it was Microsoft Research that found HTML5 adoption might hurt Apple’s profits.
HTML5 Adoption Might Hurt Apple's Profit, Research Finds | PCWorld Business Center
Running rings around your network cards.
In the end, we continued to use our “workaround” of scaling network load out to 4 NIC cards, which gave us enough network bandwidth as well as RSS CPUs to handle the heavy network traffic. You can certainly use more powerful 10Gbps NIC, but remember to configure “RSS rings” to a proper value.
One of the best tools for finding what exactly is consuming memory in Windows Vista or higher.
Have you ever wondered exactly how Windows is assigning physical memory, how much file data is cached in RAM, or how much RAM is used by the kernel and device drivers? RAMMap makes answering those questions easy. RAMMap is an advanced physical memory usage analysis utility for Windows Vista and higher.
When you start using Analysis Services with larger dimensions and cubes, you may notice that your SQL Server is performing poorly if it’s on the same server. By default, Analysis Services, SQL Server, Integration Services and Reporting Services (and perhaps even Full Text Search) are all fighting for valuable memory. Setting the caps on each of those services, and ensuring that other services aren’t chewing up memory is important for a well-performing SQL Server.
Greg Galloway points out a great tool above from Sysinternals called RAMMAP. Looks like a great utility for finding out just what is gnawing at your memory, and whether there’s spyware hijacking your system.
I can’t count the number of times I’ve seen a new client’s server in the following state. The server has SQL and SSAS on the same box. Both are set to the default memory limits (which is no memory cap for SQL and 80% of server memory for SSAS). By the time I see the server, SQL and SSAS are fighting each other for memory, causing each other to page out, and performance degrades dramatically. (Remember what I said about disk being a million times slower than RAM?)
What’s further confusing with SQL configuration, is SQL uses “KB” as the default max memory setting, while Analysis Services has just “80” which means use up to 80% of physical RAM. Typing in a number > 100 will allocate an absolute value of the “Bytes” of RAM Analysis Services will use. So much for consistency.
Don’t forget to clear out the event viewer if you have a mysterious “services.exe” eating up memory. I have seen a large security log take up 700MB of RAM.
SQL # is a set of CLR functions that perform some interesting tasks not easily available from SQL. Things like getting twitter feeds, getting web pages, or working with the filesystem.
Yep, I can tweet from SQL Server thanks to SQL#. In fact, I sent that tweet out.
I can also pick up my twitter stream
This article outlines a method of documenting cubes with some stored procedures and Reporting Services reports. The only flaw, which is more on the SQL side, is the lack of a way to dynamically specify the linked server name, without getting into dynamic sql.
This could be useful for managing change in cubes, and providing end-user or technical documentation.
Being a business intelligence consultant, I like to spend my time designing data warehouses, ETL scripts and OLAP cubes. An unfortunate consequence of this is having to write the documentation that goes with the fun techy work. So it got me thnking, is there a slightly more fun techy way of automating the documentation of OLAP cubes…
There are some good tools out there such as BI Documenter, but I wanted a way of having more control over the output, and also automating it further so that you don’t have to run an overnight build of the documentation.
I found a great article by Vincent Rainardi describing some DMVs (Dynamic Management Views) available in SQL 2008 which got me thinking, why not just build a number of SSRS reports calling these DMVs, which would then dynamically create the cube structure documentation in real time whenever the report rendered..
This post is the first in a 3 part set which will demonstrate how you can use these DMVs to automate the SSAS cube documentation and user guide.
Automating SSAS cube docs using SSRS, DMVs and spatial data | Purple Frog Systems
When dealing with multiple Measure Groups in a cube, you could have items repeating if they are at a higher level than the detailed rows. Here are a couple articles and MSDN info identifying best practices around this.
All but the simplest data warehouses will contain multiple fact tables, and Analysis Services allows you to build a single cube on top of multiple fact tables through the creation of multiple measure groups. These measure groups can contain different dimensions and be at different granularities, but so long as you model your cube correctly, your users will be able to use measures from each of these measure groups in their queries easily and without worrying about the underlying complexity.
http://www.packtpub.com/article/measures-and-measure-groups-microsoft-analysis-services-part2
Users will want to dimension fact data at different granularity or specificity for different purposes. For example, sales data for reseller or internet sales may be recorded for each day, whereas sales quota information may only exist at the month or quarter level. In these scenarios, users will want a time dimension with a different grain or level of detail for each of these different fact tables. While you could define a new database dimension as a time dimension with this different grain, there is an easier way with Analysis Services.
Moving data around has never been so easy.
The fact that we can take output from DELETE and feed it to INSERT actually models what we are trying to do perfectly. And, we get some advantages:
- This is now a single, atomic statement on its own.
- The logic about which rows to move is specified only once, which is neater.
- The logic about which rows to move is only processed one time by the SQL Server engine.
By default, SQL 2008 sets the dimension error handling property to Custom, which breaks any dimensions that have duplicate attributes. For instance, if you have a state or province column in your dimension and it’s not unique, it will “report and stop” when handling the error.
The fix is to select “report and continue”, or set “default” for your error handling, or setup a composite key that makes the attribute unique, or configure relationships that make it unique.
There are some other things that SSAS does behind the scenes. This article provides some more info.
The reason is that when processing the dimension, SSAS by default does a right trim and this eliminates not only the spaces, but also any of the three special characters (tab, line feed and carriage return) we added! Note how this differs from T-SQL where these characters are not impacted by RTRIM as can be seen here:
If the number of blog comments signifies how important a subject is, this blog post takes the cake.
The built-in time intelligence features (YTD, Y/Y, etc) of Analysis Services don’t work very well.
Enter DateTool dimension: an alternative Time Intelligence implementation
SQLBI - Marco Russo : DateTool dimension: an alternative Time Intelligence implementation
An oldie but goodie, some MDX examples and best practices around building BI data warehouses. Some content may not be relevant with SQL R2 but some stands the test of time.
Best Practices for Business Intelligence Using the Microsoft Data Warehousing Framework
In SQL Server Denali, Reporting Services leverages the SharePoint service application infrastructure and it doesn't require installing a Reporting Services server. Not only this simplifies setup but improves performance because there is no round-tripping between SharePoint and report server anymore. Configuring Reporting Services for SharePoint integration mode is a simple process that requires the following steps:
1. Get your spatial data ready. If you’re just starting to work with spatial data and don’t have a handy .shp file, you can visit some of these sites which provide publicly available shapefiles:
http://www.vdstech.com/map_data.htm
http://wwwn.cdc.gov/epiinfo/script/shapefiles.aspx
http://www.mapcruzin.com/download-free-arcgis-shapefiles.htm
http://www.census.gov/geo/www/cob/bdy_files.html
2. Download the SQL Server Spatial Tools from ArcGIS, which include the Shape2SQL tool.
I think it’s Best Practice but whatever, looks interesting…
Analysis Services Best Practise Analyser (SqlAsBpa for short) is a tool which checks your live Microsoft Sql Server Analysis Services 2005 against some important best practises, and reports items which violate these best practises.
Resmon exposes the Analysis Services 2008 DMVs as a cube, which could help expose performance problems and find resolution.
With Analysis Services 2008 and later, data from dynamic management views can be retrieved with SQL query syntax as described here. Therefore, it is possible to build an Analysis Services cube which uses Analysis Services DMV SQL queries as the data source.
The ResMon cube rolls up information about Analysis Services such as memory usage by object, perfmon counters, aggregation hits/misses, and current session stats.
On Tuesday this week we published a whitepaper that goes into great depth about using SQL Server with Dynamic Memory. You can download it from here: http://msdn.microsoft.com/en-us/library/hh372970.aspx
Windows has had a recycle bin since Version 3.1, perhaps even earlier. Even DOS had an undelete command. How come SQL requires you to restore from backup, assuming a backup even exists?
The Oracle feature, Flashback, just sold me on using Oracle for a solution requiring high availability and uptime, provided the budget is there... Not sure what a red query has anything to do with it though.
Sometimes it is a rouge query, sometimes a simple data clean up effort by the users, whatever may the cause be, inadvertent data-loss is a very common phenomenon. Backup and recovery capabilities are provided by the database management systems which ensure the safety and protection of valuable enterprise data in case of data loss however, not all data-loss situations call for a complete and tedious recovery exercise from the backup. Oracle introduced flashback features in Oracle 9i and 10g to address simple data recovery needs.
Spatial data in SQL 2008 R2 allows you to create freeform diagrams in your Reporting Services Reports. Some interesting possibilities here with data-driven diagrams. For instance, what stores on a mall map generate the most traffic? What part of an automobile has the most frequent damage replacements? What part of the body is most affected by a lab test?
The ability to flag these shapes with red/yellow/green traffic lighting makes it a cool proposition for some deep visual reporting.
My session is about using SSRS, SQL spatial data and DMVs to visualise SSAS OLAP cube structures and generate real-time automated cube documentation (blog post here if you want to know more…).
This shows an unusual use for spatial data, drawing diagrams instead of the usual demonstrations which are pretty much always displaying sales by region on a map etc. Whilst writing my demos, it got me thinking – why not use spatial data to draw even more complex pictures, diagrams or logos…
Drawing a logo or diagram using SQL spatial data | Purple Frog Systems
