Cool MS Outlook addin – find out how email conversations translate to emotions and behaviours.
Email Behavior Analyzer
A dump of all things SQL Server, SQL Reporting Services, Analysis Services, around Business Intelligence, Business Performance Management and technology.
Cool MS Outlook addin – find out how email conversations translate to emotions and behaviours.
Email Behavior Analyzer
Great tips for optimizing SSIS source queries.
Luckily, T-SQL gives us a brilliant query hint to help avoid this.
OPTION (FAST 10000)
This hint means that it will choose a query which will optimise for the first 10,000 rows – the default SSIS buffer size. And the effect can be quite significant.
If you’re having issues with SSIS 2008 R2 logging in script tasks, it’s most likely because syntax has changed.
See this link on how to use ComponentMetadata.FireProgress instead of Dts.FireProgress.
---------------------------
Microsoft Visual Studio
---------------------------
The report definition failed to load: There is an error in XML document (5, 2).. Verify the report definition conforms to the correct schema.
---------------------------
OK Cancel
---------------------------
If you get this, and are using Subversion for your Reporting Services development, you may have a corrupt data source file. The xml involved has nothing to do with rptproj, it is actually an item within, in this case the data source.
XML and Visual Studio are my two least favourite things when dealing with bugs.
there is an error in xml document (5, 2) rptproj - Google Search
Teo outlines some of the pros and cons of the new BI edition of SQL 2012.
About $10k per server vs. over $50k for Enterprise Edition is the biggest plus. It will provide all the features for Analysis Services previously found only in Enterprise, and gives a fully functional PowerPivot, Analysis Services & Reporting Services server to the SME’s.
From what I understand, the key driver for Microsoft making this change would have been the requirement of installing 2 separate instances of SQL for using both PowerPivot and Analysis Services (multidimensional or tabular) data stores. For those clients wishing to install 2 separate environments for those, the cost will end up being about $20k anyway, not including Sharepoint and DW SQL instances.
One limitation is the 2-socket configuration limit. Another is the per-user licensing scheme, which could leave companies with 50-200 users in a grey zone. Enterprise or BI?
The licensing model for private clouds seems a bit more appealing from an ROI perspective. Invest in SQL Enterprise and deploy an unlimited # of VMs. The disadvantage is the need to license the entire server farm for SQL Enterprise + Software Assurance.
Business Intelligence Edition in SQL 2012 - Prologika (Teo Lachev's Weblog) - Prologika Forums
Microsoft also seems to be shutting out AMD with the new licensing scheme, and targeting Intel’s Q2 release of their new 2-socket chipset.
Now that MS has moved to a core licensing model + user vs CPU socket, will this change the R&D direction of CPU manufacturers? Will they now focus their efforts more on GPU processing? What if a PC only had 1 core & 1 socket, but multiple GPUs and something else besides a CPU to handle the more precise calculations?
Actually, it should be any O/S, if VMs are available… an interesting twist on cannibalizing their existing Windows Azure platform.
To date, Microsoft has been balking at customer requests to add persistent VMs to Azure, hoping to get customers to develop Azure apps from scratch instead. But the lack of the ability to host apps like SharePoint and other third-party business applications with persistence was a deal breaker for a number of business users who were unwilling to consider Azure until Microsoft added this support, one of my contacts said.
Microsoft to enable Linux on its Windows Azure cloud in 2012 | ZDNet
One area Reporting Services falls down a bit is in the parameter selection process for the built-in Report Manager and integrated modes. Usually this involves building custom parameter web parts or asp.net portals to get around. There are some workarounds for issues related to multi-value parameters and the infamous ‘Select All’ checkbox that may help if your database is getting clobbered.
Multi-Value (Select All) parameter in Reporting Services | Microsoft Connect
Is Reporting Services going the way of the dodo, to be replaced by Power View (Project Crescent?)
No.
Comparing Power View, Report Builder, and Report Designer
Power View won’t replace the existing Reporting Services reporting products.
Report Designer is a sophisticated design environment that developers and IT pros can use for embedded reporting in their applications. In Report Designer they can create operational reports, shared data sources, and shared datasets, and author report viewer controls.
In Report Builder, IT pros and power users can create powerful operational reports, and reusable report parts and shared datasets.
Report Builder and Report Designer create RDL reports; Power View creates RDLX reports. Power View cannot open RDL reports, and vice versa.
Both Report Designer and Report Builder are shipping in Microsoft SQL Server 2012 Release Candidate 0 (RC 0) Reporting Services, along with Power View. For more information, see Tools (SSRS).
SQL Server Reporting Services Team Blog - Site Home - MSDN Blogs
It is interesting that Microsoft is launching tools like PowerPivot and Power View, knowing that they will cannibalize their existing Analysis Services and Reporting Services products and roadmaps for future installations.
My question is, why don’t they just go all-in and build out these tools as replacements, instead of diverging paths? Why not incorporate all the best features of Power View into Report Builder, and leverage the RDL language that is designed for reporting?
Same goes for Analysis Services and PowerPivot. Why have two separate servers for each product? What is the value (other than licensing) of splitting up these technologies into 2 siloed environments? The sum of the parts is much better than the individual.
Still looking for some cohesion and sense of direction for the future of SQL Server reporting and BI tools… and hoping for a better 2012.
A deployment checklist for the new Denali / SQL 2012 tools
http://msdn.microsoft.com/en-us/library/hh231687(v=sql.110).aspx
The Swiss Army knife of Reporting Services tools. From two men who both aren’t from Switzerland. I’m not really sure what it doesn’t do.
SQL-RD is a Business Intelligence application for windows that saves time and money by making it easy to define single or packages of Microsoft® SQL Reporting Services reports, schedule and run them automatically, and send the reports to print, fax, disk, ftp, sms or email in a number of standard formats.
Day 23: Tips from Technoblab for Report Header/Footer, format codes, customizing the toolbar, working with images, etc
Lots of nice code examples here
A database field can neither be placed in a report header nor in the footer. This will throw an error "The Value expression for the textbox <fieldname> refers to a field. Fields cannot be used in page headers or footers".
There is however a workaround. Place a hidden textbox in the report body call it tb_hidden and assign to this the database field that you need in the header. Place a textbox in the report header and assign the hidden textbox's value to it
ex: =reportitems!tb_hidden.Value
This however as a drawback, the value will appear only in the first page. If your report runs into many pages then you need to take a different approach.
Pass the value to appear in the header as a parameter from your .aspx page call it database_field. Assign this to the textbox in the header.
ex: =Parameters!database_field.Value
Day 22: Auto-caching and logging of SQL RS parameters
The solution tracks the selected parameters from a report execution and uses them later for the next execution to take them as default parameters. This enables users to log parameters and inspect the execution statistics of reports as well as caching former execution parameters.
Excellent article with how to filter distinct values from a list using SSRS and Sharepoint.
Using SharePoint List data for Reporting Services (SSRS) parameter source « Dan English's BI Blog
Day 21 was probably part of Day 20 too. Still worth a mention. Code Generator for SQL RS reports
SSIS Task for SQL Server 2008 to create server reports or local reports from a recordset data source.
Day 20: An aggregator of all the SSIS components on Codeplex and elsewhere
Anything and everything you ever need to do with SSIS is somewhere on this page. Includes some Reporting Services functionality too.
This is a community project for developing additional tasks and components for SSIS, and identifying other CodePlex projects that do the same. 51 Integration Services extensibility projects exist on CodePlex - find them and over a hundred more from around the 'net right here.
Day 17, 18, and 19: Whatever you want to do with SQL is somewhere in these samples…
Microsoft SQL Server Community Projects & Samples
Day 16: One of the more useful tools out there – integration of Reporting Services with SSMS for exporting table data to a report!
Because the import/export wizard sucks.
SQLReporter enable you exporting table or view to Reporting Services Report , Then , you can export it to Excel & PDF. Quick & nice.
SQL Injection attacks are probably the most common attacks on internet-facing SQL Servers due to their ease of use. This tool helps expose vulnerabilities to end-users and non-IT executives, visually.
The reasoning is that most existing SQL Injection testing tools are executed from a command line and “lack an intuitive user interface”. In other words, if you can’t show the problem in a pretty web page, people won’t really believe it exists.
Day 15: Leech for Reporting Services
SSRS downloader is a tool to download all report files from a folder from Reporting Services at once. It uses reportservice2005.asmx endpoint web service to do this.
Day 15: Amazon SimpleDB query extension for your cloud computing needs
If you’d like to tell your manager you’re running reports “off the cloud”, here’s one way to do it.
http://aws.amazon.com/simpledb/
This project is a SQL Server Reporting Services 2008 data processing extension that facilitates querying of Amazon's SimpleDB.
Day 14: A Powershell provider for SSRS
Doesn’t look too current but probably still works…
A PowerShell provider for SSRS (SQL Server Reporting Services). The provider allows for navigation of SSRS installations, management of reports, etc, all through a scriptable interface.
Day 13: Self-service documentation
These are reports for MS SQL Management Studio and MS SQL Reporting Services for creation of database documentation with the use of descriptions from Extended Properties.
Reports contain the detailed information on tables, views and stored procedures etc. Use of reports engine allows exporting generated reports into Excel, Word, PDF etc. formats easily.
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: