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
Perhaps it will be sooner? The new Cray’s achieve 50 petaflops, so all they need to do is get some biological computing interfaces hooked up to the processor to truly think for themselves as an organic being.
Amazing that in 1984 we were using 4.77MHz machines to draw lines on the screen and 30 years later we’re looking at not only simulating life, but replicating it.
Engineer and inventor Ray Kurzweil believes that the human Brain is capable of a comparable processing power of about 10 PFlop/s. Of course, there are different opinions and a rather scientific experiment to estimate the computational ability of the human mind by IBM engineers concluded that our brain may be closer to about 38 PFlop/s and a memory of about 3584 TB. At the current speed of progress, we may be able to simulate a human brain as early as next year or, by IBM’s estimates, within 3 years.
The new 50 and 100-Core CPUs might actually slow down some programs? Here’s how.
"We think 'Oh, there's something I'm computing. I'll compute it once and put it into a variable'. Well, if you put it in a variable, and then a hundred processors access that variable to get that data, you've got a bottleneck. But if all one hundred of them computed it, no bottleneck," he said.
"Boy, is that foreign to my brain," he confessed.
We suggested that our generation might have to wait for our kids to become programmers before this new way of thinking became the new standard. "I hope not," he chuckled, noting that although today's programmers may have to learn a new mindset, they do have one great advantage over the next generation of code monkeys: experience.
Intel code guru: Many-core world requires radical rethink • The Register
The GPU-processing mindset is quite similar. Who would have thought that running applications in the GPU would dramatically increase processing power of otherwise standard hardware. However, it requires programming specifically optimized for the GPU which many programmers wouldn’t pick up on.
Before you go optimizing for a GPU, watch for the FSA, a GPU+CPU architecture that is transparent to developers.
I wonder how fast email would come up with this one…
The XK6, announced on Tuesday, is made up of multiple supercomputer blade servers. Each blade includes up to four compute nodes containing AMD Opteron CPUs and Nvidia Tesla-architecture GPUs. It marks Cray's first attempt to blend dedicated GPUs and CPUs in a single high-performance computing (HPC) system.
Why does it look like they put in just enough blades to spell CRAY XK6?
The world’s most powerful computer currently runs at 2.5 petaflops. The Cray beast will run at 50.
This script may be useful to automate the constant email task of saving attachments. It can be applied as an Outlook rule to automatically save attachments for mail messages with a certain subject or sender. It should only be used against trusted senders and with a uniquely identifiable subject so as not to download corrupt or damaging files.
It is saved into the “c:\attach\” directory, however this could be substituted for a network share or Sharepoint folder in the code below.
To setup this rule in Outlook:
1. Tools – Macro – Visual Basic Editor
2. Right-click on Project 1 – Insert Module
3. Paste code below
4. Close window
5. Send yourself a message with the subject and attachment you want, or select an existing message.
6. Right-click on the message – Create Rule
7. Check sender and subject contains and select a part of the subject line that could be used to uniquely identify the email.
8. Click Advanced Options, confirm options and click Next
9. Check 'run a script', click 'a script' select Project1.SaveToFolder
10. Click OK
To turn off the rule:
1. Tools – Rules and Alerts – Uncheck or Delete the rule.
‘<CODE >
Sub SaveToFolder(MyMail As MailItem)
Dim strID As String
Dim objNS As Outlook.NameSpace
Dim objMail As Outlook.MailItem
Dim objAtt As Outlook.Attachment
Dim c As Integer
Dim save_name As String
'Place path to sav to on next line. Note that you must include the
'final backslash
Const save_path As String = "c:\attach\"
strID = MyMail.EntryID
Set objNS = Application.GetNamespace("MAPI")
Set objMail = objNS.GetItemFromID(strID)
If objMail.Attachments.Count > 0 Then
For c = 1 To objMail.Attachments.Count
Set objAtt = objMail.Attachments(c)
save_name = Left(objAtt.FileName, Len(objAtt.FileName) - 4)
'save_name = save_name & Format(objMail.ReceivedTime, "_mm-dd-yyyy_hhmm")
save_name = save_name & Right(objAtt.FileName, 4)
objAtt.SaveAsFile save_path & save_name
Next
End If
Set objAtt = Nothing
Set objMail = Nothing
Set objNS = Nothing
End Sub
Private Sub Application_Startup()
'MsgBox "Welcome, " & Application.GetNamespace("MAPI").CurrentUser
Application.ActiveExplorer.WindowState = olMaximized
End Sub
‘</CODE>
Document your Analysis Services databases.
Analysis Services Documenter (SqlASDoc for short) is a tool which creates documentation for your Analysis Services 2005 and Analysis Services 2008 databases. It connects to your live server, and queries the metadata for the name and description which the developers attached to every object (database, cube, dimension, hierarchy, attribute, translation, data source, data source view,...). It then produces an HTML file which contains the name and description of all these objects. But there is more: also other properties, such as last processing time, creation time, sizes of fact tables etc. are written to the HTML file.
Business Intelligence analysis doesn’t have to be just about the numbers. Unstructured information contains perhaps more valuable insight into day-to-day activities than financial or KPI data.
Here’s one add-in for Lync which lets you analyze your relationships via messenger conversations.
Conversations Analyzer is an application that reviews your Microsoft Lync instant messaging (IM) conversation history and gives you scores for your use of trust-building language in day-to-day communications with business associates and friends. Conversations Analyzer applies trust rules to determine the occurrence of words and phrases that have a positive or negative effect on trust.
Last month's release of an updated Analysis Services Operations guide.
Abstract:
This white paper describes how operations engineers can test, monitor, capacity plan, and troubleshoot Microsoft SQL Server Analysis Services OLAP solutions in SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2.
Curves, forks, and dead-ends in the roadmap of MS BI. Avoiding MDX in favor of SQL may no longer be an option when the MS “Universe” data model layer is adopted.
I just hope MS provides some tools that an end-user could use to maintain these BISM schemas, without having to understand DAX and MDX. And Visual Studio, or anything else over 500MB, is not a client tool that can be easily deployed.
Why not put this stuff into Sharepoint or Office? Building BISMs in Excel seems to be a good idea to me.
Thus, we'll have a single unified model, called BI Semantic Model (BISM). BISM will have two technologies to model data: the Multidimensional one (formerly known as UDM) and the Tabular one (that will be an enhancement of the current PowerPivot data model experience). You will have two languages, MDX and DAX, to define business logic and to query the model. And you will have two engines, MOLAP and Vertipaq (with their corresponding counterpart for accessing data in passthrough mode, ROLAP and Direct Query). The great and wonderful big news is that in BISM it will be possible to combine these components as you need: DAX query over a multidimensional model, MDX Script over a Tabular, and any client able to access to any model.
SQLBI - Marco Russo : Microsoft updates its BI Roadmap - #ssas #bism #teched #powerpivot
Pointclouds and heatmaps are my favourite visualization tools. Here is one example of how a city (in this case a video game city) could be mapped to show where the most accidents or other incidents occur.
http://www.vizworld.com/2011/05/heatmaps-point-clouds-big-data-processing/
If you are looking for innovation and future ideas, Microsoft Research is a good place to start. Steve Ballmer is on the Innovation Team for the US Government. Though spending for MS research has dipped recently, they are spending 90% of their $9.6 billion on the cloud.
Why is the cloud so important? Tablet computing. A tablet doesn’t have enough disk space to house an entire product suite, and IT staff wouldn’t want to be patching and supporting tablets in addition to desktop computers.
The tablet is a rogue PC that is infiltrating corporations organically. Like the iPhone, tablets are causing IT to take notice and focus efforts on mobile computing.
901 results for cloud on Microsoft Research. 836 results for Windows.
Looks like the future is a more ad-hoc, rapidly changing cloud environment.
Of course they couldn’t use IF or IIF (SSIS team had to be different…) so here’s how to do an if statement in a derived column.
Update: So the question was if how you would use the substring statement in the body of you iif statement to check if the column was blank…if it is then set if to null otherwise set it to the substring value.
Basically, it would be like this. Please note that I have used a different form to test for a blank field. In my version I use Trim and LEN(gth) functions so that the line can be a thousand blank spaces and I will get the same result..my column name in this instance is “Test”
(LEN(TRIM(Test)) > 0 ? SUBSTRING(Test,1,5) : NULL(DT_WSTR,5))
One reason why you don't name your keywords with size limitations.
CLARIFICATION The differentiation between Oracle's varchar2 is an Oracle datatype, equivalent to its (as well as TSQL's) own varchar.varchar and varchar2 seems to have originated back whenthe ANSI SQL standard was still being formulated. I cannot locate any definitive references as to what exactly the implementation differences, if any, were initially (pre-Oracle 7), as this remains a source of confusion. Suffices to say that the two are practically treated as synonyms since Oracle 7. Both suffered improvements (maximum size went from 2k to 4k) in release 8. Note that TSQL's varchar can store 8k
Some concerns around triggers. Usually there are performance and blocking issues whenever I see triggers in a database.
1) they make things happen "automagically"
2) they are rarely implemented correct
3) they slow down DML
Useful tip of the day – merge multiple files….
You can merge multiple csv or text files with a simple DOS command. Copy the code below into a text editor and save the file with a .bat extension. Save the file in the same folder as the csv files.
copy *.csv importfile.csv
I've used this as part of an import routine. Step one produces a set of csv files from SQL queries on an Oracle database. Step two merges all the csv files together. Step three imports a single file into Sage MMS
Software as a Service and Smart Apps are the future of BI
A flood of vendors have entered the SaaS BI market in recent years, including Actuate, BIRST, GoodData, Host Analytics, QlikView, Oco, Panorama, and PivotLink, while larger BI players like IBM, Oracle, Microsoft, and MicroStrategy are introducing products to take advantage of the move toward self-service BI. And all of them are racing to get a larger presence on smartphones and connected tablets.
Microsoft BI Labs consolidates some of the tools and add-ins from Microsoft Labs related to BI.
Sometimes the built-in tools available from Microsoft or your corporate BI software aren’t enough. It should not be taboo to look for 3rd-party tools that fulfill a need and provide value.
Most companies would not entertain use of third-party tools and utilities, for the obvious reasons of cost, developer expertise and corporate policies. But it is not that tools should not be used at all, it's just that there should be correct reasons when you should consider the options of using freeware or third-party licensed tools and utilities. Below are some of the reasons in my viewpoint when usage of tools can be justified and considered.
Last month’s outage of Amazon may have serious repercussions on the future of Cloud-based services and storage, and even business intelligence offerings in the cloud.
Like rolling blackouts, a disconnect from the cloud can kill business. Uptime, especially for a large cloud provider, has to be seamless. Like a hydro plant, it just has to work.
Although downtime for BI tools usually doesn’t equal the urgency of downtime for mail or web hosts, more than a day could be a killer for some apps.
Amazon outage sparks frustration, doubts about cloud - Computerworld
“Order is where you put a process into place because you want to scale the business to a different level. Bureaucracy is where nobody understands why you do it. “
Lars Bjork of QlikTech, on Order vs. Bureaucracy - NYTimes.com
I would add, bureaucracy is where you put approvals for daily processes in place that exceed a single level of the company organization structure, and the company structure looks more like a family tree than a nicely trimmed hedge.
For some reason, SSIS team dropped the ball when dealing with 64-bit and SQL 2005. Cryptic errors and non-working packages, and command-line workarounds were everywhere. SQL 2008 has a “use 64-bit” option when scheduling the package. However, there are still challenges with 3rd-party drivers.
Here’s a workaround to get Oracle 64-bit drivers going.
* Install Oracle 32 and 64 bit drivers, 11g i think, maybe first install 64 bit then 32 bit
* Install Attunity Oracle provider 64 bit
* Copy Visual Studio directory to new dir: C:\ProgFilesX86\Should work now both in designtime (BIDS) and runtime (SSIS). Oracle is sensitive to () in the path of the calling app so everything under Program Files (x86) will fail. After normal installation of SQL Server, simply copy VS dir and start devenv.exe from there.
Use Attunity:s .NET provider in SSIS. Really fast and stable.
Microsoft has always put limitations for licensing purposes on SQL Server. Express edition could only use 2GB of RAM. Standard edition was a laggard in terms of features in Analysis Services. Most of the time this required our customers to purchase Enterprise edition, and possibly sacrifice a scale-out architecture for scaling-up.
Scale-up might not let you get around these limitations with SQL 2008 R2. Datacenter edition might be the only option for those customers with > 8 CPUs.
What is new for SQL Server 2008 R2 Standard Edition and SQL Server 2008 R2 Enterprise Edition, are more restrictive hardware license limits compared to the SQL Server 2008 versions of both of those editions.
SQL Server 2008 Enterprise Edition had no limit for the number of processor sockets, but was limited to 64 logical processors. SQL Server 2008 R2 Enterprise Edition imposes a new limit of eight physical processor sockets, but will theoretically let you use up to 256 logical processors (as long as you are running on Windows Server 2008 R2). However, this is not possible, currently, since it would require a processor with 32 logical cores. As of April 2011, the highest logical core count you can get in a single processor socket is 20 (if you are using the new Intel Xeon E7 series). Also, the RAM limit for R2 has changed from “operating system limit”, as it was in the 2008 release, to a hard limit of 2TB.
SQL Server 2008 R2 Standard Edition has a new RAM limit of 64GB. This lowered limit may catch many people by surprise, since it is very easy to have much more than 64GB of RAM, even in a two-socket server. You should keep this RAM limit in mind if you are buying a new server and you know that you will be using Standard Edition. One possible workaround for this limit would be to have a second or third instance of SQL Server 2008 R2 Standard Edition installed on the same machine, so you could use more than the 64GB limit for a single instance. The physical socket limit for SQL Server 2008 R2 Standard Edition is still four processor sockets.
Glenn Berry's SQL Server Performance | Semi-random musings about SQL Server performance
Project Crescent seems to fall into the “shiny tool” space that Crystal Xcelsius occupies. From a presentation standpoint, it pops. Will it be a daily reporting workhorse? Let’s see.
Click on the Crescent logo below to play the Project "Crescent" teaser video and experience Project “Crescent” from the Microsoft Company meeting where we originally showed over 70’000 people for the first time this unique ability to bring data to life:
See the complete keynote at PASS Summit 2010 here: http://www.sqlpass.org/summit/na2010/
A Glimpse at Project Crescent - SQL Server Reporting Services Team Blog - Site Home - MSDN Blogs
Coming out of Microsoft there are a few tools that really made me turn my head a couple times and go “wow”. Data Analyzer, an OLAP tool from early 2000 was one of those.
It had the ability to save as PowerPoint, which Reporting Services and even Excel couldn’t do. It was discontinued after Office XP. It took PerformancePoint to bring that “export to PPT” feature back to life. And I didn’t know more than 2 people who actually knew what it was or used it more than twice.
Another tool I found innovative was Site Server 3.0’s Content Management link map. It was decision tree style 3D interface that let you browse through your site like a spider’s web, with the capability of zooming into the web.
Looked something like this but in 3-D!
The Wikipedia article on Site Server just about sums up many Microsoft “Product as a Solution” offerings targeted at the gray area between technical and business users.
On this front, Site Server's main advantage was its low cost. Another feature that might have been a source of confusion was the taxonomy management system. The tools used to maintain item metadata were very basic and required a degree of technical familiarity foreign to most business users.
Site Server was discontinued after it’s 3.0 release in 1998.
Sounds a lot like PerformancePoint Planning. Let’s rephrase that.
On this front, PerformancePoint Planning’s main advantage was its low cost. Another feature that might have been a source of confusion was the model management system. The tools used to integrate data were very complex and required a degree of technical familiarity foreign to most business users.
In the end, all things were absorbed by Sharepoint, except perhaps for that cool link visualization tool, and PerformancePoint Planning.
Perhaps, in the next version of Sharepoint, Microsoft will have another cube model builder with integrated dimension data management and a powerful set of financial reporting tools. Maybe even a cool 3D hyperlink visualizer too…
Building an application requiring map or postal code info for Canada? Learn about the number of residences or businesses by postal code using this service by Canada Post. Also includes PDF boundary maps.
http://www.canadapost.ca/cpc2/addrm/hh/current/indexp/cpALL-e.asp
Another service (paid) provides an updated database of postal codes with detailed address and lat/long information.
http://www.zipcodeworld.com/postalcodegold.htm
Canada Post - Householder Counts and Maps / Nombre de chefs de ménage et cartes
Sharepoint Insights demos for BI.
Overview
This SharePoint and Office 2010 demonstration Content Pack provides the content and scripts to support Insights Demo 1. The goal of this demo is to demonstrate and communicate the value of Business Intelligence (BI) in SharePoint 2010. We highlight some of the “eye-candy” components that pop, and demonstrate some of the Enterprise Content Management (ECM), social media, and enhanced search capabilities of SharePoint 2010 along the way. Please note that this demo is not designed to “sell” a particular feature, BI solution, or application. Rather, it seeks to demonstrate a sweep of Microsoft BI solutions (except SRS). We aim to show that BI is not merely a backward-looking set of data analysis tools to be used by executive management or financial analysts. Providing flexible BI solutions with SharePoint makes key business data accessible to mid-tier employees in an organization, to be used in daily decision-making and planning. It is intended for use with a specific Virtual Machine listed in the Installation & Setup section.
Insights (Business Intelligence) Demo 1 Version 3 - iwdemos - Site Home - MSDN Blogs
A verbatim repost of a great training linkfest from Clint Kunz.
- If you are a technical developer, dba, administrator, etc... and need to quickly learn about the suite of tools that Microsoft offers to enable Business Intelligence, check out:
- This book recently released the 2nd edition as of this posting, is a great way to map the latest Microsoft's technologies to the Kimball methodology for Data Warehousing:
- Subscribe to this Blog there is a very detailed set of technical training that will take you deeply into each aspect of creating solutions using each component of the Microsoft BI Stack:
- Here is the direct link to the announcement of the Training Kit:
- You can find SQLInsights on the MSDN Training and Certification Resources page at the following URL:
- Link to Bruno Aziza's Business Intelligence Blog:
With 6000+ attributes available to monitor in Windows XP this command line tool is very underrated.
WMIC.exe
Windows Management Instrumentation Command.
Read a huge range of information about local or remote computers. Also provides a way to make configuration changes to multiple remote machines.
I wonder what BI tool they’re using for that one… must be something in-house.
Four key BI predictions to help organizations plan for 2011 and beyond.
Gartner is the leader in creating Magic Quadrants. Microsoft is now the leader for Business intelligence, according to Gartner.
The niche market is becoming quite cluttered as the big players acquire and knock-out specialized tools in favour of mass markets and commoditization. Panorama, a company I worked with in the past, seems to have fallen a bit out of favour. Microstrategy is becoming a leader in the BI space and I think it’s a big potential acquisition target for one of the bigger vendors, if they decide to split their stock, and if the stock wasn’t as overvalued as it appears to be in my opinion.
With the stock on the way to $140/share, Microstrategy and it’s Ipad 2 strategy appears to be working. MSTR has a market cap of under $2 billion. Microsoft has $217 billion. IBM - $200 billion. Oracle - $172 billion. Apple - $317 billion(!)
There should be more consolidation in the future… but perhaps MSTR will be staying independent for awhile longer…
Daniel Rubiolo blog about his view of Business Intelligence, with some great metrics on the components and percentage of resources dedicated to a BI solution.
The traditional definition of “data warehouse, or DW” from late ‘70s/early ‘80s involved the end-to-end solution for business users… from extracting data and aggregating it into special data models, the queries, all thru the applications/reports the users interacted with to consume that data.
According to Daniel, a BI project is 70% getting the data and 30% showing it off. I would suggest it fits with the 80/20 rule in most cases. 80% of the time and effort in a BI project would be sourcing the data and gathering requirements, designing, developing, testing, and deploying a solution. 20% of the time would be adjusting and presenting reports.
This is probably why PowerPivot is such a favourite for power users, since they don’t necessarily require outside IT resources to build their models. It becomes a 50/50 game with a faster ROI. However, scalability and maintainability of a solution that resides on a user’s desktop remains in question. The “hit by a bus” rule makes desktop-based custom models a potentially risky solution for a business
Emilie Bridon provides a BI recap of 2010 in the Microsoft space.
The one that has me interested is BISM. Sounds like it might finally provide a central “one truth” version of your metadata. However the limitation of requiring Visual Studio might kill any hopes of leveraging it from a pure business perspective. Users that I work with need to be able to manage their data without an IT gatekeeper.
Can’t see anything else that excites me a whole lot with the BI space in 2010, except perhaps with Social media. It was truly an acquisition and consolidation year…
Business Intelligence Recap of 2010 - Microsoft Business Intelligence - Site Home - MSDN Blogs
Michael posts his Study Plan for 70-433 exam and details on SQL 2005 support
Yesterday was April 12, 2011, and as far as I know mainstream support for SQL Server 2005 ended. See Aaron Bertrand’s A Quick Note About SQL Server 2005 Mainstream Support.
With SQL 2005 support expiring shortly, the move is on to switch to SQL 2008 R2 (why would you switch to just SQL 2008?)
Running upgrade advisor before the upgrade is a must. However, some of the warnings could safely be ignored rather than changing a potentially large codebase.
I have seen many people asking how shall they move towards upgrading their SQL Server 2000 Database to SQL Server 2008. I thought of putting together an article based on my experience where I have migrated many servers from SQL Server 2000 to 2008. This particular Article is about analyzing incompatible code which is a MUST step in my view. At end of this article, I have compiled list of various errors/warning that you may come across after running analysis tool.
Watch for the performance effects of running SQL Analysis on a production box. It is running a firehose client-side trace from the Spotlight server to the SQL Server.
Based on your information, it would appear that someone in your group may be using SQL Analysis or session trace in the product. SQL Analysis is a schedulable utility that initiates a SQL Trace based on the criteria you specify - if it's enabled with the collection type default and only the options on the initial screen specified, you may be running a continuous client-side trace without any filtering to capture the various statement/procedure/RPC events. Session-level tracing initiated if someone clicks into a specific SPID (session_id) and then selects the trace tab, so it's a less likely option.
Spotlight on SQL Server Ent. 701 - ... | SQL Server | Database Management Community | Quest Software
The basics of using SSIS to load SQL Azure for a BI Cloud solution, part 1.
All the Pieces: Microsoft Cloud BI Pieces, Part 1 - SQL Server BI Blog
Migration Wizard
http://sqlazuremw.codeplex.com/
Pricing for SQL Azure
http://www.microsoft.com/en-us/sqlazure/purchase.aspx
Microsoft BizSpark for qualifying companies
http://www.microsoft.com/bizspark/
Recompiles to a stored procedure can cause performance issues. Other than WITH RECOMPILE, sp_configure, or a server restart, what else can cause a recompile?
We often use SET option in stored procedure without realizing if it can cause performance problem. I was working on Critical Database Performance issue recently arguing on SET option causing recompile without realizing which one causes or which one not. Moreover I came across in situation when some experts mentioned that "set isolation level read uncommitted" and "set nocount on" are causing recompilation. Whereas truth is these options DO NOT cause recompilation. So I thought of putting following list available handy for reference whenever we write code or optimize code or find stored procedure recompiling due to "set option changed".
Brent goes into detail about how important functional requirements are versus fuzzy requirements.
do you want it to be 10% faster, 100% faster, or 1,000% faster?”
Brent Ozar - Too Much Information
1000% please….
Correlation is important when dealing with risk management or forecasting calculations. The closer to 1 you are, the closer the numbers are correlated. Consider if you purchase GOOG and MSFT stock. Since they are both in the tech sector, they could be part of a combined selloff, which means your risk is increased if you own both in your portfolio.
The Select Sector SPDRs Correlation Tracker identifies GOOG as being 0.46 correlated to MSFT, which appears low.
You can use Correlation for forecasting results and determining trends in SQL. Consider this query:
SELECT(COUNT(*)*SUM(x.Sales*y.Sales)-SUM(x.Sales)*SUM(y.Sales))/(SQRT(COUNT(*)*SUM(SQUARE(x.Sales))-SQUARE(SUM(x.Sales)))*SQRT(COUNT(*)*SUM(SQUARE(y.Sales))-SQUARE(SUM(y.Sales))))correlationFROM BulbSales x JOIN BulbSales y ON x.month=y.monthWHERE x.Year=1997 AND y.Year=1998
Transact-SQL Cookbook: Chapter 8: Statistics in SQL
If the correlation is closer to 1, the numbers are similar. Comparing various years and months of sales or expenses can easily uncover correlations. This could also work for tracking correlation dependencies between expenses and revenue.
SQL has a number of formulas that are useful for identifying patterns and trends in your data.
Bidoma is an email/desktop alerting engine that notifies of changes to Excel worksheets and PerformancePoint dashboards. Could be a good measure for spreadsheet controls, master data management and timely responses to KPI changes.
![clip_image002[6][4]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_uqdv0-RFKCDZow_p8L7PvPCfCI1vVNwiOXeFWh9KG7Lpp_QiQqBjJwok0gL1uKdOWg54KvneBdXW7oN2nWijiqywoQd1YaRc_F-5DSDTGlc0sS4L0CgxuMGdhwIoZRb33w96SNcw4tLNmOjoiZOdlEQfqsenB7u6EvydL_H_weJI7k6I-ivqNkN-O_FmGT_0zZcbHusksgKqc9Z9cEHnf6ku5V0MtHv7GCYoACUYq5XtwBfpJRIMRzzm9OH3LIZpdN=s0-d "clip_image002[6][4]")
