Deploying SSAS databases I have found that there are three ways for deploying an Analysis Services database onto an Analysis Services server Using the Analysis Services Deployment Wizard Use the Analysis Services Deployment Wizard to use the XMLA output files generated by the Analysis Services project to deploy the project's metadata to the new server. Using an XML Script We can generate an XML script of the metadata in the SQL server Management Studio from an existing Analysis Services database...
Read More
Did you know that creating a data dictionary is easy if you use extended properties? Here is how I have done it with the database. The first step is to make sure the database has the extended properties defined. To do this right click on the database and then click on properties. click on the Extended properties link on the left hand side. check whether the extended properties are defined. If not define them by typing the name and value. You can also use sys.sp_addextendedproperty script to add ...
Yesterday I was trying to get a part of a string from a field where the first comma occurs. For example there is a field let us call it 'Descriptor' which is a combination of a few fields let us say "full name, Date of birth, unique number of the record" in that order. I wanted to get the full name using a select statement. Here is the syntax I used. select substring(descriptor,1,charindex(',', descriptor)-1) as [fullname] from contacts
I had this requirement today where I had to copy from a table in one database into a similar table in another database. the code I have used is insert into database1.dbo.table1 select * from database2.dbo.table1 where xxxxx where xxxx is the criteria that you need to add. Precautions you have to take before you do this. Backup the database into which you are copying the record(s) so that you can restore if something goes wrong. Copy contents when there are no other transactions being entered int...
I have listened to Craig Utley's SelectViews show on SSWUG.org . He gave the analysis tip that defining attribute relationships in SSAS is important for aggregations. What are attribute Relationships? In Analysis Services 2005, Attribute relationships define functional dependencies between attributes. For example an employee and manager relationship. Employee is related to Manager. Manager is relating to Employee Given this definition, consider the query: Select measures.sales on columns from Sa...
I thought I will put together all the date formats as a list here so that it can be a good referencing point when I need them. If anyone knows of anything that is not in here please let me know Query (current date: 21/05/2010) Example select convert(varchar, getdate(), 1) 05/21/10 select convert(varchar, getdate(), 2) 10.05.21 select convert(varchar, getdate(), 3) 21/05/10 select convert(varchar, getdate(), 4) 21.05.10 select convert(varchar, getdate(), 5) 21-05-10 select convert(varchar, getdat...
Defensive Database Programming By Alex Kuznetsov The goal of Defensive Programming is to produce resilient code that responds gracefully to the unexpected. To the SQL Server programmer, this means T-SQL code that behaves consistently and predictably in cases of unexpected usage, doesn't break under concurrent loads, and survives predictable changes to database schemas and settings. Inside this book, you will find dozens of practical, defensive programming techniques that will improve the quality...
BI Components of Office 2007 We have built a datawarehouse in our sql server 2005 database and built some cubes in Sql server analysis services using sqlser server integration services. Now I wanted to utilise the BI capabilities of office 2007 to provide useful information to the business users. The BI components of Office 2007 are: Excel 2007 Report Builder Microsoft Office SharePoint Server 2007 (MOSS) Excel Services PerformancePoint Services In this first part I will go through my learnings ...
I had a requirement to import an excel 2007 spreadsheet into a database as a table in sql server 2005. In Excel 2007 the drivers required for exporting have changed when compared to the previous versions. With Excel 2007 you use the driver named 'Microsoft Access 12.0 database engine OL DB provider' Following are the steps to import data into sql server 2005 from Excel. Right click on the database from sql server management studio and choose tasks -- import data Choose the data source as the dri...
I hve attended the idera sponsored webinar on 'Top 10 Most Useful SQL Server DMVs' by greg Robidoux Here are my learnings of that webcast What are DMVs Dynamic Management Views are views and functions introduced in sql server 2005 for monitoring and tuning sql server performance. Dynamic Management Objects (DMOs) Dynamic Management Views (DMVs) -- can select like a view Dynamic Management Functions(DMFs) --Requires input parameters like a function When and Why use them Provides information that ...
An exceptional lineup of SQL Server and BI experts is coming to your computer starting at 12:00 GMT (UTC) on May 19. 24 Hours of PASS provides an in-depth look at the hottest SQL Server and BI topics , including (but not limited to!) the new SQL Server 2008 R2. Take a look at some the sessions on offer: What Exactly is in SQL Server 2008 R2? Solving Common Business Problems with Microsoft PowerPivot Filtered Indexes, Sparse Columns: Together, Separately Top 10 Mistakes on SQL Server BLITZ! 60 Mi...
Here are the steps I followed to schedule a Sql server analysis services backup Connect to Analysis services using Management Studio Right click on the analysis services database for which you want to schedule the backup Click on backup Tick the Allow overwrite check box At the top click on the arrow beside the script and choose script to New Query Window The code is scripted. Below is an example. <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <...
Did you know that even if you create an exact copy of the sql server virtual machine, the analysis services will not work? I faced this problem last week. The exisitng development machine (lets call it DEV1) stopped working and was giving lots of errrors. So we thought that we will create a copy of the exisitng production system as a virtual machine and use that as the development machine and we created an exact copy of the production system as a virtual machine but gave it a different name (DEV...
Microsoft has released an ebook named -- " Introducing Microsoft SQL Server 2008 R2 , by Ross Mistry and Stacia Misner!" The book contains 10 chapters and 216 pages, like so: PART I Database Administration CHAPTER 1 SQL Server 2008 R2 Editions and Enhancements 3 CHAPTER 2 Multi-Server Administration 21 CHAPTER 3 Data-Tier Applications 41 CHAPTER 4 High Availability and Virtualization Enhancements 63 CHAPTER 5 Consolidation and Monitoring 85 PART II Business Intelligence Development CHAPTER 6 Sca...
The following is a news item I thought would be interesting for all of you. PASS Hosts New Com.PASS Content Feeds PASS is pleased to host Com.PASS , a new set of content feeds that provide Microsoft SQL Server and Business Intelligence professionals broad access to quality information across respected community Websites. Conceived by Brian Knight of Pragmatic Works and developed in collaboration with PASS President Rushabh Mehta, SQLServerCentral.com Editor Steve Jones, and sswug.org Founder and...
Nominations for The Exceptional DBA of 2010 open today, giving database administrators around the world the chance to be recognized for service to their employers, the SQL Server community, and the IT industry at large. The link to the site is: http://www.exceptionaldba.com/?utm_source=ssc&utm_medium=survey&utm_content=dba_awards&utm_campaign=sqlbackupbundle Secure and available data is crucial for a company's success, and so are the DBAs. All too often DBAs don't get the respect the...