SQL Server 2005 Best Practices…
30 juillet 2007 | Integration Services, Analysis Services, Reporting Services, SQL Server
Cet article a pour but de regrouper à un même endroit l'ensemble des best practices publiés autour de SQL Server et de la suite BI
Here are some highlights from Mike's reply:
- LINQ to SQL is targeted more toward rapidly developing applications against your existing Microsoft SQL Server schema, while the Entity Framework provides object- and storage-layer access to Microsoft SQL Server and 3rd party databases through a loosely coupled, flexible mapping to existing relational schema.
- LINQ to SQL has features targeting "Rapid Development" against a Microsoft SQL Server database.
- The Entity Framework has features targeting "Enterprise Scenarios".
- The Entity Framework is more than LINQ to Entities; it includes a "storage layer" that lets you use the same conceptual application model through low-level ADO.NET Data Provider interfaces using Entity SQL, and efficiently stream results as possibly hierarchical/polymorphic DataReaders, saving the overhead of materializing objects for read-only scenarios where there is no additional business logic.
Source: Julie Lerman Blog - Mike Pizzo on LINQ to SQL vs. Entity Framework
The Excel 2007 Add-in, can be downloaded from: http://www.microsoft.com/downloads/details.aspx?FamilyID=25836e52-1892-4e17-ac08-5df13cfc5295&DisplayLang=en
There is also an accompaning MSDN article "Publishing and Synchronizing Excel 2007 Tables to SharePoint Lists": http://msdn2.microsoft.com/en-us/library/bb462636(office.11).aspx
Creating a SharePoint list from within Excel 2007 is known as exporting an Excel table. In Excel 2003, this was known as publishing an Excel list. Once the table data is placed on the SharePoint site, users can see the Excel 2007 data without having to opening Excel 2007. You can maintain a link between the SharePoint list and the Excel 2007 data, but without the Excel 2007 Add-in, any changes in the Excel 2007 spreadsheet are not reflected in the SharePoint list. Without the Excel 2007 Add-in, you can only synchronize changes in the SharePoint list to the Excel 2007 spreadsheet - but you cannot save the workbook in the new Office Open XML Formats. Instead, to retain the functionality, you need to save the workbook in the Excel 97-2003 file format.
Source: Excel 2007 can now do two-way sync to a SharePoint List
Google Docs seems to have the most bang for the buck ($0)
The key features that characterize collaborative writing tools and technologies include:
- File types supported:which are the supported file formats for the files that can be edited collaboratively
- Text chat: the presence of a text chat that users can utilize to communicate while editing
- Revisions:the capability to track all changes made to the original document and go back to older versions
- RSS: support for RSS feeds, allowing users to get real-time notifications when changes are made.
- Email updates:the capability to receive email updating users when there are changes to the documents that are being edited.
- Public/Private:the possibility to set up private or public collaborative editing sessions.
- Max Editors:the max number of editors allowed
- Real-Time - Co-editing:the possibility to collaboratively edit in real-time
- Software/Web-based: the type of collaborative system
- Comments:the possibility to add comments to the document
- Spell check:the availability of a spell checking tool
Source: Collaborative Writing Tools And Technology: A Mini-Guide
Generates documentation for SQL schemas and Analysis Services...really well.
SqlSpec
Do you wish you had comprehensive, uniform, and easily navigable documentation for all your databases, regardless of platform?
Are you a developer or DBA that just inherited a huge, complex database with no supporting docs, and don't know where to start learning how it's put together?
Are you an independent contractor working with databases? Your clients would LOVE you if you could provide comprehensive documentation about the databases you build.
Are you a manager who is tired of your developers making changes to databases on live servers and not updating the corresponding spec?
SqlSpec will make sense of it all and generate easy-to-read docs for you for any database on the following platforms:
- SQL Server 2000/2005
- Oracle 9i and above
- DB2 8.2* and above
- Sybase ASE 12.0 and above
- PostgreSQL 8.0* and above
- MySQL 5.0 and above
- Analysis Server 2005
- Access 97 and above
Read more about the many features of SqlSpec here.
I've seen two techniques described for getting the results of an MDX query into an SSIS data stream. 1 - Use the OLE DB Source Adapter and ignore the whiny warning. This seems to work when you Preview, but crashes SSIS (even SP2) when you run the job. 2 - Use a linked server and an OpenQuery command. This works but has a lot of overhead. My colleague Pablo Guzman and I came up with a pretty slick way to do it. - Create a new ADO.Net Connection and from the .Net Providers\SQLClient Data Providers list choose the OLE DB Provider for AS9.0. - Create a DataReader Source adapter connected to the new AS connection manager. - Put the MDX into the SQLCommand property--be sure to map the external and output columns. This obviously flattens the cell set, but it doesn't limit what you put on the axes--probably a good idea to have the columns end up static. The DataReader Source adapter turns all the columns into DT_NTEXT, which you then have to convert to DT_WSTR to be able to convert to anything else, but that's standard SSIS data type manipulation. Seems a lot slicker than creating a linked server. Cheers, Reed
Source: Hitachi Consulting BI Blog :
Great resource for snippets here.
The Enterprise Library Data Access Application Block is the new version of the Microsoft Data Access Application Block (DAAB). Whereas the previous DAAB was a stand-alone application block, the new DAAB is a part of the new Enterprise Library collection of application blocks. You can obtain the most recent version of the library from the Patterns & Practices Enterprise Library Developer Center.
Last week, I spent one whole day trying to figure out how to configure and work with the new Data Access Application Block. This was due to few resources being available on the web. I even visited the Data Access Application Block forum at the ASP.NET Forums, but found few good resources to start with. Even with the few articles I found, I was not immediately successful in learning how to use the different methods available in the new Enterprise Library.
For the above reasons, I decided to write this article; not to explain how to configure the Enterprise Library, but rather to provide sample codes that can be used in a data-driven ASP.NET website.
Source: Get Started with the Enterprise Library Data Access Application Block: ASP Alliance
This executes in 2 seconds on a cold cache on my laptop, compared to 52 seconds for the equivalent query which evaluates the TopPercent for every single cell, so it's definitely a big improvement
Source: Advanced Ranking and Dynamically-Generated Named Sets in MDX - FindTech Blogs
Hot Topics
Performance Point CTP3 – will be available this month!
What’s new in Monitoring and Analytics CTP3
Click here to sign up for CTP3
SQL Server 2008: “Katmai”
Developers to Get Hooks into SQL Server ‘Katmai’
Bridging the Gap Between Data and Developers
Microsoft acquires Dundas’s data visualization components, & SQL 2008 CTP news
Microsoft Acquire Stratature
Microsoft Buys Data Management Vendor Stratature
Business Intelligence Virtual Labs
Try out a business intelligence virtual lab to test out the technology stack, including:
- Analysis Services
- Excel 2007
- Business Scorecard Manager 2005 (BSM)
- ProClarity
- Report Builder
- Integration Services
- Data Mining
Click here to access the Virtual Labs.
ProClarity Evaluation
Available through MSVL and through the following MSDN Premium subscriptions:
Visual Studio Professional w/MSDN Premium Subscription
Visual Studio Team Architect Edition w/MSDN Premium Subscription
Visual Studio Team Developer Edition w/MSDN Premium Subscription
Visual Studio Team Database Professional Edition w/MSDN Premium Subscription
Visual Studio Team Test Edition w/MSDN Premium Subscription
Visual Studio Team Suite Edition w/MSDN Premium Subscription
Academic Alliance members will also have access to these bits.
Feel free to email me for further assistance.
ERP-Link: Microsoft BI for SAP
The iNet Platform provides a SAP Certified connector to bring data from SAP R/3 & BW to SQL Server Analysis Services.
Jason Morales recently hosted a webinar with ERP-Link to present SAP BW data through ProClarity Dashboards. The session offered great insights into how iNet extends the ETL functionality of SSIS to more thoroughly integrate with SAP data sources.
Please click here to request access to the webinar recording.
Upcoming Events
PerformancePoint/ProClarity Briefing - Customer & Partner
London June 26th (AM): Click here to register
Manchester June 28th (AM): Click here to register
An introduction to Monitoring & Analytics
London June 26th (PM): Click here to register
Manchester June 28th (PM): Click here to register
TechNet Webcast: SQL Server Code Name “Katmai” Overview
When: Tue (June 26) @ 4pm-5pm (BST)
Click here to register
TechNet Webcast: Real-Time BI with SQL Server 2005 Analysis Services
When: Wed (June 20) @ 5:30pm (BST)
Click here to register
MSDN Webcast: SharePoint Server 2007 and Business Intelligence
When: Fri (June 22) @ 8pm (BST)
Click here to register
Training
Online & Instructor led
PerformancePoint Server Training Site
Includes BSM & ProClarity self-paced online training
Resources
Microsoft BI
Performance Management 101 with PPS 2007
SQL Server
Microsoft SQL Server 2005: Scales to your growing business needs
Data Mining
Microsoft BI Blogs
Microsoft BI Blog – by Patrick Husting
Microsoft OLAP by Mosha Pasumansky
Prologika (Teo Lachev’s Weblog)
Ian Tien’s Unofficial PerformancePoint Server and Business Scorecard Manager Blog
Russell Christopher's Semi-Useful BI Musings
Direct Reports (Brian Welcker’s Weblog)
Randon Thoughts on MS BI products - Patrice Truong
Microsoft BI Partners
http://www.microsoft.com/bi/partners/partners.aspx
OLAP Report - summaries
SQL Server Analysis Services 2005 (Microsoft)
Dimensional Relational vs. OLAP: The Final Deployment Conundrum
BI News Links & Press Releases
Ballmer on BI: Microsoft’s CEO Looks Ahead
Microsoft Touts PerformancePoint, Next-Gen SQL Server at Inaugural BI Fete
Microsoft Plans BI, Security, BizTalk Managed Services
SAP and Microsoft Extend Alliance for Microsoft SQL Server 2005
Business intelligence now a given for SMBs
Next-Gen Business Intelligence: The Future Is Now
Opinion: Enterprise Search Is This Year’s Hot Topic For Business Intelligence
Often end users use Excel to navigate into the cube. Then they may ask you to produce a report using the same data. It would be useful to get the MDX query used by Excel. Moreover, Excel 2007 offers a good MDX quality of the produced query against Analysis Services 2005 (much better than Excel 2003).
I don't know why Excel doesn't have such a function. I created a simple Excel macro that add an item to the PivotTable menu. A code that add a button in the Ribbon would be very welcome!
If you want to add these macro for all Excel sheets, you have to modify the PERSONAL.XLS file contained into "Documents and Settings\[Username]\Application Data\Microsoft\Excel\XLSTART".
This is the macro Workbook_Open that is executed when you open a workbook.
Private Sub Workbook_Open()
Dim ptcon As CommandBar
'See the following for list of menus in excel
'http://support.microsoft.com/support/kb/articles/Q213/5/52.ASP
'Title: XL2000: List of ID Numbers for Built-In CommandBar Controls
Set ptcon = Application.CommandBars("PivotTable context menu")
insertDisplayMDX:
Dim cmdMdx As CommandBarControl
For Each btn In ptcon.Controls
If btn.Caption = "MDX Query" Then GoTo doneDisplayMDX
Next btn
' Add an item to the PivotTable context menu.
Set cmdMdx = ptcon.Controls.Add(Type:=msoControlButton, temporary:=True)
' Set the properties of the menu item.
cmdMdx.Caption = "MDX Query"
cmdMdx.OnAction = "DisplayMDX"
doneDisplayMDX:
End SubAnd this is the DisplayMDX subroutine, that you can insert in a separate module.
Sub DisplayMDX()
Dim mdxQuery As String
Dim pvt As PivotTable
Dim ws As Worksheet
Set pvt = ActiveCell.PivotTable
mdxQuery = pvt.MDX
' Add a new worksheet.
Set ws = Worksheets.Add
ws.Range("A1") = mdxQuery
End SubWhen you right-click the PivotTable, the last item of the menu will be "MDX Query". If you click it, a new worksheet is created with the query in A1 cell. This is the fastest way to copy and paste it into other programs. I would like to be able copying directly into clipboard, but I still haven't found a reliable way to do it. If someone has some good idea, please contact me or comment to this post.
I attached a copy of a PERSONAL.XLS for the lazy of you!
Source: SQL BI : January 2007 - Posts
How to include SQL Server 2005 KPI’s in Reporting Services Reports
(The following is originally a description with screendumps, which unfortunately are not supported. Hopefully usefull anyway ...)
There seems to be an issue about including SQL Server 2005 KPI's in Reporting Services Reports (in Visual Studio) - especially the images associated with the generated parameters, e.g. "-1" leads to "red gauge / traffic light" and so on.
However it is possible to make reports in Report Builder including SQL Server 2005 KPI's.
It has been suggested to include a data source and a Report Server Model in the Visual Studio Project. This should make it possible to build a report including KPI’s. However there still seems to be a problem accessing the measures even though the metadata can be viewed. The Report Wizard does not have an option for including graphics for the indicators – only the numbers to generate the images (“1”, ”-1”, ”0” etc.). Therefore the indicators are not included in the dataset and cannot be included in the reports.
This has lead to the suggestion, that there is an issue with the Report Model functionality in Visual Studio.
There is a work around though ...
First you must associate your KPI’s to measure groups in the Visual Studio project …
Establish an Analysis Services data source on your Report Server …
Note: the Connection String must be typed when the data source is established. (See next-next screen dump.)
From the data sources on the reportserver a Report Model can be generated.
(Double-click the data source and click on generate model.)
Based on this model, reports can be build in Report Builder - including SQL Server 2005 KPI's, including images.
Open Report Builder from the Report Manager. The established data model(s) will be shown as possible data sources.
Build the report …
The reports can be exported as a .rdl-file from Report Builder. Click Save to file … not Save as …
If you choose Save as … the report is saved on the server.
Import the .rdl-file in your Visual Studio project …
The layout of the report can be edited in Visual Studio. The Data and Preview panes return an error when clicked.
Save and deploy the report … Right-click the project name / Properties to view where the report is deployed to by default (e.g. http://localhost/reportserver/reports).
The described solution is definitely a work-around – it is not an optimal solution. There are two major downsides with the solution: the reports cannot be previewed in Visual Studio and the dataset cannot be changed in Visual Studio.
Source: Integrating KPI in Reporting Services and drill down reports - MSDN Forums
I’m using the association rules algorithm from Analysis Services 2005 to figure out things like: On days that INTC rises significantly, MSFT often rises significantly too. However, I don’t intend to use that knowledge to simply buy MSFT when I notice INTC going up. There’s not much value in that in itself. The value arises in realizing there is a connection between the fortunes of MSFT and INTC. So, if there is a situation where INTC announces its quarterly earnings before MSFT, I’ll have a good clue that MSFT’s quarter will be similar. This won’t work well on stocks as well-known as these two. If INTC announced a great quarter, MSFT will immediately start rising. But perhaps there are pairs that go relatively unnoticed.
I can also create a web of relationships using these pairs. For example, hypothetically, if I see INTC and MSFT go up and down together very often, and I see that INTC and HP also go up and down together very often, there lies a small web: MSFT-->INTC-->HP.
This is the market basket analysis approach. In the normal use of market basket analysis, analysts want to know which products are purchased together (ex: milk and cereal, pizza and beer, steak and Cabernet Sauvignon) in the same "shopping basket". That way, the products could be placed near each other or promotion campaigns could be engineered for the two products. For more on market basket analysis (using OLAP), see Amir Netz's classic article, Analysis Services: DISTINCT COUNT, Basket Analysis, and Solving the Multiple Selection of Members Problem.
Source: Eugene Asahara
E D L E H M A N'S P O S T I N G
This coming Monday, June 4, we will release the June CTP of Katmai. We will also announce the official naming of Katmai as SQL Server 2008. Download links will be live on http://connect.microsoft.com/sqlserver Monday morning at 8:30am EST.
Among the 27 improvements included in the CTP are:
Declarative Management Framework (DMF) is a new policy-based management framework for the SQL Server Database Engine that delivers the following benefits:
. Ensure compliance with policies for system configuration . Prevent/monitor changes to the system by authoring policies for the desired configuration . Reduce total cost of ownership by simplifying administration tasks
Change Data Capture (CDC). Change Data Capture (CDC) is a generic component that will track database changes asynchronously and expose the changes through a relational interface which can be consumed easily.
. Through this interface, consumers can very easily track changes based on their specific requirements and consume the change data using T-SQL or other data access methods.
MERGE SQL Statement. Common data warehouse scenarios require developers to either insert information or update information. SQL Server 2008 will provide new statements within the SQL language to enable developers to provide this functionality with a single statement.
Star Join Query Optimizations. For improved query performance for common data warehouse scenarios, Star Join Query optimizations reduce query response time by recognizing data warehouse join patterns.
AS Dimension Design: Improved Supportability and Integration of Best Practices . Enhance UI for creating and editing dimensions to guide users toward designs that follow best practices.
. These include: Finish Attribute Relationship Designer, Dimension structure (presentation of attribute relationships), modification to wizards to align output with best practices, simplifying creation of composite keys, and AMO warnings (spanning all objects, not just dimensions)
Table Value Parameters. In many customer scenarios, it is necessary to pass a set of table structured values (rows) to a stored procedure/function on the server. These values may be used for populating/updating a table directly or for more complex manipulation of data for business logic purpose. Table valued parameter will provide an easier way to define a table type as well as allow applications to create, populate and pass table structured parameters to stored procedures and functions
Another important accomplishment is taking the first steps for the new setup architecture with the enablement of side-by-side installations with Yukon SP2.
Along with the release of the first Katmai CTP, we're implementing a new approach for SQL Server Samples and Community projects on Codeplex (http://www.codeplex.com/SqlServerSamples), Microsoft's open source project hosting site. Customers will now have direct access to all Microsoft SQL Server product sample source code without the need to install anything. Addressing a long-standing customer ask, customers can also install samples for specific SQL technologies from Codeplex rather having to use a single monolithic install of all samples. Microsoft SQL Server Product Samples released on Codeplex will be aligned to and tested with specific releases.
We have also started to release Community sample projects on Codeplex, which include groups of samples contributed outside of our regular product release cycle by SQL Server MVPs, Microsoft employees, or other members of the community.
The Codeplex SQL Portal page serves as a common entry point for these and other SQL Server-related projects on Codeplex. The Katmai version of SQL Server Management Studio and BI Dev Studio include a "Samples and Community Projects" link on the Community Menu that will redirect customers to the portal page.
With this approach to samples and community projects, we bring together a much broader body of code for our customers from a wide variety of sources. This new approach also enables us to take much more of a cross-product approach aligned to customer needs than the single-product focused approach we have taken in the past.
Along with the June CTP, we will be opening New MSDN Forums dedicated to Katmai located at http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=428&SiteID=1 (won't work until Monday). We are also expanding a pilot that's been working very well in the SSIS forum. Any known MVP poster can now flag replies to forum threads that need to be escalated to the Microsoft SQL team for follow-up. Member of the SQL team will be notified daily of any escalated threads. In order to flag any reply to a forum thread, just place the text [Microsoft follow-up] (including the brackets) in your reply. As always, if you have any problems with this new capability, please let me know.
Go to http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx for additional info on Katmai.
Analysis Services Processing Best Practices
SQL Server Best Practices Article
Published: June 7, 2007
Writers: Denny Lee, Nicholas Dristas, Carl Rabeler
Contributors: Kevin Cox, Craig Utley, Eric Jacobsen, Akshai Mirchandani
Technical Reviewer: Sasha (Alexander) Berger, Dave Wickert
Applies To: SQL Server 2005 SP2
Summary: Following the best practices described in this article will help you improve the performance of OLAP database processing
This probably does not apply to many people yet, but If you have installed the RTM of Office 2007 and don't yet have SP2 of SQL 2005 installed, then your calculation tab in the cube designer is probably broken.
Source: SSAS: Office 2007 - breaks the calculation tab in BIDS
Say you have a table which contains different currency values - each row in your table contains values for stored with a different currency types (see figure 1). You want to display these values in your report and ensure that each value is displayed with the correct currency symbol (figure 2). How do you to this?
SQL Server 2008 CTP3 has been released to the public for download. In addition, MS has purchased Dundas Software's visualization tools for incorporation into the platform. No more cripplecharts in Reporting Services!
Do you have passion for technology? Do you want to make a difference? Then try out a SQL Server 2008 Community Technology Preview (CTP). The SQL Server development team uses your feedback from CTPs to help refine and enhance product features.
Source: SQL Server 2008 CTP
MS is offering Exchange, SharePoint, LCS, and now PerformancePoint as a managed service. The new flavours of BizTalk should drive the growth of these options.
"Microsoft pushing a managed services offering is more troubling at this interval than any previous occasion," said Stephen Moss, COO of NSPI, a Roswell, Georgia security solution provider. "It will be most interesting to see [Microsoft's plans] in the area of direct offerings of services or Software + Services as the case may be."
The complete mashup of MS BI technologies will soon be released as PerformancePoint.
Q: Microsoft talked a lot today about the next version of SQL Server. What's on the horizon for Microsoft Bl applications, and what can users expect next?
A: The key thing is what we are doing with PerformancePoint 2007
Source: CRM News: Strategy: Microsoft's Raikes on the Role of BI in Corporate Strategizing
Hmm... I wonder if this relates to Microsoft Surface? Multiple touchpoints = multiple mice maybe?
Overview
The Windows MultiPoint SDK helps developers create programs that affordably increase the reach of existing classroom computers. With MultiPoint, a single computer can have multiple mice connected. MultiPoint makes it possible for groups of students to work on one computer—at the same time—helping students everywhere to build essential learning skills such as collaboration, teamwork, and computer familiarity.
Source: Download details: Microsoft Windows MultiPoint Software Development Kit (SDK)
SQL Server 2008
Microsoft have officially announced their intention to have Katmai available in 2008. (The title of this post is nothing more than idle speculation. Katmai doesn't have an official name yet.)
http://www.microsoft.com/presspass/press/2007/may07/05-09KatmaiPR.mspx
Among the features announced are the Declarative Management Framework, the Resource Governor and also support for geographical data, documents and images in the database.
Declarative management will allow you to manage your SQL Server configuration across many databases and servers by defining policy rules that are automatically applied, montiored and enforced. It's a great feature for large data centres and complex environments. It's also unlike anything I have seen from the other DBMS vendors.
The Resource Governor is a very welcome and overdue improvement. It means that for the first time you will be able to allocate and limit SQL Server resources to support individual workloads with different priorities and needs running concurrently on the same server. That's going to help deliver more consistent performance and availability from every SQL Server instance - for example by making sure that ad-hoc decision support queries can't impact your operational systems by starving them of resources.
The new data type support is potentially exciting too. I've been asked more than a few times about support for spatial data within SQL Server. It's something that DB2 and Oracle already offer.
You can read more at:
http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx
PerformancePoint CTP3 - Possible release date and some future features in Monitoring and Analytics.
I was asked last night whilst presenting Monitoring and Analytics in CTP2 to the UK SQL BI users group (www.sqlserverfaq.com) if I knew when CTP3 was due out.
This from Jeff Zhu at Microsoft on the newsgroup.
The date may be around July 20th.
Most of changes in CTP3 of Planning will be bug fixes. We also have some security enhancement in the application, more documentation, and a few of feature improvements. Don't expect many new features in CTP3.
I like One Note... and people who put Power Toys out for it.
The ability to sort tabs is an obvious feature that was missing from 2003. This powertoy solves the problem (for 2007)
Here's a comment from someone who is using One Note as a patient database.
Daniel,
This addon is SO highly coveted! I fully plan on using OneNote for my clinical practice, however, I was really concerned about being able to find my patients quickly. This will be just great (I can sort at the end of every day).
Suggestions? Well, maybe an addon like this, but it will automatically take a page that begins with B and place it in the B section (or any other letter). This will help me find my patient's names more quickly.
Thanks!
erik
Source: OneNote Extensibility & More.. : Sort Pages powertoy
I use OneNote regularly to track my day-to-day activities and various snippets from emails. The WIN-S screenshot shortcut is invaluable when doing documentation. I just installed 2007 and will have to see what new features come out of it.
I think there are some other people who have rave reviews of One Note, according to one of the founders of the product:
Another measure is "buzz" like the blog measure above. Are people talking about your product? If so, that's also a good sign. Notice there is a spike not just on the "news" of availability (around Jan 30) but there are higher spikes later - that's when people are using the product and talking about it. For examples of what people are saying, check out Dan's "blog roundup" posts: January, February, March, April. Some of my favorite quotes:
"OneNote 2007 sharing is indistinguishable from magic"
"I just purchased a copy of Microsoft OneNote, my life will never be the same."
But those are tame. Why not really go for it?
"The Greatest Invention in Human History? I vote for Microsoft OneNote"
"I need Office OneNote 2007 to live."
And for the you-know-who crowd:
"I can't believe I'm so excited over some program that M$ came up with. It's probably just all the adrenaline that's been pumping through me lately."
And we're just getting started!
Source: Chris Pratley's OneNote Blog
I just wish that they would expose the .one file format and a macro language so that we can write some custom code against it. (Silverlight viewer, maybe?)
Will see if it's doable 2007.
For example:
=iif( me.Value < 0, “Red“, “Black“) placed in the color property of a textbox this expression will change the text colour based on the value returned.
=Format(Fields!BirthDate.Value, "MMM dd,yyyy") placed in the value property this expression will format a date value, (ie Dec 10, 2006)=System.Web.HttpContext.GetGlobalResourceObject("AppResources", "ID14831").ToString() placed in the value property uses your existing resource file to translate a label
Source: Dave Lloyd's 2 Cents
Analysis Services really shines as a client-server solution. However, for remote locations, portability, and custom applications, local cubes may be what you want.
Creating a local cube is fairly simple so I won't go into it.
The syntax can be found here.
http://msdn2.microsoft.com/en-us/library/ms145581.aspx
After playing a bit with the parameters, I managed to create a global cube located in a .cub file on my laptop. Oddly enough, Excel 2003 does not associate .cub files, so you need to either associate it or open Excel and then open the .cub file. Opening the file brings up the standard pivot table interface against the local cube. A couple problems I encountered - dragging in dimension attributes appeared slower than hosting the cube on a server, and I encountered the 64,000 row limit pretty quickly.
I passed the cube on to someone else in our company, who received a No Visible Tables error message.
Nobody said client requirements for BI solutions were trivial.
Here they are.
Microsoft .NET Framework Version 2.0 Redistributable Package (x86)
MSXLM 6.0 Parser
Microsoft SQL Server 2000 PivotTable Services
Microsoft SQL Server 2005 Analysis Provider Services 9.0 OLEDB Provider
Of course, Analysis Services 2005 also has support for synchronizing databases, so if it is possible to keep multiple servers at remote locations, this would probably be the better route for things like SOX compliance and security purposes.
Ever since I found WinDirStat, the best tool for finding large files on your hard disk and cleaning up data, Treemaps and Heatmaps have been of interest.
Here is one way to replicate this functionality in Reporting Services.
I generated the colors by finding the darkest acceptable color (not quite red) that black text was still readable on top of, then creating a gradient using Paint.NET fading from this color to white, then finally using a "pixelate" effect in Paint.NET to remove the gradient smoothness and get definite colors. I'm sure you can think of better ways to do this!
Source: Thomas Williams :
Our Blogosphere
• Cindi Howson's BI Scorecard
• The Brain Food Blogger
• Tony Byrne's CMS TrendWatch
• Third Eye View, by Rajan Chandras
• Breakthrough Analysis by Seth Grimes
• In Context by Doug Henschen
• As the Enterprise Develops, by Nelson King
• David Linthicum on Changing the Enterprise
• Alan Pelz-Sharpe's ECM TrendWatch
• Addicted to BI, by Neil Raden
• Peak Performance by Mark Smith
• Product Maven
• Bruce Silver's BPMS Watch
• David Stodder's Mission Intelligence
Source: http://www.intelligententerprise.com/movabletype/blog/chowson.html
Dali is the intelligent object-relational mapper and data access layer that can drastically reduce the amount of database code you write. Most developers experience a 50-80% reduction in data access code. Less code means less errors and less time.
Source: Home :: Revelation Technologies
Spreadsheet Evaluation
This evaluation discusses criteria to consider when evaluating BI suites Office add-ins and export capabilities.The BI Market
Business intelligence and the sub markets defined. Lists vendor products per sub market, serving as a necessary guide for comparing like-for-like products and seeing the breadth of individual vendor offerings.Blank BIScorecard®
This spreadsheet provides a summary overview of vendor scores per major functional area plus a detailed list of 100+ criteria to consider when selecting or standardizing on a BI product.Flying High with BI
How innovation and a maturing market will finally allow BI to take off in many organizations.
Enterprise Business Intelligence: Strategies and Technologies for Deploying BI on an Enterprise Scale
Here's the Deal: Negotiating BI contracts
The BIScorecard® review series in Intelligent Enterprise
Although the product scores here are now out of date, the series provides you with a framework for evaluating BI suites and a sample of the approach used on BIScorecard® for current versions of BI suites.
Source: :: BIScorecard® ::
Certified Business Intelligence Professional (CBIP)
TDWI, the leading association for business intelligence and data warehousing professionals, offers the industry's most comprehensive certification program available: the Certified Business Intelligence Professional (CBIP). CBIP, a true test-based certification program, is offered in five key specialties for Business Intelligence success: Leadership & Management, Business Analytics, Data Analysis & Design, Data Integration, and Administration & Technology.
Becoming certified requires that you pass three exams; the Core exam, the Data Warehousing exam, and one specialty exam. Passing the CBIP exams is based on your knowledge and experience within your area of specialization. Prior study is not required although it is recommended to update or expand your current knowledge.
Levels of Certification
Practitioner Level: CBIP certification at the Practitioner Level is awarded to professionals who score above 50 percent on each of three exams. Practitioner Level certification demonstrates a working knowledge of concepts, skills, and techniques within a specialty, as well as the ability to assume leadership responsibilities in the area of specialization at the project level.
Mastery Level: CBIP certification at the Mastery Level is awarded to professionals who score above 70 percent on each of three exams. Mastery Level certification demonstrates that concepts, skills, and best practices have been mastered within a specialty, as well as the ability to lead a team effectively at the project and program levels, and the skills to mentor others.
Source: Certified Business Intelligence Professional (CBIP): TDWI
Predictive Analytics - Something worth looking at (see Monte Carlo Analysis)
During this last course, attendees also threw the vendors a wild card in which they got to vote for a new demo topic. The vendors only learned what that topic would be during the lunch break. Top of the list: predictive analytics (beating out other hot topics such as performance management, production reporting, and BI search). Kudos to each of the vendors for speaking about - and for some, demoing - these capabilities literally at the last minute!
Source: On BI Bake Offs and Vaporware | The Intelligent Enterprise Blog
How can I create local cube files?
There are two ways to create local cube files in Analysis Services 2005. The easier way is to use a command called CREATE GLOBAL CUBE. A more complex (and more flexible) way is to use Analysis Services Scripting Language (ASSL). CubeSlice 9 supports two forms of ASSL – one that uses the Analysis Server cube as the data source and the other that uses the relational data source. The CubeSlice Relational ASSL usually offers more flexibility in creating local cube files.
For more information on the creation of local cube files, download the document Using Local Cubes with Microsoft SQL Server 2005 Analysis Services from this website.
Source: Questions and Answers about Using Local Cubes to Improve Browsing Speed Performance
Toby Gray, that's just not fair! How come my chocolate covered raisins never come with any SQL?
Source: Classics Week: Chocolate Covered SQL - Worse Than Failure
How do I refer to a table on the web? How to I refer to specific colums/row/cells in a web table?
Here's an example that may help you out. It goes to Yahoo's finance page (where I knew there would be a table) and finds the table with the mortgage rate. Then it finds the row for 15 year mortgages and writes the whole row to an Excel sheet. MSDN has help for these HTML objects here. Look for 'document', 'table', and 'td'.
Source: Daily Dose of Excel » Automation
Implementation and Maintenance exam (70-431) is the first stop for everyone entering this new certification track, and serves as both a single exam certification as well as the entry exam for the MCITP-level certifications. This book provides an introduction to the development and administrative aspects of SQL Server 2005 and features practical guidance for all aspects of the exam.
Source: Wiley::MCTS: Microsoft SQL Server 2005 Implementation and Maintenance Study Guide: Exam 70-431
Microsoft SharedView Beta
Brief Description
Microsoft SharedView is a fast and easy way to share documents and screen views with small groups of friends or coworkers; anytime, anywhere. Use SharedView to put your heads together and collaborate.
Whitepaper - Improve your OLAP Environment with Microsoft and Teradata
Brief Description
This paper describes how you can leverage Microsoft SQL Server Analysis Services and Teradata technologies to improve your analytical OLAP application environment, specifically relational (ROLAP) type solution.
Source: Download details: Teradata-Microsoft White Paper - AJI and OLAP.doc
Brief Description
myBI enables users to view reports, scorecards, key performance indicators (KPIs), multi-dimensional analytics, and other metrics without having to move from one reporting site to another.
Source: Download details: Centralizing and Sharing Business Intelligence
..today, Microsoft announced the purchase of aQuantive
, a 10-year-old, publicly traded digital marketing company in an all cash deal worth approximately $6 billion -- the biggest acquisition in Microsoft history.
So who is aQuantive? The Seattle-based company owns Avenue A | Razorfish, Atlas and DRIVEpm, which Microsoft calls "one of the industry’s most successful families of digital marketing service and technology companies." The company was originally founded in 1997 as Avenue A, and went public in 2000 under that name. It became aQuantive in 2003.
Great site for finding patterns, related sites and referrers to a web site.
One of the greatest enhancements in Analysis Services 2005 is the ability to use SQL Server Profiler to troubleshoot performance issues during querying and processing. Even cooler is the ability to import Perfmon data into Profiler so you can analyze system and Analysis Services performance counters at every step of querying and processing.
Source: Elizabeth Vitt - Analysis Services - Importing Perfmon Data into Profiler
Automating build of Analysis Services Projects
As you may be aware, an Analysis Services project consists of the following files:
- A project file (*.dwProj)
- One or more Data sources (*.ds)
- One or more Data source views (*.dsv)
- A database file (*.database)
- One or more cube files (*.cube) with their partition (*.partitions)
- One or more dimension files (*.dim)
You can right click the project in Visual Studio and choose "build". What happens now seems to be a lot of called to DLL files executed by the integration between Visual Studio and the Analysis Services. The output of this build is the bin folder in your project directory. This folder contains a database file (*.asdatabase). The .asdatabase file is the one you want - you will see why later.
Unlike other projects - which you can build using their proper compiler (Example: CSC.EXE, VBC.EXE) - Analysis Services does not seem to have a compiler executable.
You could be tempted to run msbuild. Unlike most temptation this will result in disappointment - msbuild does not understand Analysis Services files.
The only way I have found to automate the building of Analysis Services projects is to invoke Visual Studio from the command line. Say you have a project called: MyAsProject.dwProj. To build it and obtain the asdatabase file you execute the following:
devenv MyAsProject.dwProj /rebuild Relase /project MyAsProject
So far so good - now you have automated the createion of the .asdatabase file.
You are not done yet. The asdatabase file provides the input to the Analysis Services Deployment Wizard. The spell cast by this wizard allows you to transform an asdatabase filen into an XML/A file. This XML/A file can be run on your deployment target - which will create the cube database.
From the command line the Deployment Wizard can be run in two modes.
The answer mode:
Microsoft.AnalysisServices.Deployment.exe /a
In this mode, you get to choose all the server specific settings and write them back to your bin directory. Check out the BOL documentation to find out exactly how this is implemented (You will find no defense for the naming of the executable in the documentation)
Once you have your answers stored (which you will only do once) you want to automate the creation of the XML/A file. Execute:
Microsoft.AnalysisServices.Deployment.exe MyAsProject.asdatabase /d /o:MyProject.xmla
Voila! You have your xmla file, which you can copy to your deployment target and run. By the way. xmla files can be run from the command line using ascmd.exe which you can find here: http://msdn2.microsoft.com/en-us/library/ms365187....
Source: Thomas Kejsers Blog
What version of SQL Server do I have?
This unofficial build chart lists all of the known KB articles, hotfixes and other builds of MS SQL Server 2005, 2000 and 7.0 that have been released.
After several weeks of frustration I finally got Report Manager to work with Vista. I guess it was too much to expect of Microsoft to actually have their own software install correctly on their own OS. For anyone else having problems this is what I had to do:
1) In Configure Report Server|Web Service Idenity - add both Server & Manager to the Classic .Net AppPool.
2) Even though I am the only user account on the computer I'm developing on and log in as administrator, that, apparently isn't good enough for Vista. (Apparently in the world of Vista NOONE is an adminstrator with full rights - great big atta boy to MS). I had to go to the control panel and add my user account and the admin account (for good measure) to the IIS user grooup.
3) Go to add/remove programs and install EVERY IIS option; again, Microsoft won't do it for you.
4) Turn off that silly user control dohicki that Apple makes fun of in their commercials (Earth to Microsoft - that thingie IS rather stupid; how about finding a way to stop hackers without crippling your OS?)
End result: I can now use Report Manager on Vista Ultimate 64bit/SQL Svr 64bit with only a few minor glitches thanks to some clues I picked up in this forum and some hair pulling sluthing.
Source: SSRS and Vista - MSDN Forums
Conclusions
- It is clear that ProcessAdd is worth the extra effort if you need to further optimize ProcessUpdate dimension processing.
- It is clear that AttributeHierarchyOrdered=false on high cardinality attributes makes a significant difference to dimension processing. Test case H shows that AttributeHierarchyOrdered=false on high cardinality attributes can double performance of ProcessAdd.
- ProcessUpdate is by far the most expensive option available. It is so much more expensive than ProcessFull that you should definitely verify whether processing scheme #1 actually performs slower than processing scheme #2 on your dataset.
Source: Greg Galloway
No more copying large ISO files around?
HttpDisk is a virtual disk driver for Windows that uses HTTP to mount disk or CD images from a web server.
Latest news: Support for persistent connections to improve performance.
Source: Windows driver examples
Plus, a virtual disk drive file, basically TrueCrypt without the encryption. Useful for maintaining drive letters between systems, and possibly as a siloed work area for source control systems.
FileDisk is a virtual disk driver for Windows NT/2000/XP that uses one or more files to emulate physical disks. A console application is included that let you dynamically mount and unmount files. An example of use for this driver is if you have made plans spending the weekend writing an RAID driver for NT but find you are short of disks. FileDisk can also use CD-images.
This website is designed for the developers and administrators who work with Microsoft SQL Server Analysis Services 2005 (SSAS). Here you will be able to find latest news, MDX FAQs, OLAP design FAQs, links to articles, BI blogs, webcasts, forums, job postings, products and any other information that is related to SSAS. As the registered user you can submit your own links or articles. They will appear on our site after website administrators approval (usually the same day). We also encourage you to subscribe to our newsletter that will keep you informed about latest additions to our website.
Source: Info about SSAS - Home
