WITH MEMBER Measures.UserID
as
'UserName()'
SELECT
[Measures].[UserID]
ON COLUMNS
FROM
Filtering by logged in user
A dump of all things SQL Server, SQL Reporting Services, Analysis Services, around Business Intelligence, Business Performance Management and technology.
To configure Reporting Services against an existing Sharepoint 2010 farm, you need to go to the root site and enable the proper features. Then configure libraries in subsites to expose the proper content types (Report Builder Model, Report, Data Source).
It can get a bit tricky. Here are some links.
Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode)
If you’re trying to restore the Contoso database samples on a BI or Standard edition of SQL 2012, you’ll get an error about Enterprise features being used. Here is a script that can be run on a trial Enterprise or Developer edition of SQL 2012 to remove that dependency.
ALTER INDEX ALL ON DimChannel REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON DimEntity REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON DimProduct REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON DimStore REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON FactExchangeRate REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON FactInventory REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON FactITMachine REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON FactOnlineSales REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON FactSales REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON FactSalesQuota REBUILD WITH (DATA_COMPRESSION = None);
ALTER INDEX ALL ON FactStrategyPlan REBUILD WITH (DATA_COMPRESSION = None);
SQL Error 909 when restoring ContosoRetailDW.bak using MS SQL Server Mgt. Studio
a .NET application that provides realtime performance data on the target SQL Server instance.
No installation required, data displayed in realtime, and can also be logged to CSV for offline analysis. Will also capture SQL data for processing uing PAL.
Layerscape is a fascinating Microsoft Research tool that integrates with Worldwide Telescope to provide deep spatial virtualizations.
Useful for distributing workloads and speeding up SSIS performance.
Microsoft® SSIS Balanced Data Distributor (BDD) is a new SSIS transform. This transform takes a single input and distributes the incoming rows to one or more outputs uniformly via multithreading. The transform takes one pipeline buffer worth of rows at a time and moves it to the next output in a round robin fashion. It’s balanced and synchronous so if one of the downstream transforms or destinations is slower than the others, the rest of the pipeline will stall so this transform works best if all of the outputs have identical transforms and destinations. The intention of BDD is to improve performance via multi-threading. Several characteristics of the scenarios BDD applies to: 1) the destinations would be uniform, or at least be of the same type. 2) the input is faster than the output, for example, reading from flat file to OleDB.
NOTE: An updated version of Microsoft SSIS Balanced Data Distributor (BDD) is available. This includes a fix for the problem causing BIDS to crash when removing the BDD transform. For more details, and download information, see KB 2616527.
Download: Microsoft® SSIS Balanced Data Distributor - Microsoft Download Center - Download Details
Instead of using CHECKSUM, timestamp, or other methods, hashing data may be a good alternative.
A common scenario in data warehousing applications is knowing what source system records to update, what data needs to be loaded and which data rows can be skipped as nothing has changed since they were last loaded. Another possible scenario is the need to facilitate searching data that is encrypted using cell level encryption or storing application passwords inside the database.
Data Hashing can be used to solve this problem in SQL Server.
Some additional training content for SQL 2012, from John Weston’s site
MSDN Virtual Lab: SQL Server 2012: Working with the SQL Server 2012 Availability Groups
MSDN Virtual Lab Express: SQL Server 2012: Exploring SQL Server 2012 Reporting Services PowerView
Don’t forget SQL 2012 just RTM’ed get more info here: http://blogs.technet.com/b/jweston/archive/2012/04/02/sql-2012-rtm-available-for-technet-customers-to-download-today.aspx
Words are powerful things. As of late, I have been working on text mining social data feeds, and investigating how Hadoop, R, Azure and SQL Server 2012 fit into the big picture. I wrote a SQL Integration Services package using a script task that pulls Twitter data from a syndication feed, parses out the words, hash tags, screen names and links, and stores the results in a SQL 2012 (or optionally SQL Azure) database. Performing some text mining of Twitter status keywords against a selection of “best” and “worst” words in the English language brings back some interesting and slightly depressing results.
I started by entering in the database the twitter screen names to follow. I targeted a few Canadian bike companies (don’t ask why). Each time the package is run, it adds the last 20 tweets, and parses screen names of users that are mentioned in each tweet. And so on. This recursion builds a very quick six-degrees-of-separation trail and some fairly random discussions.
Running this process for about 4 days, sometimes 2-3 times per day, produced 5599 tweets. Originally I was looking at using R and Hadoop to analyze the results, which is a bit like bringing a ballistic missile to a knife fight. To slice this data with SQL takes only a couple of seconds or less. Perhaps reading the entire Twitter firehose or analyzing historic tweet data might change the architecture in the future. For now, things are working pretty well.
Of a selection of 5599 individual tweets, 9 contain the “best words” and 2135 have the “worst words” as rated by Vocabula Review. That’s 38% of the sample that have an aura of foolishness or odium, and 0.1% that have an aura of fun and majesty. The sampling is fairly small, with the top word “valley” only coming up 3 times.
Another dataset with seeded with a more technology-centric list of twitter users like Robert Scoble some Microsoft folks I follow brought back similar results. Running this process over the course of a month saved 59,583 tweets containing 796,171 words, links, screen names, emoticons and hash tags.
Of the 796k words, 24,171 came up in the “worst words” filter. That’s about 30%. A measly 282 came up in the “best words” filter. That’s less than 0.001%.
The following Top 5 Words came up.
valley 74
azure 19
simplicity 11
bliss 10
recherche 8
With only 140 characters to leverage, you would think that people would use words like “animadversion” or “cachinnation” to provide deep and meaningful expression. Instead, you get the logorrhea that is the Twitter dataset.
Check out www.vocabula.com to improve your tweets and amaze your followers with fun and majesty.
Excellent list of must-have software for SQL Server and everything else.
My must-have software is:
A wizard for installing SQL? Yes, it exists…
FineBuild provides 1-click install and best-practice configuration of SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005.
CQRS is a design pattern acronym which stands for Command Query and Responsibility Segregation. I would call this the R/RW pattern, or the separation of Church and State. In a reporting application design, it would silo the read queries from the write commands.
In its simplicity, it is designing two services for each application interface, one that reads and one that writes.
That is it. That is the entirety of the CQRS pattern.
CQRS, Task Based UIs, Event Sourcing agh! | Greg Young
Since the bulk of applications have highly concurrent reads and minimal or bulk writes, thinking about this pattern during design is very important. What if you were able to architect a system where you could spin up unlimited services that provide querying, and a few services that provide writes/updates, queuing them up for later? Would this improve performance and scalability? What if you could split the data for these systems into unlimited read-only models and a few read/write models?
What if the data in the read-only models was effortlessly in-synch with the read/write models, with minimal latency and lazy consistency?
This is one of the tenets behind Azure and cloud computing in general.
You can do this in a SQL Server environment, albeit not so effortlessly and with some gotchas, using Log Shipping, Undo and database snapshots for a read-only picture of the database. There is some latency, and keeping the databases in synch adds lots of overhead.
SQL 2012 appears to solve some of the limitations of SQL 2008 R2 log shipping with it’s AlwaysOn capabilities for rolling out read-only, distributed clusters. It still doesn’t seem as effortless as it should be.
Replication also offers a solution, though it can be brittle and has its limitations and maintenance overhead.
SANs also offer a high-cost solution for fast bulk loads, with some downtime.
You cannot use SAN snapshots of an active SQL Server database file(s) unless you detach them first. This is very intrusive.
I actually do this for a situation where I need 800GB of data from one server to another every night.
It requires an outage on both, but is very fast to virtually move the data to the other server.
Change data capture is another solution for maintaining multiple concurrent copies of a data stream. SQL 2012 now has an Oracle CDC feature which improves the integration and reporting story.
The above solutions are very hammer-based approaches to the solution and don’t necessarily relate to CQRS. Message queuing, eventing, change data capture and pub/sub systems all seem to fall within the CQRS design pattern, though they are really just an extension to the thought process.
Chad Meyers has an excellent posting on Laws, Rules, Principles, Patterns, and Practices.
Another set of excellent posts on the concept of CQRS.
There doesn’t seem to be a silver bullet to the solution just yet, though I’m sure someone will get it right sooner or later. The CQRS pattern is merely that, a pattern. The devil is in the details.
Upgrading from Denali CTP1? Your keyboard shortcuts (CTRL-E, CTRL-R) are probably not working. Use Options – Keyboard – Reset to fix.
Ctrl+E not working for executing statements | Microsoft Connect
One of the things you may want to do as soon as you install SQL 2012 is disable the auto refresh feature of SQL Agent, which will fill up your agent logs with superfluous messages.
We investigated this issue and it's indeed painful behavior from the user perspectives, we will address this issue in nearest Service Pack.
For now I can suggest you the the following workaround:
Go to registry on your SQL Server machine and navigate to corresponding SQL Instance registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ MSSQL11.YOUR_INSTANCE_NAME\SQLServerAgent
Change registry settings called “AutoRegistryRefresh” from 1 to 0.
The side impact of this: if you change any default settings behavior for SQL Agent, you may need to restart SQL Agent to get it.
Reloading Agent Settings appears in SQL Agent Logs every 90 seconds | Microsoft Connect
How to configure additional output formats (HTML) for Reporting Services.
There was a question on Twitter about how to display a report in HTML instead of MHTML due to some browser issues. Based on the fact that it was MHTML, I’m assuming we are talking about exporting a report as the default report view is HTML. First off, if we look at our export options for a report, we see the following:
Exporting via HTML instead of MHTML - CSS SQL Server Engineers - Site Home - MSDN Blogs
The distributed replay feature of SQL 2012 allows for load testing a sampled workload against a different SQL server, and spinning up multiple workloads to simulate real activity.
Here’s the install instructions.
Jonathan Kehayias | Installing and Configuring SQL Server 2012 Distributed Replay
The MAK talks about command line utilities in SQL 2012.
Microsoft provided many Command line utilities for SQL Server 2012. In this article I am going to explain the functionality and use of these command line utilities based on the feature it is dealing with.
SQL Server 2012 Command Line Utilities — DatabaseJournal.com
Also, the details on PowerShell cmdlets for SQL
Beware the new fetch feature of SQL 2012. RBAR in disguise?
Although SQL Server 2012 new Paging function is intuitive for the purpose of paging a data set, it comes at a cost for large volume of data. The Top Batching and Direct ID Batching perform significantly better under similar circumstances, i.e. simple Source Data with continuous ID.
Paging Function Performance in SQL Server 2012 | Ms SQL Girl
There is also another bug around identity columns skipping increments after a SQL restart that might affect how paging functions in SQL 2012. Be sure to test any applications doing server-side paging!
Log Parser Studio is a utility that allows you to search through and create reports from your IIS, Event, EXADB and others types of logs. It builds on top of Log Parser 2.2 and has a full user interface for easy creation and management of related SQL queries.
Data Warehousing and Business Intelligence
Vincent Rainardi presents an excellent resource for a Business Intelligence project, with plenty of tips and tricks to succeed when building a data warehouse or reporting solution.
Questioning using SQL 2012 as a production environment? There are a few critical issues with the current platform that may make you want to think about when SP1 is coming out….
This one is critical for those that think identity should be an auto-incrementing sequential key.
Just to add to your list. There is one more critical bug wherein if you define identity column on a table and insert some values. Thereafter restart SQL server and insert further more values and do a restart again and then when u insert in that table there are large gaps in the identity values.
An interesting technique for building a VSDB (Very Small Database) with a very large query plan profile. If you have a huge database and don’t want to do performance testing of procedures in production, mirroring statistics could be a way to simulate this activity using tiny or even empty tables.
I recently read an excellent post by Kevin Kline regarding scripting out histograms from one database and applying them to another. What would have made it better is if there was a way to automate it. And so I pulled out powershell and got to work.
This is Sparta...n SQL!: Scripting out statistics
There should really be a tool that clones the schema of a database and the statistics of a database while clearing out the data or subsets of the data.
Before deciding to use ID as a column name, consider this scenario when dealing with CSV files…
"SYLK: File format is not valid" error message when you open file
"SYLK: File format is not valid" error message when you open file
Some information on SAP Data Services here, and a refresher on Databases and Data Warehousing.
Also, an interesting post on ETL vs ELT. Would it be faster to use ETL to transform your data before loading, or would it be faster to just copy your data directly to the warehouse and manage it inside?
Perhaps this is the role the new FileTable type in SQL 2012 will play for you. Why not FTP your CSV files directly into SQL as blobs, and manage the transformations within?
Maybe not the best example, but it does centralize things a bit more…
SQL Server 2012 is released this weekend. SQL 2012, you can now use select-style statements to return customized results from a single stored procedure.
EXEC uspGetEmployeeManagers 16 WITH RESULT SETS ( ([Reporting Level] int NOT NULL, [ID of Employee] int NOT NULL, [Employee First Name] nvarchar(50) NOT NULL, [Employee Last Name] nvarchar(50) NOT NULL, [Employee ID of Manager] nvarchar(max) NOT NULL, [Manager First Name] nvarchar(50) NOT NULL, [Manager Last Name] nvarchar(50) NOT NULL ) );
Custom paging using an ORDER BY clause
-- Skip 0 rows and return only the first 10 rows from the sorted result set. SELECT DepartmentID, Name, GroupName FROM HumanResources.Department ORDER BY DepartmentID OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;
Throw to negate the need for custom messages with RAISERROR
C#/VB style Format to format dates, times, numbers
OVER clause for windowed summarization / rolling totals
PARSE to convert strings to dates, currencies, etc
TRY_CONVERT to avoid errors when converting data types
TRY_PARSE to determine if strings = data types
DATEFROMPARTS to take a year, month and day and create a date
CHOOSE to return an index item from a list of selections. (Arrays in SQL?)
Some helpful tools coming out of SQL MDS, including regex and XSLT straight from T-SQL.
I've been having a play with SQL 2008 R2 and its new Master Data Services (MDS). I was pleased to see it adds some additional functions when you create a new MDS database. These include mdq.Split and mdq.RegexReplace which implement handy Regular Expression functions. Most of these are documented at: http://msdn.microsoft.com/en-us/library/ee633712(SQL.105).aspx
I also discovered a scalar function called mdq.XmlTransform which allows you to use XSLT:
Tracking your social media campaign? Here are some tools to look at.
A couple years ago, Jay Baer wrote a great blog post called ‘The 39 social media tools I’ll use today’ which was an all-in-one toolkit for social media marketers (and still is).
A lot has changed in the two years since that post was published so here is a ’2012 remix’ featuring 50 (mostly free) tools you can use on a daily basis.
Whether you are just starting out in the social media arena or have been at it for a few years, this will hopefully be a handy resource. So, let’s serve ‘em up
Looking for a Windows 8 / HTML5 map control?
CanvasMap is a JavaScript map visualization control (similar to Bing Maps or Google Maps) that is built entirely on top of using the HTML5 Canvas element to render the map. Currently, neither Bing Maps or Google Maps use the Canvas element to render maps, so I thought I’d do this to see how the performance might compare. I know there are a few alternatives already, but I thought I’d have some fun trying to build my own.
One fork in the road may be a single platform for Windows Phone & Windows 8 apps, or the ability to run WP7.5 apps on Win8.
Which would be nice. Kind of like running iPhone apps on the iPad.
The star of last year's product roadmap was Office 365. Microsoft this year looks likely to pivot from the public cloud to the private cloud, while also shipping staples like SQL Server and releasing major betas -- and possibly final versions -- of blockbusters including Windows 8 tablet, client and server, and Office 15.
The 2012 Microsoft Product Roadmap -- Redmond Channel Partner
Need a fast way of getting lat/long values for a batch of addresses in Excel?
A lot of folks have to geocode data that they get from various folks in their organization, and it often makes its way in to Excel. Of course, you can batch geocode using Bing Maps Services, but in some scenarios it is a lot more convenient or reasonable to use Excel.
Don’t forget to ensure that your test cases contain time-based events, like leap years and Daylight Savings. Not that it would have helped much, since it was a cert expiry that caused the issue. The moral of the story? Ensure critical apps have a disaster recovery plan that doesn’t include a single source of failure (cloud provider).
When the clocks struck midnight, things quickly got janky, and a cloud-system domino effect took charge. A large number of Western Hemisphere sites and the U.K. government's G-Cloud CloudStore were among the many stopped cold by the outage. Microsoft has been retracing its steps in finding out what exactly happened and hasn't said very much yet, although it did report in an Azure team blog that the problem has "mostly" been fixed.
IT in Canada - Canada's Only Integrated Social Media News Network
NoSQL, or DIY SQL is the most common technique for blasting out distributed data stores.
This article is a great overview of the types of NoSQL designs out there, and ways to model the data.
NoSQL databases are often compared by various non-functional criteria, such as scalability, performance, and consistency. This aspect of NoSQL is well-studied both in practice and theory because specific non-functional properties are often the main justification for NoSQL usage and fundamental results on distributed systems like CAP theorem are well applicable to the NoSQL systems. At the same time, NoSQL data modeling is not so well studied and lacks of systematic theory like in relational databases. In this article I provide a short comparison of NoSQL system families from the data modeling point of view and digest several common modeling techniques.
I recently signed up for mailings from Analyticbridge, the social network for analytic professionals. It seems like daily emails are the norm, which I’ll have to setup a rule to move someplace. In the meantime, they just released a list of the top analytic blogs and websites.
Worth checking out.
Top analytic blogs and websites, with trending information - AnalyticBridge
I wonder if MS will fix this resolution limitation in the release…
Laurent Bugnion experienced Windows Update hell when he got hit in the middle of a presentation. The moral of the story? Run Windows Update before the presentation, or turn off updates altogether.
His presentation looks really interesting. Taking a Silverlight to WP7 & WP7 to Win8/WinRT.
What was the issue actually?
After reboot I tried again, and the same result happened: No Metro apps could be started. From the room, I got the suggestion that it was a resolution problem. And indeed: You cannot run Metro applications with less than 768 pixels vertical resolution. And because of the projector, I was running at 1280x720. So I thought OK, let’s try it, increased the resolution to 1280x768 and indeed I could start the Metro apps… but then the projector stopped showing the image!
23 seconds to download a CD’s worth of content. 3 minutes to get a DVD? Wow.
The news today is the new service, called OnLive Desktop Plus. It’s not free — it costs $5 a month — but it adds Adobe Reader, Internet Explorer and a 1-gigabit-a-second Internet connection.
That’s not a typo. And “1-gigabit Internet” means the fastest connection you’ve ever used in your life — on your iPad. It means speeds 500 or 1,000 times as fast as what you probably get at home. It means downloading a 20-megabyte file before your finger lifts from the glass.
You get the same speed in both directions. You can upload a 30-megabyte file in one second.
OnLive Desktop Plus Puts Windows 7 on the iPad in Blazing Speed - State of the Art - NYTimes.com
A log analyzer for Reporting Services, also try Log Parser Lizard
In this tool we demonstrate the best way to analyze the Report Server Service Trace Log and Report Server Execution Log that is useful if you are debugging an application or investigating an issue or event.
Download: Reporting Services LogViewer - Microsoft Download Center - Download Details
Killer PowerPoint examples – great video on how to create dashboards with PowerPivot.
Killer Real-World PowerPivot Examples | Tech·Ed Australia 2011 | Channel 9
Cool MS Outlook addin – find out how email conversations translate to emotions and behaviours.
Email Behavior Analyzer