Power Pivot is the new name for Microsoft Gemini, a user-based cube building and data analysis add-in for Excel 2010.
Not to be confused with Pivot Power.
Monday, October 26, 2009
Wednesday, October 21, 2009
Location Location Location
A few years ago, one of my clients determined that they should centralize their real estate and shift around workers to better optimize the facilities. Read – downsizing. I found this out from a student who was working on the project much later. All I knew was as contractors we had to be out of the building, and we might want to bring our computer equipment with us (and chairs, which I found out later).
So our development team was shuffled off from one of the nicest penthouse offices in the city, attached to a huge mall, next to the largest assortment of restaurants in the city, to a 1960s-design nuclear bunker disguised as an office.
We found out quickly that our small team of people had the run of the floor. Every chair was broken, and designed before Herman Miller got out of high school. There was a doomsday clock attached to the ceiling in the centre of the floor. It didn’t work either. The decor was styled with vintage warning signs. “Danger due to acid.” “Diesel generators – do not open this door!”.
It was a great work environment that motivated me to work from home (and other client locations) much more often.
Phil Factor reminded me about this, and has a funny story about how location is sometimes more important than appearance or prestige.
My friend, with some exaggeration, explained that the management had decided that it seemed wrong to give any offices of a major retail bank the appearance of a deserted building, so all contractors were given offices near the windows.
Phil Factor's Phrenetic Phoughts : Moving Down in the World of Work
Ten Common SQL Server Reporting Services Challenges and Solutions
Simple Talk has a great article on the top 10 things you might have issues in dealing with SQL Reporting Services.
Challenges/Solutions
SSRS offers a range of different reporting techniques and technologies, to cater for the reporting needs of all levels of users, from the chief executives, to business analysts, to operational staff. Their reporting needs range from simple, tabular ad-hoc reports, to parameterized, linked or snapshot reports, to complex drill-down and drill-through multi-level reports.
Following is the list of some of the challenges I have encountered while developing such reports using Reporting Services 2000/2005. In the sections that follow, I will cover each challenge individually, providing insight into what may cause the difficulty, alongside a possible solution.
- Horizontal Tables: Calendar Reports
- Select "ALL" Query Parameter option
- Multiple Sheets in Excel
- Excel Merged Cell Issues
- Blank Pages
- Vertical Text
- Report Data in Header/Footer
- Are you missing XML/CSV data on your exports?
- Template Reports
- Using the Reporting Services database
A ZIP file containing samples of the reports detailed in this article is available to download, try out and amend to suit your own needs.
Ten Common SQL Server Reporting Services Challenges and Solutions
Monday, October 19, 2009
Basics of the IIS Database Manager : Using the IIS Database Manager : Managing and Maintaining IIS 7.0 : The Official Microsoft IIS Site
Microsoft has released a new version of the IIS Database Manager, providing the ability to manage SQL from a web page.
Overview
IIS Database Manager allows you to easily manage your local and remote databases from within IIS Manager. IIS Database Manager automatically discovers databases based on the Web server or application configuration and also provides the ability to connect to any database on the network. Once connected, IIS Database Manager provides a full array of management options including managing tables, views, stored procedures and data, as well as running ad hoc queries.
IIS Database Manager provides native support for SQL Server and is also fully extensible for developers to add support for other database systems. In addition, because IIS Database Manager is an extension of IIS Manager, administrators can securely delegate the management of databases to authorized local or remote users, without having to open additional management ports on the server.
Here are a few articles to get you started on using the IIS Database Manager:
- Basics of the IIS Database Manager
- Working with Tables
- Working with Views
- Working with Stored Procedures
For more information, check out the IIS Database Manager Home Page.
Friday, October 16, 2009
Exam 70-433 - SQL 2008 Database Development
Just passed the SQL 2008 exam above. Without divulging the content, it seemed very obscure, and there wasn't a lot of maintenance or installation questions. Which is obvious in retrospect, because I ended up studying for 70-432 - Implementation and Maintenance but booked one exam too far ahead. Makes me even happier I passed it!
To study, I used MS OneNote to take the exam outline and break it down into multiple sections, crossing off the items I covered as I studied. Most of my studying included books online with some testing out of the new partition functionality and full-text functionality. I did a few reviews of the new features of SQL 2008 when it first came out in CTP, and have since implemented it a few more times. I didn't do a whole lot of deep-dives. The key things I focused on during studying were the new features of SQL 2008.
The exam itself was glitchy - some of the exhibits didn't show up, and a couple questions had duplicate answers. (which made it easier to find the right one.) For questions that were wordy, not obvious or not easy to read I marked for later.
These turned out to be almost half the questions on the exam.
It was definately a challenging one. Next step is to book exam 70-432 - SQL 2008 Implementation and Maintenance - the one I actually studied for!
To study, I used MS OneNote to take the exam outline and break it down into multiple sections, crossing off the items I covered as I studied. Most of my studying included books online with some testing out of the new partition functionality and full-text functionality. I did a few reviews of the new features of SQL 2008 when it first came out in CTP, and have since implemented it a few more times. I didn't do a whole lot of deep-dives. The key things I focused on during studying were the new features of SQL 2008.
The exam itself was glitchy - some of the exhibits didn't show up, and a couple questions had duplicate answers. (which made it easier to find the right one.) For questions that were wordy, not obvious or not easy to read I marked for later.
These turned out to be almost half the questions on the exam.
It was definately a challenging one. Next step is to book exam 70-432 - SQL 2008 Implementation and Maintenance - the one I actually studied for!
Wednesday, October 14, 2009
Microsoft SQL Server and VMware Virtual Infrastructure
Publisher:
VMwareLatest Revision:
Mar 18, 2009Download:
http://www.vmware.com/files/pdf/solutions/sql_server_virtual_bp.pdfDescription
Microsoft SQL Server is a very popular and widely deployed general purpose database server supported on Windows Sever operating systems. As customers embrace a “virtualize first” policy for all applications within their enterprises, they often need guidelines for deploying SQL Server in VMware Infrastructure. This paper should help you understand how to characterize your SQL Server databases for virtualization and the best practices for designing VMware Infrastructure to support SQL Server.
Concatenating Rows - SQLServerCentral
Intent
Sometimes when generating reports or text for web pages or form letters, it becomes handy to concatenate multiple column values from rows of data into a single value entirely within T-SQL. For reference, a problem I encountered recently involved selecting one or more department names and converting them to a comma-separated list to appear in a drop-down list.
The SQL language itself doesn't easily facilitate this but there are ways around this. Below I outline two alternate queries using SQL Server.
Open DBDiff for SQL Server 2008 — DatabaseJournal.com
Open DBDiff for SQL Server 2008
By Muthusamy Anantha Kumar aka The MAKOpen DBDiff 1.0 is the utility provided by Microsoft via CodePlex. You may have used the Open DBDiff utility for SQL Server 2005 before. DBDiff utility compares two databases and generates a script that could be used to synchronize two databases. The Open DBDiff utility is in beta version 8. This article is going to illustrate the various use of the Open Diff utility.
Sunday, October 11, 2009
Data loss easier than recovery
"This week, Microsoft announced that they had lost all Sidekick user data including pictures, contacts, calendars and other information from the Danger's servers. Since the devices sync with the servers, the devices also lost the data. The Sidekick data services had amazingly been out over a week.
From what they say, after a week of investigation, there is no way to retrieve the user data. Customers will have to start over. "
Link to story
And the moral of the story is... (for the MS cloud providers anyway)
Find Last BackUp Date Of All Databases on your Server
Whenever you perform a backup, SQL Server 2005 updates the following tables: msdb.dbo.backupfile, msdb.dbo.backupmediafamily, msdb.dbo.backupmediaset and msdb.dbo.backupset.
You can use these tables to retrieve backup information about your database. In the following query, a join is made between sys.sysdatabase and msdb.dbo.backupset to fetch the database name, last backup date and the user who took the backup.
SQL Server 2005 Solution
SELECT
T1.Name as DatabaseName,
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as LastBackUpTaken,
COALESCE(Convert(varchar(12), MAX(T2.user_name), 101),'NA') as UserName
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name
ORDER BY T1.Name
You can run this on multiple servers using SQL 2008 and the new server group feature.
You can exclude servers that aren't required to be monitored in the group by placing
AND @@servername not in ('servername')
in the where clause.
This is my take on the query:
Use msdb
go
select name,type,database_name,last_backup
from
(SELECT sd.name,
bs.TYPE,
bs.database_name,
max(bs.backup_start_date) as last_backup
FROM sys.databases sd
Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)
left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
where bs.[type] not in ('I')
and (sd.recovery_model <> 3 or bs.type not in ('L'))
and sd.is_read_only = 0
and @@servername not in ('localhost')
Group by sd.name,
bs.TYPE,
bs.database_name
) sd
where sd.last_backup <= dateadd(d,-1,getdate())
Order by sd.name,last_backup
All the databases that haven't had full backups in the last day.
For Sidekick users, the moral of the story is... backup your data outside of the cloud.
Should be an interesting week for those using Sidekicks to their full potential, and a blow to cloud computing proponents.
What would happen of any of these vendors announced they had lost all your (their) data?
Facebook
Myspace
Google Apps
Amazon S3
Blogger
Youtube
iTunes
Kindle
Something to think about...
Cloud-computing and the seven deadly data risks
From what they say, after a week of investigation, there is no way to retrieve the user data. Customers will have to start over. "
Link to story
And the moral of the story is... (for the MS cloud providers anyway)
Find Last BackUp Date Of All Databases on your Server
Whenever you perform a backup, SQL Server 2005 updates the following tables: msdb.dbo.backupfile, msdb.dbo.backupmediafamily, msdb.dbo.backupmediaset and msdb.dbo.backupset.
You can use these tables to retrieve backup information about your database. In the following query, a join is made between sys.sysdatabase and msdb.dbo.backupset to fetch the database name, last backup date and the user who took the backup.
SQL Server 2005 Solution
SELECT
T1.Name as DatabaseName,
COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as LastBackUpTaken,
COALESCE(Convert(varchar(12), MAX(T2.user_name), 101),'NA') as UserName
FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name
ORDER BY T1.Name
You can run this on multiple servers using SQL 2008 and the new server group feature.
You can exclude servers that aren't required to be monitored in the group by placing
AND @@servername not in ('servername')
in the where clause.
This is my take on the query:
Use msdb
go
select name,type,database_name,last_backup
from
(SELECT sd.name,
bs.TYPE,
bs.database_name,
max(bs.backup_start_date) as last_backup
FROM sys.databases sd
Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)
left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
where bs.[type] not in ('I')
and (sd.recovery_model <> 3 or bs.type not in ('L'))
and sd.is_read_only = 0
and @@servername not in ('localhost')
Group by sd.name,
bs.TYPE,
bs.database_name
) sd
where sd.last_backup <= dateadd(d,-1,getdate())
Order by sd.name,last_backup
All the databases that haven't had full backups in the last day.
For Sidekick users, the moral of the story is... backup your data outside of the cloud.
Should be an interesting week for those using Sidekicks to their full potential, and a blow to cloud computing proponents.
What would happen of any of these vendors announced they had lost all your (their) data?
Myspace
Google Apps
Amazon S3
Blogger
Youtube
iTunes
Kindle
Something to think about...
Cloud-computing and the seven deadly data risks
Friday, October 09, 2009
Features Supported by the Editions of SQL Server 2008
Features Supported by the Editions of SQL Server 2008
Use the following tables to determine which features are supported by the different editions of SQL Server 2008.
Wednesday, October 07, 2009
Farewell to the Excel 2003 addin and the BI Accelerator - Windows Live
Winding down availability for add-ins in earlier versions brings this add-in’s lifespan to a close. It sounds like customers were using this instead of upgrading to Excel 2007… which is still missing some of the features of the add-in.
”The Excel Add-in for SQL Server Analysis Services has been removed to avoid customer confusion about support for this component. As noted in the details that accompanied the release of this product, Microsoft does not provide any support for this add-in and has no plans to release future versions. Newer versions of Excel include most of the functionality that is provided by this add-in; these newer versions are supported according to the Microsoft Product Lifecycle.”
Farewell to the Excel 2003 addin and the BI Accelerator - Windows Live
What is the go-to path for Excel 2003 customers? Upgrade. It is unfortunate that they removed access to this tool rather than “open-sourcing” it or sending it off into MS Research land.
How about removal from MS downloads and addition into CodePlex?
http://www.codeplex.com/site/search?projectSearchText=analysis%20services
Same goes for tools like Proclarity. Leaving customers in the dark is not necessarily the correct approach.
Excel 2003 has ended mainstream support as of April 14, 2009.
http://office.microsoft.com/en-us/products/HA102294401033.aspx
Extended support ends January 14, 2014.
Subscribe to:
Posts (Atom)
