How-To Tutorials

The main part of this article is dedicated to a library called VectorVision that was incorporated into Papervision3D. After discussing the classes of this library, we will take a look at the Lines3D class in the next part that also enables you to draw 3D lines. This class was already a part of Papervision3D before VectorVision was incorporated. VectorVision: 3D vector text and drawing VectorVision is a library written in ActionScript that allows you to render vector graphics in Papervision3D and add a 3D perspective to them. The project started as a separate library that you could download and use as an add-on. However, it was fully integrated in Papervision3D in June 2008. Being able to use vector shapes and text theoretically means that you could draw any kind of vector graphic and give it a 3D perspective. This article will focus on the features that are implemented in Papervision3D: Creating 3D vector text Drawing 3D vector shapes such as lines, circles, and rectangles Keep in mind that 3D letters can be seen as vector shapes too, just like lines, circles, and rectangles. The above distinction is made based on how VectorVision is implemented in Papervision3D. Some classes specifically deal with creating 3D text, whereas others enable you to create vector shapes. Creating a template class for the 3D text examples Because the 3D text examples we are about to see have a lot in common, we will use a template class that looks as follows: package{ import flash.events.Event; import org.papervision3d.materials.special.Letter3DMaterial; import org.papervision3d.typography.Font3D; import org.papervision3d.typography.Text3D; import org.papervision3d.typography.fonts.HelveticaBold; import org.papervision3d.view.BasicView; public class Text3DTemplate extends BasicView { private var material:Letter3DMaterial; private var font3D:Font3D; private var text3D:Text3D; private var easeOut:Number = 0.6; private var reachX:Number = 0.5 private var reachY:Number = 0.5 private var reachZ:Number = 0.5; public function Text3DTemplate() { stage.frameRate = 40; init(); startRendering(); } private function init():void { //code to be added } override protected function onRenderTick(event:Event = null):void { var xDist:Number = mouseX - stage.stageWidth * 0.5; var yDist:Number = mouseY - stage.stageHeight * 0.5; camera.x += (xDist - camera.x * reachX) * easeOut; camera.y += (yDist - camera.y * reachY) * easeOut; camera.z += (-mouseY * 2 - camera.z ) * reachZ; super.onRenderTick(); } }} We added some class properties that are used in the render method, where we added code to move the camera when the mouse moves. Also, we imported four classes and added three class properties that will enable us to create 3D text. How to create and add 3D text Let's see how we can create 3D vector text that looks crisp and clear. The general process of creating and displaying 3D text looks as follows: Create material with Letter3DMaterial. Create a Font3D instance. Create a Text3D instance, passing the text, font, and material to it, and add it to the scene or to another do3D. We will create an example that demonstrates several features of Text3D: Multiline Alignment Outlines All the following code should be added inside the init() method. Before we instantiate the classes that we need in order to display 3D text, we assign a text string to a local variable. var text:String = "Multiline 3D textnwith letter spacing,nline spacing,nand alignment ;-)"; Now, let's create a text material, font, and text. First we instantiate Letter3DMaterial, which resides in the org.papervision3d.materials.special package: material = new Letter3DMaterial(0x000000); The constructor of this class takes two optional parameters:

Generally, jobs run immediately upon being enabled, or when we call the run_job procedure of the dbms_scheduler package. Many jobs are time-based; they are controlled by a schedule based on some kind of calendar. However, not everything in real life can be controlled by a calendar. Many things need an action on an ad hoc basis, depending on the occurrence of some other thing. This is called event-based scheduling. Events also exist as the outcome of a job. We can define a job to raise an event in several ways—when it ends, or when it ends in an error, or when it does not end within the expected runtime. Let's start with creating job events in order to make job monitoring a lot easier for you. Monitoring job events Most of the time when jobs just do their work as expected, there is not much to monitor. In most cases, the job controller has to fix application-specific problems (for example, sometimes file systems or table spaces get filled up). To make this easier, we can incorporate events. We can make jobs raise events when something unexpected happens, and we can have the Scheduler generate events when a job runs for too long. This gives us tremendous power. We can also use this to make chains a little easier to maintain. Events in chains A chain consists of steps that depend on each other. In many cases, it does not make sense to continue to step 2 when step 1 fails. For example, when a create table fails, why try to load data into the nonexistent table? So it is logical to terminate the job if no other independent steps can be performed. One of the ways to handle this is implementing error steps in the chain. This might be a good idea, but the disadvantage is that this quickly doubles the steps involved in the chain, where most of the steps—hopefully—will not be executed. Another disadvantage is that the chain becomes less maintainable. It's a lot of extra code, and more code (mostly) gives us less oversight. If a job chain has to be terminated because of a failure, using the option of creating an event handler to raise a Scheduler event is recommended instead of adding extra steps that try to tell which error possibly happened. This makes event notification a lot simpler because it's all in separate code and not mixed up with the application code. Another situation is when the application logic has to take care of steps that fail, and has well-defined countermeasures to be executed that make the total outcome of the job a success. An example is a situation that starts with a test for the existence of a fi le. If the test fails, get it by FTP; and if this succeeds, load it into the database. In this case, the first step can fail and go to the step that gets the file. As there is no other action possible when the FTP action fails, this should raise a Scheduler event that triggers—for example—a notification action. The same should happen when the load fails. In other third-party scheduling packages, I have seen these notification actions implemented as part of the chain definitions because they lack a Scheduler event queue. In such packages, messages are sent by mail in extra chain steps. In the Oracle Scheduler, this queue is present and is very useful for us. Compared to 10g, nothing has changed in 11g. An event monitoring package can de-queue from the SCHEDULER$_EVENT_QUEUE variable into a sys.scheduler$_event_info type variable. The definition is shown in the following screenshot: What you can do with an event handler is up to your imagination. The following DB Console screenshot shows the interface that can be used to specify which events to raise: It is easy to generate an event for every possible event listed above and have the handler decide (by the rules defined in tables) what to do. This does sound a little creepy, but it is not very different from having a table that can be used as a lookup for the job found in the event message where—most of the time—a notification mail is sent, or not sent. Sometimes, a user wants to get a message when a job starts running; and most of the time, they want a message when a job ends. In a chain, it is especially important to be able to tell in which step the event happened and what that step was supposed to do. In the event message, only the job name is present and so you have to search a bit to find the name of the step that failed. For this, we can use the LOG_ID to find the step name in the SCHEDULER_JOB_LOGS (user/dba/all_SCHEDULER_JOB_LOG) view, where the step name is listed as JOB_SUBNAME. The following query can be used to find the step_name from the dba all_scheduler_log view, assuming that the event message is received in msg: select job_subname from all_scheduler_job_log wherelog_id = msg.log_id; To enable the delivery of all the events a job can generate, we can set the raise_events attribute to a value of: dbms_scheduler.job_started + dbms_scheduler.job_succeeded +dbms_scheduler.job_failed + dbms_scheduler.job_broken +dbms_scheduler.job_completed + dbms_scheduler.job_stopped +dbms_scheduler.job_sch_lim_reached + dbms_scheduler.job_disabled +dbms_scheduler.job_chain_stalled Or in short, we can set it to: dbms_scheduler.job_all_events. There are many things that can be called events. In the job system, there are basically two types of events: events caused by jobs (which we already discussed) and events that makes a job execute.

JClic (http://clic.xtec.net/en) is a free (under a GPL license) software application released by the Ministry of Education of the Government of Catalunya. It is written in Java, and allows us to create the following seven types of interactive activities: Association games - to identify the relationship between two groups of data Memory games - to discover hidden pairs of elements Exploring, Identifying, and Information games - to start with initial information and choose paths to the answer Puzzles – to order graphics, text, and audio, or to combine graphics and audio Written answers – to write text, a word, or a sentence Text activities – to solve exercises based on words, sentences, letters, and paragraphs (these can be completed, corrected, or ordered) Wordsearches and crosswords – to find hidden words or solve crossword puzzles JClic exercises can be more visually appealing than Hot Potatoes, as we will see, and can be particularly useful for younger students. But, as they require Java, this should be checked with the ICT coordinator as Java must be installed on the schools' PCs. In the software download area (http://clic.edu365.cat/en/jclic/download.htm), we can download JClic author, the application that allows us to create these activities. The file will use WebStart, and will run from a single file, named jclic.jnlp. When we run it for the first time, in Microsoft Vista at least, we will need to give permission for the application to Run (selecting the Always trust content from this publisher option will avoid having to perform this step every time we start JClic): Then JClic will start loading: The interface of JClic author is as shown in the following screenshot: As it can be seen, there are four available tabs: Project – the default tab, which allows us to define some details of the project. Media library – where pictures and other multimedia are managed. Activities – where the project activities are created or modified. This tab further contains four tabs. Sequences – where we can sequence several activities in the same project. The options inside these tabs will be available only after we create a new project. Start a new project The first step in building interactive JClic activities is to start a new project (via menu option File | New project): We should then define: The name of the project The name of the file in which the project will be saved (having a double extension of .jclic.zip) The default folder for saving the files to is:C:/Programme Files/JClic/projects/name of project (in Windows)$home/JClic/projects/name of project (in other OSs) We can change this and, if we are using multimedia files, again we should keep everything organized inside this folder. Creating a puzzle activity We are now ready to start creating our first activity, a puzzle. In Module 2 - A world of music—we can pick some of the pictures of instruments that our students gathered in the Instrument Mappers activities and create a jigsaw puzzle as part of a final game for the module. We will perform the following steps: Provide details of the project in the Project tab. Import a picture to the Media library. Add an activity called Exchangeable puzzle. Create a sequence. Note that we are starting from the tab on the left and moving to the right as we configure the activity. As an example, I created a project called Instruments: Next, I added a description of the activity and specified myself as an author by clicking on the plus (+) button. We can specify in more details, but for now this much information is enough as an example. Now, let's import a picture into our Media library by clicking on the icon on the far left on the toolbar: If we pick a picture from any folder on our computer, JClic will recommend that this be copied to the project folder (we should accept this recommendation, especially if we want to upload our activity to Moodle). Note that the Media library accepts different kinds of multimedia files, from MP3 to Flash and video. This can be useful in other types of activities. We now have a picture of a lamelaphone that will make a difficult jigsaw for our students. Lamelaphone image source: Weeks, Alex (2006). Mbira dzavadzimu 1.jpg. Retrieved October 12, 2008,from http://commons.wikimedia.org/wiki/File:Mbira_dzavadzimu_1.jpg The next step is to add the puzzle activity, on the Activities tab, by clicking on the icon on the far left of the toolbar: A dialog box is displayed, and in this menu we should select the Exchange puzzle option, entering a name for our puzzle, in the input field at the bottom of the dialog box: We can then add a description of the activity, and if needed, we can define a timer countdown (in the Counters section), among other options: Reports are mentioned in this dialog box. JClic provides a way to gather students' responses, but due to the complexity of this functionality, we will not deal with it in this book. In the Window tab, on the the Activities tab, we can also define some color options, as shown in the screenshot below: In the Messages tab, we can add an initial message, which for example, gives the context of the activity, and a final message, as feedback for the exercise by clicking on the dark gray areas: Finally, in the Panel tab, we should insert the lamelaphone picture from our Media library and define the kind of jigsaw that we want. In the following screenshot, I have done the following three things: Selected a jigsaw with curved unions. Defined 5x5 pieces. Selected the image from the Media library. Our puzzle activity is now ready, and we can now add a finding pairs activity to the same project, in a sequence.

MDX calculation performance Optimizing the performance of the Storage Engine is relatively straightforward: you can diagnose performance problems easily and you only have two options—partitioning and aggregation—for solving them. Optimizing the performance of the Formula Engine is much more complicated because it requires knowledge of MDX, diagnosing performance problems is difficult because the internal workings of the Formula Engine are hard to follow, and solving the problem is reliant on knowing tips and tricks that may change from service pack to service pack. Diagnosing Formula Engine performance problems If you have a poorly-performing query, and if you can rule out the Storage Engine as the cause of the problem, then the issue is with the Formula Engine. We've already seen how we can use Profiler to check the performance of Query Subcube events, to see which partitions are being hit and to check whether aggregations are being used; if you subtract the sum of the durations of all the Query Subcube events from the duration of the query as a whole, you'll get the amount of time spent in the Formula Engine. You can use MDX Studio's Profile functionality to do the same thing much more easily—here's a screenshot of what it outputs when a calculation-heavy query is run: The following blog entry describes this functionality in detail: http://tinyurl.com/mdxtrace; but what this screenshot displays is essentially the same thing that we'd see if we ran a Profiler trace when running the same query on a cold and warm cache, but in a much more easy-to-read format. The column to look at here is the Ratio to Total, which shows the ratio of the duration of each event to the total duration of the query. We can see that on both a cold cache and a warm cache the query took almost ten seconds to run but none of the events recorded took anywhere near that amount of time: the highest ratio to parent is 0.09%. This is typical of what you'd see with a Formula Engine-bound query. Another hallmark of a query that spends most of its time in the Formula Engine is that it will only use one CPU, even on a multiple-CPU server. This is because the Formula Engine, unlike the Storage Engine, is single-threaded. As a result if you watch CPU usage in Task Manager while you run a query you can get a good idea of what's happening internally: high usage of multiple CPUs indicates work is taking place in the Storage Engine, while high usage of one CPU indicates work is taking place in the Formula Engine. Calculation performance tuning Having worked out that the Formula Engine is the cause of a query's poor performance then the next step is, obviously, to try to tune the query. In some cases you can achieve impressive performance gains (sometimes of several hundred percent) simply by rewriting a query and the calculations it depends on; the problem is knowing how to rewrite the MDX and working out which calculations contribute most to the overall query duration. Unfortunately Analysis Services doesn't give you much information to use to solve this problem and there are very few tools out there which can help either, so doing this is something of a black art. There are three main ways you can improve the performance of the Formula Engine: tune the structure of the cube it's running on, tune the algorithms you're using in your MDX, and tune the implementation of those algorithms so they use functions and expressions that Analysis Services can run efficiently. We've already talked in depth about how the overall cube structure is important for the performance of the Storage Engine and the same goes for the Formula Engine; the only thing to repeat here is the recommendation that if you can avoid doing a calculation in MDX by doing it at an earlier stage, for example in your ETL or in your relational source, and do so without compromising functionality, you should do so. We'll now go into more detail about tuning algorithms and implementations. Mosha Pasumansky's blog, http://tinyurl.com/moshablog, is a goldmine of information on this subject. If you're serious about learning MDX we recommend that you subscribe to it and read everything he's ever written. Tuning algorithms used in MDX Tuning an algorithm in MDX is much the same as tuning an algorithm in any other kind of programming language—it's more a matter of understanding your problem and working out the logic that provides the most efficient solution than anything else. That said, there are some general techniques that can be used often in MDX and which we will walk through here. Using named sets to avoid recalculating set expressions Many MDX calculations involve expensive set operations, a good example being rank calculations where the position of a tuple within an ordered set needs to be determined. The following query includes a calculated member that displays Dates on the Rows axis of a query, and on columns shows a calculated measure that returns the rank of that date within the set of all dates based on the value of the Internet Sales Amount measure: WITH MEMBER MEASURES.MYRANK AS Rank ( [Date].[Date].CurrentMember ,Order ( [Date].[Date].[Date].MEMBERS ,[Measures].[Internet Sales Amount] ,BDESC ) )SELECT MEASURES.MYRANK ON 0 ,[Date].[Date].[Date].MEMBERS ON 1 FROM [Adventure Works] It runs very slowly, and the problem is that every time the calculation is evaluated it has to evaluate the Order function to return the set of ordered dates. In this particular situation, though, you can probably see that the set returned will be the same every time the calculation is called, so it makes no sense to do the ordering more than once. Instead, we can create a named set hold the ordered set and refer to that named set from within the calculated measure, so: WITH SET ORDEREDDATES AS Order ( [Date].[Date].[Date].MEMBERS ,[Measures].[Internet Sales Amount] ,BDESC ) MEMBER MEASURES.MYRANK AS Rank ( [Date].[Date].CurrentMember ,ORDEREDDATES ) SELECT MEASURES.MYRANK ON 0 ,[Date].[Date].[Date].MEMBERS ON 1 FROM [Adventure Works] This version of the query is many times faster, simply as a result of improving the algorithm used; the problem is explored in more depth in this blog entry: http://tinyurl.com/mosharank Since normal named sets are only evaluated once they can be used to cache set expressions in some circumstances; however, the fact that they are static means they can be too inflexible to be useful most of the time. Note that normal named sets defined in the MDX Script are only evaluated once, when the MDX script executes and not in the context of any particular query, so it wouldn't be possible to change the example above so that the set and calculated measure were defined on the server. Even named sets defined in the WITH clause are evaluated only once, in the context of the WHERE clause, so it wouldn't be possible to crossjoin another hierarchy on columns and use this approach, because for it to work the set would have to be reordered once for each column. The introduction of dynamic named sets in Analysis Services 2008 improves the situation a little, and other more advanced techniques can be used to work around these issues, but in general named sets are less useful than you might hope. For further reading on this subject see the following blog posts: http://tinyurl.com/chrisrankhttp://tinyurl.com/moshadsetshttp://tinyurl.com/chrisdsets Using calculated members to cache numeric values In the same way that you can avoid unnecessary re-evaluations of set expressions by using named sets, you can also rely on the fact that the Formula Engine can (usually) cache the result of a calculated member to avoid recalculating expressions which return numeric values. What this means in practice is that anywhere in your code you see an MDX expression that returns a numeric value repeated across multiple calculations, you should consider abstracting it to its own calculated member; not only will this help performance, but it will improve the readability of your code. For example, take the following slow query which includes two calculated measures: WITH MEMBER [Measures].TEST1 AS [Measures].[Internet Sales Amount] / Count ( TopPercent ( { [Scenario].[Scenario].&[1] ,[Scenario].[Scenario].&[2] }* [Account].[Account].[Account].MEMBERS* [Date].[Date].[Date].MEMBERS ,10 ,[Measures].[Amount] ) )MEMBER [Measures].TEST2 AS [Measures].[Internet Tax Amount] / Count ( TopPercent ( { [Scenario].[Scenario].&[1] ,[Scenario].[Scenario].&[2] }* [Account].[Account].[Account].MEMBERS* [Date].[Date].[Date].MEMBERS* [Department].[Departments].[Department Level 02].MEMBERS ,10 ,[Measures].[Amount] ) )SELECT { [Measures].TEST1 ,[Measures].TEST2 } ON 0 ,[Customer].[Gender].[Gender].MEMBERS ON 1FROM [Adventure Works] A quick glance over the code shows that a large section of it occurs twice in both calculations—everything inside the Count function. If we remove that code to its own calculated member as follows: WITH MEMBER [Measures].Denominator AS Count ( TopPercent ( { [Scenario].[Scenario].&[1] ,[Scenario].[Scenario].&[2] }* [Account].[Account].[Account].MEMBERS* [Date].[Date].[Date].MEMBERS ,10 ,[Measures].[Amount] ) )MEMBER [Measures].TEST1 AS [Measures].[Internet Sales Amount] / [Measures].DenominatorMEMBER [Measures].TEST2 AS [Measures].[Internet Tax Amount] / [Measures].DenominatorSELECT { [Measures].TEST1 ,[Measures].TEST2 } ON 0 ,[Customer].[Gender].[Gender].MEMBERS ON 1FROM [Adventure Works] The query runs much faster, simply because instead of evaluating the count twice for each of the two visible calculated measures, we evaluate it once, cache the result in the calculated measure Denominator and then reference this in the other calculated measures. It's also possible to find situations where you can rewrite code to avoid evaluating a calculation that always returns the same result over different cells in the multidimensional space of the cube. This is much more difficult to do effectively though; the following blog entry describes how to do it in detail: http://tinyurl.com/fecache Tuning the implementation of MDX Like just about any other software product, Analysis Services is able to do some things more efficiently than others. It's possible to write the same query or calculation using the same algorithm but using different MDX functions and see a big difference in performance; as a result, we need to know which are the functions we should use and which ones we should avoid. Which ones are these though? Luckily MDX Studio includes functionality to analyse MDX code and flag up such problems—to do this you just need to click the Analyze button—and there's even an online version of MDX Studio that allows you to do this too, available at: http://mdx.mosha.com/. We recommend that you run any MDX code you write through this functionality and take its suggestions on board. Mosha walks through an example of using MDX Studio to optimise a calculation on his blog here: http://tinyurl.com/moshaprodvol Block computation versus cell-by-cellWhen the Formula Engine has to evaluate an MDX expression for a query it can basically do so in one of two ways. It can evaluate the expression for each cell returned by the query, one at a time, an evaluation mode known as "cell-by-cell"; or it can try to analyse the calculations required for the whole query and find situations where the same expression would need to be calculated for multiple cells and instead do it only once, an evaluation mode known variously as "block computation" or "bulk evaluation". Block computation is only possible in some situations, depending on how the code is written, but is often many times more efficient than cell-by-cell mode. As a result, we want to write MDX code in such a way that the Formula Engine can use block computation as much as possible, and when we talk about using efficient MDX functions or constructs then this is what we in fact mean. Given that different calculations in the same query, and different expressions within the same calculation, can be evaluated using block computation and cell-by-cell mode, it’s very difficult to know which mode is used when. Indeed in some cases Analysis Services can’t use block mode anyway, so it’s hard know whether we have written our MDX in the most efficient way possible. One of the few indicators we have is the Perfmon counter MDXTotal Cells Calculated, which basically returns the number of cells in a query that were calculated in cell-by-cell mode; if a change to your MDX increments this value by a smaller amount than before, and the query runs faster, you're doing something right. The list of rules that MDX Studio applies is too long to list here, and in any case it is liable to change in future service packs or versions; another good guide for Analysis Services 2008 best practices exists in the Books Online topic Performance Improvements for MDX in SQL Server 2008 Analysis Services, available online here: http://tinyurl.com/mdximp. However, there are a few general rules that are worth highlighting: Don't use the Non_Empty_Behavior calculation property in Analysis Services 2008, unless you really know how to set it and are sure that it will provide a performance benefit. It was widely misused with Analysis Services 2005 and most of the work that went into the Formula Engine for Analysis Services 2008 was to ensure that it wouldn't need to be set for most calculations. This is something that needs to be checked if you're migrating an Analysis Services 2005 cube to 2008. Never use late binding functions such as LookupCube, or StrToMember or StrToSet without the Constrained flag, inside calculations since they have a serious negative impact on performance. It's almost always possible to rewrite calculations so they don't need to be used; in fact, the only valid use for StrToMember or StrToSet in production code is when using MDX parameters. The LinkMember function suffers from a similar problem but is less easy to avoid using it. Use the NonEmpty function wherever possible; it can be much more efficient than using the Filter function or other methods. Never use NonEmptyCrossjoin either: it's deprecated, and everything you can do with it you can do more easily and reliably with NonEmpty. Lastly, don't assume that whatever worked best for Analysis Services 2000 or 2005 is still best practice for Analysis Services 2008. In general, you should always try to write the simplest MDX code possible initially, and then only change it when you find performance is unacceptable. Many of the tricks that existed to optimise common calculations for earlier versions now perform worse on Analysis Services 2008 than the straightforward approaches they were designed to replace. Caching We've already seen how Analysis Services can cache the values returned in the cells of a query, and how this can have a significant impact on the performance of a query. Both the Formula Engine and the Storage Engine can cache data, but may not be able to do so in all circumstances; similarly, although Analysis Services can share the contents of the cache between users there are several situations where it is unable to do so. Given that in most cubes there will be a lot of overlap in the data that users are querying, caching is a very important factor in the overall performance of the cube and as a result ensuring that as much caching as possible is taking place is a good idea. Formula cache scopes There are three different cache contexts within the Formula Engine, which relate to how long data can be stored within the cache and how that data can be shared between users: Query Context, which means that the results of calculations can only be cached for the lifetime of a single query and so cannot be reused by subsequent queries or by other users. Session Context, which means the results of calculations are cached for the lifetime of a session and can be reused by subsequent queries in the same session by the same user. Global Context, which means the results of calculations are cached until the cache has to be dropped because data in the cube has changed (usually when some form of processing takes place on the server). These cached values can be reused by subsequent queries run by other users as well as the user who ran the original query. Clearly the Global Context is the best from a performance point of view, followed by the Session Context and then the Query Context; Analysis Services will always try to use the Global Context wherever possible, but it is all too easy to accidentally write queries or calculations that force the use of the Session Context or the Query Context. Here's a list of the most important situations when that can happen: If you define any calculations (not including named sets) in the WITH clause of a query, even if you do not use them, then Analysis Services can only use the Query Context (see http://tinyurl.com/chrisfecache for more details). If you define session-scoped calculations but do not define calculations in the WITH clause, then the Session Context must be used. Using a subselect in a query will force the use of the Query Context (see http://tinyurl.com/chrissubfe). Use of the CREATE SUBCUBE statement will force the use of the Session Context. When a user connects to a cube using a role that uses cell security, then the Query Context will be used. When calculations are used that contain non-deterministic functions (functions which could return different results each time they are called), for example the Now() function that returns the system date and time, the Username() function or any Analysis Services stored procedure, then this forces the use of the Query Context. Other scenarios that restrict caching Apart from the restrictions imposed by cache context, there are other scenarios where caching is either turned off or restricted. When arbitrary-shaped sets are used in the WHERE clause of a query, no caching at all can take place in either the Storage Engine or the Formula Engine. An arbitrary-shaped set is a set of tuples that cannot be created by a crossjoin, for example: ({([Customer].[Country].&[Australia], [Product].[Category].&[1]),([Customer].[Country].&[Canada], [Product].[Category].&[3])}) If your users frequently run queries that use arbitrary-shaped sets then this can represent a very serious problem, and you should consider redesigning your cube to avoid it. The following blog entries discuss this problem in more detail: http://tinyurl.com/tkarbsethttp://tinyurl.com/chrisarbset Even within the Global Context, the presence of security can affect the extent to which cache can be shared between users. When dimension security is used the contents of the Formula Engine cache can only be shared between users who are members of roles which have the same permissions. Worse, the contents of the Formula Engine cache cannot be shared between users who are members of roles which use dynamic security at all, even if those users do in fact share the same permissions. Cache warming Since we can expect many of our queries to run instantaneously on a warm cache, and the majority at least to run faster on a warm cache than on a cold cache, it makes sense to preload the cache with data so that when users come to run their queries they will get warm-cache performance. There are two basic ways of doing this, running CREATE CACHE statements and automatically running batches of queries. Create Cache statement The CREATE CACHE statement allows you to load a specified subcube of data into the Storage Engine cache. Here's an example of what it looks like: CREATE CACHE FOR [Adventure Works] AS({[Measures].[Internet Sales Amount]}, [Customer].[Country].[Country].MEMBERS,[Date].[Calendar Year].[Calendar Year].MEMBERS) More detail on this statement can be found here: http://tinyurl.com/createcache CREATE CACHE statements can be added to the MDX Script of the cube so they execute every time the MDX Script is executed, although if the statements take a long time to execute (as they often do) then this might not be a good idea; they can also be run after processing has finished from an Integration Services package using an Execute SQL task or through ASCMD, and this is a much better option because it means you have much more control over when the statements actually execute—you wouldn't want them running every time you cleared the cache, for instance. Running batches of queries The main drawback of the CREATE CACHE statement is that it can only be used to populate the Storage Engine cache, and in many cases it's warming the Formula Engine cache that makes the biggest difference to query performance. The only way to do this is to find a way to automate the execution of large batches of MDX queries (potentially captured by running a Profiler trace while users go about their work) that return the results of calculations and so which will warm the Formula Engine cache. This automation can be done in a number of ways, for example by using the ASCMD command line utility which is part of the sample code for Analysis Services that Microsoft provides (available for download here: http://tinyurl.com/sqlprodsamples); another common option is to use an Integration Services package to run the queries, as described in the following blog entries— http://tinyurl.com/chriscachewarm and http://tinyurl.com/allancachewarm This approach is not without its own problems, though: it can be very difficult to make sure that the queries you're running return all the data you want to load into cache, and even when you have done that, user query patterns change over time so ongoing maintenance of the set of queries is important. Scale-up and scale-out Buying better or more hardware should be your last resort when trying to solve query performance problems: it's expensive and you need to be completely sure that it will indeed improve matters. Adding more memory will increase the space available for caching but nothing else; adding more or faster CPUs will lead to faster queries but you might be better off investing time in building more aggregations or tuning your MDX. Scaling up as much as your hardware budget allows is a good idea, but may have little impact on the performance of individual problem queries unless you badly under-specified your Analysis Services server in the first place. If your query performance degenerates as the number of concurrent users running queries increases, consider scaling-out by implementing what's known as an OLAP farm. This architecture is widely used in large implementations and involves multiple Analysis Services instances on different servers, and using network load balancing to distribute user queries between these servers. Each of these instances needs to have the same database on it and each of these databases must contain exactly the same data in it for queries to be answered consistently. This means that, as the number of concurrent users increases, you can easily add new servers to handle the increased query load. It also has the added advantage of removing a single point of failure, so if one Analysis Services server fails then the others take on its load automatically. Making sure that data is the same across all servers is a complex operation and you have a number of different options for doing this: you can either use the Analysis Services database synchronisation functionality, copy and paste the data from one location to another using a tool like Robocopy, or use the new Analysis Services 2008 shared scalable database functionality. The following white paper from the SQLCat team describes how the first two options can be used to implement a network load-balanced solution for Analysis Services 2005: http://tinyurl.com/ssasnlb. Shared scalable databases have a significant advantage over synchronisation and file-copying in that they don't need to involve any moving of files at all. They can be implemented using the same approach described in the white paper above, but instead of copying the databases between instances you process a database (attached in ReadWrite mode) on one server, detach it from there, and then attach it in ReadOnly mode to one or more user-facing servers for querying while the files themselves stay in one place. You do, however, have to ensure that your disk subsystem does not become a bottleneck as a result. Summary In this article we covered MDX calculation performance and caching, and also how to write MDX to ensure that the Formula Engine works as efficiently as possible. We've also seen how important caching is to overall query performance and what we need to do to ensure that we can cache data as often as possible, and we've discussed how to scale-out Analysis Services using network load balancing to handle large numbers of concurrent users.

Integration with the Spring Framework Spring is one of the most complete lightweight containers, which provides centralized, automated configuration, and wiring of your application objects. It improves your application's testability and scalability by allowing software components to be first developed and tested in isolation, then scaled up for deployment in any environment. This approach is called the POJO (Plain Old Java Object) approach and is gaining popularity because of its flexibility. So, with all these advantages, it's no wonder that Spring is one of the most used frameworks. Spring provides many nice features: however, it works mainly in the back end. Here ZK may provide support in the view layer. The benefit from this pairing is the flexible and maturity of Spring together with the easy and speed of ZK. Specify a Java class in the use attribute of a window ZUL page and the world of Spring will be yours. A sample ZUL looks like: <?xml version="1.0" encoding="UTF-8"?> <p:window xsi_schemaLocation="http://www.zkoss.org/2005/zul http://www.zkoss.org/2005/zul " border="normal" title="Hello!" use="com.myfoo.myapp.HelloController"> Thank you for using our Hello World Application. </p:window> The HelloController points directly to a Java class where you can use Spring features easily. Normally, if a Java Controller is used for a ZUL page it becomes necessary sooner or later to call a Spring bean. Usually in Spring you would use the applicationContext like: ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); UserDAO userDAO = (UserDAO) ctx.getBean("userDAO"); Then the userDAO is usable for any further access. In ZK there is a helper class SpringUtil. It wrapps the applicationContext and simplifies the code to: UserDAO userDAO = (UserDAO) SpringUtil.getBean("userDAO"); Pretty easy, isn't it? Let us examine an example. Assume we have a small web application that gets flight data from a flight table. The web.xml file looks like: <?xml version="1.0" encoding="UTF-8"?> <web-app version="2.4" xsi_schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"> <display-name>IRT-FLIGHTSAMPLE</display-name> <filter> <filter-name>hibernateFilter</filter-name> <filter-class> org.springframework.orm.hibernate3.support.OpenSessionInViewFilter </filter-class> </filter> <filter-mapping> <filter-name> hibernateFilter </filter-name> <url-pattern>/*</url-pattern> </filter-mapping> <context-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:applicationContext-jdbc.xml ,classpath:applicationContext-dao.xml ,classpath:applicationContext-service.xml ,classpath:applicationContext.xml </param-value> </context-param> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener </listener-class> </listener> <session-config> <!-- Default to 30 minute session timeouts --> <session-timeout>30</session-timeout> </session-config> <mime-mapping> <extension>xsd</extension> <mime-type>text/xml</mime-type> </mime-mapping> <servlet> <description> <![CDATA[The servlet loads the DSP pages.]]> </description> <servlet-name>dspLoader</servlet-name> <servlet-class> org.zkoss.web.servlet.dsp.InterpreterServlet </servlet-class> </servlet> <servlet-mapping> <servlet-name>dspLoader</servlet-name> <url-pattern>*.dsp</url-pattern> </servlet-mapping> <!-- ZK --> <listener> <description> Used to cleanup when a session is destroyed </description> <display-name>ZK Session Cleaner</display-name> <listener-class> org.zkoss.zk.ui.http.HttpSessionListener </listener-class> </listener> <servlet> <description>ZK loader for ZUML pages</description> <servlet-name>zkLoader</servlet-name> <servlet-class> org.zkoss.zk.ui.http.DHtmlLayoutServlet </servlet-class> <!-- Must. Specifies URI of the update engine (DHtmlUpdateServlet). It must be the same as <url-pattern> for the update engine. --> <init-param> <param-name>update-uri</param-name> <param-value>/zkau</param-value> </init-param> <load-on-startup>1</load-on-startup> </servlet> <servlet-mapping> <servlet-name>zkLoader</servlet-name> <url-pattern>*.zul</url-pattern> </servlet-mapping> <servlet-mapping> <servlet-name>zkLoader</servlet-name> <url-pattern>*.zhtml</url-pattern> </servlet-mapping> <servlet> <description>The asynchronous update engine for ZK </description> <servlet-name>auEngine</servlet-name> <servlet-class> org.zkoss.zk.au.http.DHtmlUpdateServlet </servlet-class> </servlet> <servlet-mapping> <servlet-name>auEngine</servlet-name> <url-pattern>/zkau/*</url-pattern> </servlet-mapping> <welcome-file-list id="WelcomeFileList"> <welcome-file>index.zul</welcome-file> </welcome-file-list> </web-app> Furthermore let's have a small ZUL page that has the interface to retrieve and show flight data: <?xml version="1.0" encoding="UTF-8"?> <p:window xsi_schemaLocation="http://www.zkoss.org/2005/zul http://www.zkoss.org/2005/zul " id="query" use="com.myfoo.controller.SampleController"> <p:grid> <p:rows> <p:row> Airline Code: <p:textbox id="airlinecode"/> </p:row> <p:row> Flightnumber: <p:textbox id="flightnumber"/> </p:row> <p:row> Flightdate: <p:datebox id="flightdate"/> </p:row> <p:row> <p:button label="Search" id="search"/> <p:separator width="5px"/> </p:row> </p:rows> </p:grid> <p:listbox width="100%" id="resultlist" mold="paging" rows="21" style="font-size: x-small;"> <p:listhead sizable="true"> <p:listheader label="Airline Code" sort="auto" style="font-size: x-small;"/> <p:listheader label="Flightnumber" sort="auto" style="font-size: x-small;"/> <p:listheader label="Flightdate" sort="auto" style="font-size: x-small;"/> <p:listheader label="Destination" sort="auto" style="font-size: x-small;"/> </p:listhead> </p:listbox> </p:window> As you can see, the use attribute of the ZUL page is the link to the SampleController. The SampleController handles and controls the objects. Let's have a short look at the SampleController sample code: public class SampleController extends Window { private Listbox resultlist; private Textbox airlinecode; private Textbox flightnumber; private Datebox flightdate; private Button search; /** * Initialize the page */ public void onCreate() { // Components resultlist = (Listbox) this.getPage().getFellow("query").getFellow("resultlist"); airlinecode = (Textbox) this.getPage().getFellow("query").getFellow("airlinecode"); flightnumber = (Textbox) this.getPage().getFellow("query").getFellow("flightnumber"); flightdate = (Datebox) this.getPage().getFellow("query").getFellow("flightdate"); search = (Button) this.getPage().getFellow("query").getFellow("search"); search.addEventListener("onClick", new EventListener() { public void onEvent(Event event) throws Exception { performSearch(); } }); } /** * Execute the search and fill the list */ private void performSearch() { //(1) List<Flight> flightlist = ((FlightService) SpringUtil.getBean("flightService")). getFlightBySearch(airlinecode.getValue(), flightnumber.getValue(), flightdate.getValue(),""); resultlist.getItems().clear(); for (Flight aFlightlist : flightlist) { // add flights to list } } } /* (1)-shows the integration of the Spring Bean*/ Just for completion the context file for Spring is listed here with the bean that is called. <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd"> <beans> <bean id="txProxyTemplate" abstract="true" class="org.springframework.transaction. interceptor.TransactionProxyFactoryBean"> <property name="transactionManager"> <ref bean="transactionManager"/> </property> <property name="transactionAttributes"> <props> <prop key="save*">PROPAGATION_REQUIRED</prop> <prop key="add*">PROPAGATION_REQUIRED</prop> <prop key="remove*">PROPAGATION_REQUIRED</prop> </props> </property> </bean> <bean id="flightService" parent="txProxyTemplate"> <property name="target"> <bean class="com.myfoo.services.impl.FlightServiceImpl"> <property name="flightDAO"> <ref bean="flightDao"/> </property> </bean> </property> </bean> </beans> In short we have learned how to use Spring with ZK and about the configurations. We have seen that the integration is quite smooth and also powerful.

  Editing a Quiz Immediately after saving the Settings page, you are taken to the Editing Quiz page. This page is divided into five tabs. Each tab enables you to edit a different aspect of the quiz. This tab... Enables you to... Quiz Add questions to the quiz. Remove questions from the quiz. Arrange the questions in order. Create page breaks between questions. Assign a point value to each question. Assign a maximum point value to the quiz. Click into the editing page for each question. Questions Create a new question. Note that you must then add the new question to the quiz under the Quiz tab (see above). Also note that every question must belong to a category. Delete a question, not just from the quiz but from your site's question bank. Move a question from one category to another category. Click into the editing page for each question. Click into the editing page for each category. Categories Arrange the list of categories in order. Nest a category under another category (they become parent and subcategories). Publish a category, so that questions in that category can be used by other courses on the site. Delete a category (you must choose a new category to move the questions in the deleted category). Import Import questions from other learning systems. Import questions that were exported from Moodle. Export Export questions from Moodle, and save them in a variety of formats that Moodle and other learning systems can understand.       Create and Edit Question Categories Every question belongs to a category. You manage question categories under the Categories tab. There will always be a Default category. But before you create new questions, you might want to check to ensure that you have an appropriate category in which to put them. The categories which you can manage are listed on this page. To Add a New Category To add a new category, first select its Parent. If you select Top, the category will be a top-level category. Or, you can select any other category to which you have access, and then the new category will be a child of the selected category. In the Category field, enter the name for the new category. In the Category Info field, enter a description of the new category. The Publish field determines whether other courses can use the questions in this category. Click the Add button. To Edit a Category Next to the category, click the icon. The Edit categories page is displayed. You can edit the Parent, Category name, Category Info, and Publish setting. When you are finished, click the Update button. Your changes are saved and you are returned to the Categories page. Managing the Proliferation of Questions and Categories As the site administrator, you might want to monitor the creation of new question categories to ensure that they are logically named, don't have a lot of overlap, and are appropriate for the purpose of your site. As these question and their categories are shared among course creators, they can be a powerful tool for collaboration. Consider using the site-wide Teachers forum to notify your teachers, and course creators of new questions and categories. Create and Manage Questions You create and manage questions under the Questions tab. The collection of questions in your site is called the Question bank. As a teacher or the course creator, you have access to some or all the questions in the question bank. When you create questions, you add them to your site's question bank. When you create a quiz, you choose questions from the question bank for the quiz. Both these functions can be done on the same Editing Quiz page. Pay attention to which part of the page you are using—the one for creating new questions or the one for drawing question from the question bank. Display Questions from the Bank You can display questions from one category at a time. To select that category, use the Category drop-down list. If a question is deleted when it is still being used by a quiz, then it is not removed from the question bank. Instead, the question is hidden. The setting Also show old questions enables you to see questions that were deleted from the category. These deleted, or hidden, or old questions appear in the list with a blue box next to them. To keep your question bank clean, and to prevent teachers from using deleted questions, you can move all the deleted questions into a category called Deleted questions. Create the category Deleted questions and then use Also show old questions to show the deleted questions. Select them, and move them into Deleted questions. Move Questions between Categories To move a question into a category, you must have access to the target category. This means that the target category must be published, so that the teachers in all the courses can see it. Select the question(s) to move, select the category, and then click the Move to>> button: Create a Question To create a new question, from the Create new question drop-down list, select the type for the next question: This brings you to the editing page for the question: After you save the question, it is added to the list of questions in that category: Question Types The following chart explains the types of questions you can create, and gives some tips for using them.  

In this two-part article by Chris Webb, we will cover query performance tuning, including how to design aggregations and partitions and how to write efficient MDX. The first part will cover performance-specific design features, along with the concepts of partitions and aggregations. One of the main reasons for building Analysis Services cubes as part of a BI solution is because it should mean you get better query performance than if you were querying your relational database directly. While it's certainly the case that Analysis Services is very fast it would be naive to think that all of our queries, however complex, will return in seconds without any tuning being necessary. This article will describe the steps you'll need to go through in order to ensure your cube is as responsive as possible. How Analysis Services processes queries Before we start to discuss how to improve query performance, we need to understand what happens inside Analysis Services when a query is run. The two major parts of the Analysis Services engine are: The Formula Engine processes MDX queries, works out what data is needed to answer them, requests that data from the Storage Engine, and then performs all calculations needed for the query. The Storage Engine handles all reading and writing of data; it fetches the data that the Formula Engine requests when a query is run and aggregates it to the required granularity. When you run an MDX query, then, that query goes first to the Formula Engine where it is parsed; the Formula Engine then requests all of the raw data needed to answer the query from the Storage Engine, performs any calculations on that data that are necessary, and then returns the results in a cellset back to the user. There are numerous opportunities for performance tuning at all stages of this process, as we'll see. Performance tuning methodology When doing performance tuning there are certain steps you should follow to allow you to measure the effect of any changes you make to your cube, its calculations or the query you're running: Wherever possible, test your queries in an environment that is identical to your production environment. Otherwise ensure that the size of the cube and the server hardware you're running on is at least comparable, and running the same build of Analysis Services. Make sure that no-one else has access to the server you're running your tests on. You won't get reliable results if someone else starts running queries at the same time as you. Make sure that the queries you're testing with are equivalent to the ones that your users want to have tuned. As we'll see, you can use Profiler to capture the exact queries your users are running against the cube. Whenever you test a query, run it twice: first on a cold cache, and then on a warm cache. Make sure you keep a note of the time each query takes to run and what you changed on the cube or in the query for that run. Clearing the cache is a very important step—queries that run for a long time on a cold cache may be instant on a warm cache. When you run a query against Analysis Services, some or all of the results of that query (and possibly other data in the cube, not required for the query) will be held in cache so that the next time a query is run that requests the same data it can be answered from cache much more quickly. To clear the cache of an Analysis Services database, you need to execute a ClearCache XMLA command. To do this in SQL Management Studio, open up a new XMLA query window and enter the following: <Batch > <ClearCache> <Object> <DatabaseID>Adventure Works DW 2008</DatabaseID> </Object> </ClearCache></Batch> Remember that the ID of a database may not be the same as its name —you can check this by right-clicking on a database in the SQL Management Studio Object Explorer and selecting Properties. Alternatives to this method also exist: the MDX Studio tool allows you to clear the cache with a menu option, and the Analysis Services Stored Procedure Project (http://tinyurl.com/asstoredproc) contains code that allows you to clear the Analysis Services cache and the Windows File System cache directly from MDX. Clearing the Windows File System cache is interesting because it allows you to compare the performance of the cube on a warm and cold file system cache as well as a warm and cold Analysis Services cache: when the Analysis Services cache is cold or can't be used for some reason, a warm file system cache can still have a positive impact on query performance. After the cache has been cleared, before Analysis Services can answer a query it needs to recreate the calculated members, named sets and other objects defined in a cube's MDX script. If you have any reasonably complex named set expressions that need to be evaluated, you'll see some activity in Profiler relating to these sets being built and it's important to be able to distinguish between this and activity that's related to the queries you're actually running. All MDX Script related activity occurs between the Execute MDX Script Begin and Execute MDX Script End events; these are fired after the Query Begin event but before the Query Cube Begin event for the query run after the cache has been cleared. When looking at a Profiler trace you should either ignore everything between the Execute MDX Script Begin and End events or run a query that returns no data at all to trigger the evaluation of the MDX Script, for example: SELECT {} ON 0FROM [Adventure Works] Designing for performance Many of the recommendations for designing cubes we've given so far in this article have been given on the basis that they will improve query performance, and in fact the performance of a query is intimately linked to the design of the cube it's running against. For example, dimension design, especially optimizing attribute relationships, can have a significant effect on the performance of all queries—at least as much as any of the optimizations described in this article. As a result, we recommend that if you've got a poorly performing query the first thing you should do is review the design of your cube to see if there is anything you could do differently. There may well be some kind of trade-off needed between usability, manageability, time-to-develop, overall "elegance" of the design and query performance, but since query performance is usually the most important consideration for your users then it will take precedence. To put it bluntly, if the queries your users want to run don't run fast your users will not want to use the cube at all! Performance-specific design features Once you're sure that your cube design is as good as you can make it, it's time to look at two features of Analysis Services that are transparent to the end user but have an important impact on performance and scalability: measure group partitioning and aggregations. Both of these features relate to the Storage Engine and allow it to answer requests for data from the Formula Engine more efficiently. Partitions A partition is a data structure that holds some or all of the data held in a measure group. When you create a measure group, by default that measure group contains a single partition that contains all of the data. Enterprise Edition of Analysis Services allows you to divide a measure group into multiple partitions; Standard Edition is limited to one partition per measure group, and the ability to partition is one of the main reasons why you would want to use Enterprise Edition over Standard Edition. Why partition? Partitioning brings two important benefits: better manageability and better performance. Partitions within the same measure group can have different storage modes and different aggregation designs, although in practice they usually don't differ in these respects; more importantly they can be processed independently, so for example when new data is loaded into a fact table, you can process only the partitions that should contain the new data. Similarly, if you need to remove old or incorrect data from your cube, you can delete or reprocess a partition without affecting the rest of the measure group. Partitioning can also improve both processing performance and query performance significantly. Analysis Services can process multiple partitions in parallel and this can lead to much more efficient use of CPU and memory resources on your server while processing is taking place. Analysis Services can also fetch and aggregate data from multiple partitions in parallel when a query is run too, and again this can lead to more efficient use of CPU and memory and result in faster query performance. Lastly, Analysis Services will only scan the partitions that contain data necessary for a query and since this reduces the overall amount of IO needed this can also make queries faster. Building partitions You can view, create and delete partitions on the Partitions tab of the Cube Editor in BIDS. When you run the New Partition Wizard or edit the Source property of an existing partition, you'll see you have two options for controlling what data is used in the partition: Table Binding means that the partition contains all of the data in a table or view in your relational data source, or a named query defined in your DSV. You can choose the table you wish to bind to on the Specify Source Information step of the New Partition Wizard, or in the Partition Source dialog if you choose Table Binding from the Binding Type drop-down box. Query Binding allows you to specify an SQL SELECT statement to filter the rows you want from a table; BIDS will automatically generate part of the SELECT statement for you, and all you'll need to do is supply the WHERE clause. If you're using the New Partition Wizard, this is the option that will be chosen if you check the Specify a query to restrict rows checkbox on the second step of the wizard; in the Partition Source dialog you can choose this option from the Binding Type drop-down box. It might seem like query binding is the easiest way to filter your data, and while it's the most widely-used approach it does have one serious shortcoming: since it involves hard-coding an SQL SELECT statement into the definition of the partition, changes to your fact table such as the deletion or renaming of a column can mean the SELECT statement errors when it is run if that column is referenced in it. This means in turn will cause the partition processing to fail.. If you have a lot of partitions in your measure group—and it's not unusual to have over one hundred partitions on a large cube—altering the query used for each one is somewhat time-consuming. Instead, table-binding each partition to a view in your relational database will make this kind of maintenance much easier, although you do of course now need to generate one view for each partition. Alternatively, if you're building query-bound partitions from a single view on top of your fact table (which means you have complete control over the columns the view exposes), you could use a query like SELECT * FROM in each partition’s definition. It's very important that you check the queries you're using to filter your fact table for each partition. If the same fact table row appears in more than one partition, or if fact table rows don't appear in any partition, this will result in your cube displaying incorrect measure values. On the Processing and Storage Locations step of the wizard you have the chance to create the partition on a remote server instance, functionality that is called Remote Partitions. This is one way of scaling out Analysis Services: you can have a cube and measure group on one server but store some of the partitions for the measure group on a different server, something like a linked measure group but at a lower level. This can be useful for improving processing performance in situations when you have a very small time window available for processing but in general we recommend that you do not use remote partitions. They have an adverse effect on query performance and they make management of the cube (especially backup) very difficult. Also on the same step you have the chance to store the partition at a location other than the default of the Analysis Services data directory. Spreading your partitions over more than one volume may make it easier to improve the IO performance of your solution, although again it can complicate database backup and restore. After assigning an aggregation design to the partition (we'll talk about aggregations in detail next), the last important property to set on a partition is Slice. The Slice property takes the form of an MDX member, set or tuple—MDX expressions returning members, sets or tuples are not allowed however - and indicates what data is present in a partition. While you don't have to set it, we strongly recommend that you do so, even for MOLAP partitions, for the following reasons: While Analysis Services does automatically detect what data is present in a partition during processing, it doesn't always work as well as you'd expect and can result in unwanted partition scanning taking place at query time in a number of scenarios. The following blog entry on the SQLCat team site explains why in detail: http://tinyurl.com/partitionslicing It acts as a useful safety mechanism to ensure that you only load the data you're expecting into a partition. If, while processing, Analysis Services finds that data is being loaded into the partition that conflicts with what's specified in the Slice property, then processing will fail. More detail on how to set the Slice property can be found in Mosha Pasumansky's blog entry on the subject here: http://tinyurl.com/moshapartition Planning a partitioning strategy We now know why we should be partitioning our measure groups and what to do to create a partition; the next question is: how should we split the data in our partitions? We need to find some kind of happy medium between the manageability and performance aspects of partitioning—we need to split our data so that we do as little processing as possible, but also so that as few partitions are scanned as possible by our users' queries. Luckily, if we partition by our Time dimension we can usually meet both needs very well: it's usually the case that when new data arrives in a fact table it's for a single day, week or month, and it's also the case that the most popular way of slicing a query is by a time period. Therefore, it's almost always the case that when measure groups are partitioned they are partitioned by time. It's also worth considering, though, if it's a good idea to partition by time and another dimension: for example, in an international company you might have a Geography dimension and a Country attribute, and users may always be slicing their queries by Country too—in which case it might make sense to partition by Country. Measure groups that contain measures with the Distinct Count aggregation type require their own specific partitioning strategy. While you should still partition by time, you should also partition by non-overlapping ranges of values within the column you're doing the distinct count on. A lot more detail on this is available in the following white paper: http://tinyurl.com/distinctcountoptimize It's worth looking at the distribution of data over partitions for dimensions we're not explicitly slicing by, as there is often a dependency between data in these dimensions and the Time dimension: for example, a given Product may only have been sold in certain Years or in certain Countries. You can see the distribution of member DataIDs (the internal key values that Analysis Services creates for all members on a hierarchy) for a partition by querying the Discover_Partition_Dimension_Stat DMV, for example: SELECT *FROM SystemRestrictSchema($system.Discover_Partition_Dimension_Stat ,DATABASE_NAME = 'Adventure Works DW 2008' ,CUBE_NAME = 'Adventure Works' ,MEASURE_GROUP_NAME = 'Internet Sales' ,PARTITION_NAME = 'Internet_Sales_2003') The following screenshot shows what the results of this query look like: There's also a useful Analysis Services stored procedure that shows the same data and any partition overlaps included in the Analysis Services Stored Procedure Project (a free, community-developed set of sample Analysis Services stored procedures): http://tinyurl.com/partitionhealth. This blog entry describes how you can take this data and visualise it in a Reporting Services report: http://tinyurl.com/viewpartitionslice We also need to consider what size our partitions should be. In general between 5 and 20 million rows per partition, or up to around 3GB, is a good size. If you have a measure group with a single partition of below 5 million rows then don't worry, it will perform very well, but it's not worth dividing it into smaller partitions; it's equally possible to get good performance with partitions of 50-60 million rows. It's also best to avoid having too many partitions as well—if you have more than a few hundred it may make SQL Management Studio and BIDS slow to respond, and it may be worth creating fewer, larger partitions assuming these partitions stay within the size limits for a single partition we've just given. Automatically generating large numbers of partitionsWhen creating a measure group for the first time, it's likely you'll already have a large amount of data and may need to create a correspondingly large number of partitions for it. Clearly the last thing you'll want to do is create tens or hundreds of partitions manually and it's worth knowing some tricks to create these partitions automatically. One method involves taking a single partition, scripting it out to XMLA and then pasting and manipulating this in Excel, as detailed here: http://tinyurl.com/generatepartitions. The Analysis Services Stored Procedure Project also contains a set of functions for creating partitions automatically based on MDX set expressions: http://tinyurl.com/autopartition. Unexpected partition scans Even when you have configured your partitions properly it's sometimes the case that Analysis Services will scan partitions that you don't expect it to be scanning for a particular query. If you see this happening the first thing to determine is whether these extra scans are making a significant contribution to your query times. If they aren't, then it's probably not worth worrying about; if they are, there are some things to try to attempt to stop it happening. The extra scans could be the result of a number of factors, including: The way you have written MDX for queries or calculations. In most cases it will be very difficult to rewrite the MDX to stop the scans, but the following blog entry describes how it is possible in one scenario: http://tinyurl.com/moshapart The LastNonEmpty measure aggregation type may result in multiple partition scans. If you can restructure your cube so you can use the LastChild aggregation type, Analysis Services will only scan the last partition containing data for the current time period. In some cases, even when you've set the Slice property, Analysis Services has trouble working out which partitions should be scanned for a query. Changing the attributes mentioned in the Slice property may help, but not always. The section on Related Attributes and Almost Related Attributes in the following blog entry discusses this in more detail: http://tinyurl.com/mdxpartitions Analysis Services may also decide to retrieve more data than is needed for a query to make answering future queries more efficient. This behavior is called prefetching and can be turned off by setting the following connection string properties: Disable Prefetch Facts=True; Cache Ratio=1 More information on this can be found in the section on Prefetching and Request Ordering in the white paper Identifying and Resolving MDX Query Bottleneck available from http://tinyurl.com/mdxbottlenecks Note that setting these connection string properties can have other, negative effects on query performance. You can set connection string properties in SQL Management Studio when you open a new MDX Query window. Just click the Options button on the Connect to Analysis Services dialog, then go to the Additional Connection Parameters tab. Note that in the RTM version of SQL Management Studio there is a problem with this functionality, so that when you set a connection string property it will continue to be set for all connections, even though the textbox on the Additional Connection Parameters tab is blank, until SQL Management Studio is closed down or until you set the same property differently. Aggregations An aggregation is simply a pre-summarised data set, similar to the result of an SQL SELECT statement with a GROUP BY clause, that Analysis Services can use when answering queries. The advantage of having aggregations built in your cube is that it reduces the amount of aggregation that the Analysis Services Storage Engine has to do at query time, and building the right aggregations is one of the most important things you can do to improve query performance. Aggregation design is an ongoing process that should start once your cube and dimension designs have stabilised and which will continue throughout the lifetime of the cube as its structure and the queries you run against it change; in this section we'll talk about the steps you should go through to create an optimal aggregation design. Creating an initial aggregation design The first stage in creating an aggregation design should be to create a core set of aggregations that will be generally useful for most queries run against your cube. This should take place towards the end of the development cycle when you're sure that your cube and dimension designs are unlikely to change much, because any changes are likely to invalidate your aggregations and mean this step will have to be repeated. It can't be stressed enough that good dimension design is the key to getting the most out of aggregations: removing unnecessary attributes, setting AttributeHierarchyEnabled to False where possible, building optimal attribute relationships and building user hierarchies will all make the aggregation design process faster, easier and more effective. You should also take care to update the EstimatedRows property of each measure group and partition, and the EstimatedCount of each attribute before you start, and these values are also used by the aggregation design process. BIDS Helper adds a new button to the toolbar in thePartitions tab of the Cube Editor to update all of these count properties with one click. To build this initial set of aggregations we'll be running the Aggregation Design Wizard, which can be run by clicking the Design Aggregations button on the toolbar of the Aggregations tab of the Cube Editor. This wizard will analyse the structure of your cube and dimensions, look at various property values you've set, and try to come up with a set of aggregations that it thinks should be useful. The one key piece of information it doesn't have at this point is what queries you're running against the cube, so some of the aggregations it designs may not prove to be useful in the long-run, but running the wizard is extremely useful for creating a first draft of your aggregation designs. You can only design aggregations for one measure group at a time; if you have more than one partition in the measure group you've selected then the first step of the wizard asks you to choose which partitions you want to design aggregations for. An aggregation design can be associated with many partitions in a measure group, and a partition can be associated with just one aggregation design or none at all. We recommend that, in most cases, you have just one aggregation design for each measure group for the sake of simplicity. However if processing time is limited and you need to reduce the overall time spent building aggregations, or if query patterns are different for different partitions within the same measure group, then it may make sense to apply different aggregation designs to different partitions. The next step of the wizard asks you to review the AggregationUsage property of all the attributes on all of the cube dimensions in your cube; this property can also be set on the Cube Structure tab of the Cube Editor. The following figure shows the Aggregation Design Wizard: The AggregationUsage property controls how dimension attributes are treated in the aggregation design process. The property can be set to the following values: Full: This means the attribute, or an attribute at a lower granularity directly related to it by an attribute relationship, will be included in every single aggregation the wizard builds. We recommend that you use this value sparingly, for at most one or two attributes in your cube, because it can significantly reduce the number of aggregations that get built. You should set it for attributes that will almost always get used in queries. For example, if the vast majority of your queries are at the Month granularity it makes sense that all of your aggregations include the Month attribute from your Time dimension. None: This means the attribute will not be included in any aggregation that the wizard designs. Don't be afraid of using this value for any attributes that you don't think will be used often in your queries, it can be a useful way of ensuring that the attributes that are used often get good aggregation coverage. Note that Attributes with AttributeHierarchyEnabled set to False will have no aggregations designed for them anyway. Unrestricted: This means that the attribute may be included in the aggregations designed, depending on whether the algorithm used by the wizard considers it to be useful or not. Default: The default option applies a complex set of rules, which are: The granularity attribute (usually the key attribute, unless you specified otherwise in the dimension usage tab) is treated as Unrestricted. All attributes on dimensions involved in many-to-many relationships, unmaterialised referenced relationships, and data mining dimensions are treated as None. Aggregations may still be built at the root granularity, that is, the intersection of every All Member on every attribute. All attributes that are used as levels in natural user hierarchies are treated as Unrestricted. Attributes with IsAggregatable set to False are treated as Full. All other attributes are treated as None The next step in the wizard asks you to verify the number of EstimatedRows and EstimatedCount properties we've already talked about, and gives the option of setting a similar property that shows the estimated number of members from an attribute that appear in any one partition. This can be an important property to set: if you are partitioning by month, although you may have 36 members on your Month attribute a partition will only contain data for one of them. On the Set Aggregation Options step you finally reach the point where some aggregations can be built. Here you can apply one last set of restrictions on the set of aggregations that will be built, choosing to either: Estimated Storage Reaches, which means you build aggregations to fill a given amount of disk space. Performance Gain Reaches, the most useful option. It does not mean that all queries will run n% faster; nor does it mean that a query that hits an aggregation directly will run n% faster. Think of it like this: if the wizard built all the aggregations it thought were useful to build (note: this is not the same thing as all of the possible aggregations that could be built on the cube) then, in general, performance would be better. Some queries would not benefit from aggregations, some would be slightly faster, and some would be a lot faster; and some aggregations would be more often used than others. So if you set this property to 100% the wizard would build all the aggregations that it could, and you'd get 100% of the performance gain possible from building aggregations. Setting this property to 30%, the default and recommended value, will build the aggregations that give you 30% of this possible performance gain—not 30% of the possible aggregations, usually a much smaller number. As you can see from the screenshot below, the graph drawn on this step plots the size of the aggregations built versus overall performance gain, and the shape of the curve shows that a few, smaller aggregations usually provide the majority of the performance gain. I Click Stop, which means carry on building aggregations until you click the Stop button. Designing aggregations can take a very long time, especially on more complex cubes, because there may literally be millions or billions of possible aggregations that could be built. In fact, it's not unheard of for the aggregation design wizard to run for several days before it's stopped! Do Not Design Aggregations allows you to skip designing aggregations. The approach we suggest taking here is to first select I Click Stop and then click the Start button. On some measure groups this will complete very quickly, with only a few small aggregations built. If that's the case click Next; otherwise, if it's taking too long or too many aggregations are being built, click Stop and then Reset, and then select Performance Gain Reaches and enter 30% and Start again. This should result in a reasonable selection of aggregations being built; in general around 50-100 aggregations is the maximum number you should be building for a measure group, and if 30% leaves you short of this try increasing the number by 10% until you feel comfortable with what you get. On the final step of the wizard, enter a name for your aggregation design and save it. It's a good idea to give the aggregation design a name including the name of the measure group to make it easier to find if you ever need to script it to XMLA. It's quite common that Analysis Services cube developers stop thinking about aggregation design at this point. This is a serious mistake: just because you have run the Aggregation Design Wizard does not mean you have built all the aggregations you need, or indeed any useful ones at all! Doing Usage-Based Optimisation and/or building aggregations manually is absolutely essential. Usage-based optimization We now have some aggregations designed, but the chances are that despite our best efforts many of them will not prove to be useful. To a certain extent we might be able to pick out these aggregations by browsing through them; really, though, we need to know what queries our users are going to run before we can build aggregations to make them run faster. This is where usage-based optimisation comes in: it allows us to log the requests for data that Analysis Services makes when a query is run and then feed this information into the aggregation design process. To be able to do usage-based optimization, you must first set up Analysis Services to log these requests for data. This involves specifying a connection string to a relational database in the server properties of your Analysis Services instance and allowing Analysis Services to create a log table in that database. The white paper Configuring the Analysis Services Query Log contains more details on how to do this (it's written for Analysis Services 2005 but is still relevant for Analysis Services 2008), and can be downloaded from http://tinyurl.com/ssasquerylog. The query log is a misleading name, because as you'll see if you look inside it it doesn't actually contain the text of MDX queries run against the cube. When a user runs an MDX query, Analysis Services decomposes it into a set of requests for data at a particular granularity and it's these requests that are logged; we'll look at how to interpret this information in the next section. A single query can result in no requests for data, or it can result in as many as hundreds or thousands of requests, especially if it returns a lot of data and a lot of MDX calculations are involved. When setting up the log you also have to specify the percentage of all data requests that Analysis Services actually logs with the QueryLogSampling property—in some cases if it logged every single request you would end up with a very large amount of data very quickly, but on the other hand if you set this value too low you may end up not seeing certain important long-running requests. We recommend that you start by setting this property to 100 but that you monitor the size of the log closely and reduce the value if you find that the number of requests logged is too high. Once the log has been set up, let your users start querying the cube. Explain to them what you're doing and that some queries may not perform well at this stage. Given access to a new cube it will take them a little while to understand what data is present and what data they're interested in; if they're new to Analysis Services it's also likely they'll need some time to get used to whatever client tool they're using. Therefore you'll need to have logging enabled for at least a month or two before you can be sure that your query log contains enough useful information. Remember that if you change the structure of the cube while you're logging then the existing contents of the log will no longer be usable. Last of all, you'll need to run the Usage-Based Optimisation Wizard to build new aggregations using this information. The Usage-Based Optimisation Wizard is very similar to the Design Aggregations Wizard, with the added option to filter the information in the query log by date, user and query frequency before it's used to build aggregations. It's a good idea to do this filtering carefully: you should probably exclude any queries you've run yourself, for example, since they're unlikely to be representative of what the users are doing, and make sure that the most important users queries are over-represented. Once you've done this you'll have a chance to review what data is actually going to be used before you actually build the aggregations. On the last step of the wizard you have the choice of either creating a new aggregation design or merging the aggregations that have just been created with an existing aggregation design. We recommend the latter: what you've just done is optimize queries that ran slowly on an existing aggregation design, and if you abandon the aggregations you've already got then it's possible that queries which previously had been quick would be slow afterwards. This exercise should be repeated at regular intervals throughout the cube's lifetime to ensure that you built any new aggregations that are necessary as the queries that your users run change. Query logging can, however, have an impact on query performance so it's not a good idea to leave logging running all the time. Processing aggregationsWhen you've created or edited the aggregations on one or more partitions, you don't need to do a full process on the partitions. All you need to do is to deploy your changes and then run a ProcessIndex, which is usually fairly quick, and once you've done that queries will be able to use the new aggregations. When you run a ProcessIndex Analysis Services does not need to run any SQL queries against the relational data source if you're using MOLAP storage. Monitoring partition and aggregation usage Having created and configured your partitions and aggregations, you'll naturally want to be sure that when you run a query Analysis Services is using them as you expect. You can do this very easily by running a trace with SQL Server Profiler or by using MDX Studio (a free MDX Editor that can be downloaded from http://tinyurl.com/mdxstudio). To use Profiler, start it and then connect to your Analysis Services instance to create a new trace. On the Trace Properties dialog choose the Blank template and go to the Events Selection tab and check the following: Progress ReportsProgress Report Begin Progress ReportsProgress Report End Queries EventsQuery Begin Queries EventsQuery End Query ProcessingExecute MDX Script Begin Query ProcessingExecute MDX Script End Query ProcessingQuery Cube Begin Query ProcessingQuery Cube End Query ProcessingGet Data From Aggregation Query ProcessingQuery Subcube Verbose Then clear the cache and click Run to start the trace. Once you've done this you can either open up your Analysis Services client tool or you can start running MDX queries in SQL Management Studio. When you do this you'll notice that Profiler starts to show information about what Analysis Services is doing internally to answer these queries. The following screenshot shows what you might typically see: Interpreting the results of a Profiler trace is a complex task and well outside the scope of this article, but it's very easy to pick out some useful information relating to aggregation and partition usage. Put simply: The Query Subcube Verbose events represent individual requests for data from the Formula Engine to the Storage Engine, which can be answered either from cache, an aggregation or base-level partition data. Each of these requests is at a single granularity, meaning that all of the data in the request comes from a single distinct combination of attributes; we refer to these granularities as "subcubes". The TextData column for this event shows the granularity of data that is being requested in human readable form; the Query Subcube event will display exactly the same data but in the less friendly-format that the Usage-Based Optimisation Query Log uses. Pairs of Progress Report Begin and Progress Report End events show that data is being read from disk, either from an aggregation or a partition. The TextData column gives more information, including the name of the object being read; however, if you have more than one object (for example an aggregation) with the same name, you need to look at the contents of the ObjectPath column to see what object exactly is being queried. The Get Data From Aggregation event is fired when data is read from an aggregation, in addition to any Progress Report events. The Duration column shows how long each of these operations takes in milliseconds. At this point in the cube optimisation process you should be seeing in Profiler that when your users run queries they hit as few partitions as possible and hit aggregations as often as possible. If you regularly see slow queries that scan all the partitions in your cube or which do not use any aggregations at all, you should consider going back to the beginning of the process and rethinking your partitioning strategy and rerunning the aggregation design wizards. In a production system many queries will be answered from cache and therefore be very quick, but you should always try to optimise for the worst-case scenario of a query running on a cold cache. Building aggregations manually However good the aggregation designs produced by the wizards are, it's very likely that at some point you'll have to design aggregations manually for particular queries. Even after running the Usage Based Optimisation Wizard you may find that it still does not build some potentially useful aggregations: the algorithm the wizards use is very complex and something of a black box, so for whatever reason (perhaps because it thinks it would be too large) it may decide not to build an aggregation that, when built manually, turns out to have a significant positive impact on the performance of a particular query. Before we can build aggregations manually we need to work out which aggregations we need to build. To do this, we once again need to use Profiler and look at either the Query Subcube or the Query Subcube Verbose events. These events, remember, display the same thing in two different formats - requests for data made to the Analysis Services storage engine during query processing - and the contents of the Duration column in Profiler will show how long in milliseconds each of these requests took. A good rule of thumb is that any Query Subcube event that takes longer than half a second (500 ms) would benefit from having an aggregation built for it; you can expect that a Query Subcube event that requests data at the same granularity as an aggregation will execute almost instantaneously. The following screenshot shows an example of trace on an MDX query that takes 700ms: The single Query Subcube Verbose event is highlighted, and we can see that the duration of this event is the same as that of the query itself, so if we want to improve the performance of the query we need to build an aggregation for this particular request. Also, in the lower half of the screen we can see the contents of the TextData column displayed. This shows a list of all the dimensions and attributes from which data is being requested —the granularity of the request—and the simple rule to follow here is that whenever you see anything other than a zero by an attribute we know that the granularity of the request includes this attribute. We need to make a note of all of the attributes which have anything other than a zero next to them and then build an aggregation using them; in this case it's just the Product Category attribute of the Product dimension. The white paper Identifying and Resolving MDX Query Performance Bottlenecks (again, written for Analysis Services 2005 but still relevant for Analysis Services 2008), available from http://tinyurl.com/mdxbottlenecks, includes more detailed information on how to interpret the information given by the Query Subcube Verbose event. So now that we know what aggregation we need to build, we need to go ahead and build it. We have a choice of tools to do this: we can either use the functionality built into BIDS, or we can use some of the excellent functionality that BIDS Helper provides. In BIDS, to view and edit aggregations, you need to go to the Aggregations tab in the cube editor. On the Standard View you only see a list of partitions and which aggregation designs they have associated with them; if you switch to the Advanced View by pressing the appropriate button on the toolbar, you can view the aggregations in each aggregation design for each measure group. If you right-click in the area where the aggregations are displayed you can also create a new aggregation and once you've done that you can specify the granularity of the aggregation by checking and unchecking the attributes on each dimension. For our particular query we only need to check the box next to the Product Categories attribute, as follows: The small tick at the top of the list of dimensions in the Status row shows that this aggregation has passed the built-in validation rules that BIDS applies to make sure this is a useful aggregation. If you see an amber warning triangle here, hover over it with your mouse and in the tooltip you'll see a list of reasons why the aggregation has failed its status check. If we then deploy and run a ProcessIndex, we can then rerun our original query and watch it use the new aggregation, running much faster as a result: The problem with the native BIDS aggregation design functionality is that it becomes difficult to use when you have complex aggregations to build and edit. The functionality present in BIDS Helper, while it looks less polished, is far more useable and offers many benefits over the BIDS native functionality, for example: The BIDS Helper Aggregation Design interface displays the aggregation granularity in the same way (ie using 1s and 0s, as seen in the screenshot below) as the Query Subcube event in Profiler does, making it easier to cross reference between the two. It also shows attribute relationships when it displays the attributes on each dimension when you're designing an aggregation, as seen on the righthand side in the screenshot that follows. This is essential to being able to build optimal aggregations. It also shows whether an aggregation is rigid or flexible. It has functionality to remove duplicate aggregations and ones containing redundant attributes (see below), and search for similar aggregations. It allows you to create new aggregations based on the information stored in the Query Log. It also allows you to delete unused aggregations based on information from a Profiler trace. Finally, it has some very comprehensive functionality to allow you to test the performance of the aggregations you build (see http://tinyurl.com/testaggs). Unsurprisingly, if you need to do any serious work designing aggregations manually we recommend using BIDS Helper over the built-in functionality. Common aggregation design issues Several features of your cube design must be borne in mind when designing aggregations, because they can influence how Analysis Services storage engine queries are made and therefore which aggregations will be used. These include: There's no point building aggregations above the granularity you are slicing your partitions by. Aggregations are built on a per-partition basis, so for example if you're partitioning by month there's no value in building an aggregation at the Year granularity since no aggregation can contain more than one month's worth of data. It won't hurt if you do it, it just means that an aggregation at month will be the same size as one at year but useful to more queries. It follows from this that it might be possible to over-partition data and reduce the effectiveness of aggregations, but we have anecdotal evidence from people who have built very large cubes that this is not an issue. For queries involving a dimension with a many-to-many relationship to a measure group, aggregations must not be built using any attributes from the many-to-many dimension, but instead must be built at the granularity of the attributes with a regular relationship to the intermediate measure group. When a query is run using the Sales Reason dimension Analysis Services fi rst works out which Sales Orders relate to each Sales Reason, and then queries the main measure group for these Sales Orders. Therefore, only aggregations at the Sales Order granularity on the main measure group can be used. As a result, in most cases it's not worth building aggregations for queries on many-to-many dimensions since the granularity of these queries is often close to that of the original fact table. Queries involving measures which have semi-additive aggregation types are always resolved at the granularity attribute of the time dimension, so you need to include that attribute in all aggregations. Queries involving measures with measure expressions require aggregations at the common granularity of the two measure groups involved. You should not build aggregations including a parent/child attribute; instead you should use the key attribute in aggregations. No aggregation should include an attribute which has AttributeHierarchyEnabled set to False. No aggregation should include an attribute that is below the granularity attribute of the dimension for the measure group. Any attributes which have a default member that is anything other than the All Member, or which have IsAggregatable set to False, should also be included in all aggregations. Aggregations and indexes are not built for partitions with fewer than 4096 rows. This threshold is set by the IndexBuildThreshold property in msmdsrv.ini; you can change it but it's not a good idea to do so. Aggregations should not include redundant attributes, that is to say attributes from the same 'chain' of attribute relationships. For example if you had a chain of attribute relationships going from month to quarter to year, you should not build an aggregation including month and quarter—it should just include month. This will increase the chance that the aggregation can be used by more queries, as well as reducing the size of the aggregation. Summary In this part of the article we covered performance-specific design features such as partitions and aggregations. In the next part, we will cover MDX calculation performance and caching.

Textures In your journey as a 3d artist, you might have encountered several (if not all) astounding works of art.  And through close inspection, you’ll notice that we barely see them without textures.  That is because textures are one of the most important aspect of 3d, but still, this doesn’t apply to all.  But adding textures to your characters, props, environment, etc. will greatly add to the aesthetic factor of your image that you wouldn’t believe it would. There are a number of ways to add texture to your objects in 3D such as UV mapping techniques, projections, 2D painting, etc.  All of these depend entirely on what kind of render are you trying to achieve.  But for the sake of this article, we’ll try to achieve some nice looking textures without having to worry about the complex tasks involved with it.  And with this, we’ll be using the ever famous and useful procedural textures to create seamless and continuously looking texture mapped over the surface of our models. More information about Procedural Textures can be found on http://www.blender.org/development/release-logs/blender-233/procedural-textures/. Now let’s add some textures, shall we? Let’s select the character model in our scene then go to the Texture tab on the rightmost part of the Material Buttons window and click Add New to add a new texture. Adding a New Texture After having added a new texture, additional windows appear allowing us to further modify how the currently added texture will affect our material.  Name this first texture as “bump” and the mapping options can be seen below. Bump Texture Mapping Settings   Bump Texture Settings Add another texture below the “bump” texture and call it “stain”.  The settings can be seen below. Stain Texture Mapping Settings   Stain Texture Settings We could have added more overlaying textures, but this will do for now just so we could see how the textures have affected our material so far.  Rendering now will only lead us to the image below. Dirtier And Better :) This time might be a good idea to change our framing and staging so we could look at it at a better perspective.  Changing the camera angle and increasing the ground plane’s scale and some adjustments on the spheres, I achieved something like this: New Camera Angle For an even better interaction from within the scene, we will adjust some material settings to simulate hard and reflective surfaces.  It’s a little unfair to give our main character some good materials while neglecting the other stuff we have.  So let’s just get on, and add some decent materials as replacement to the initial materials that both the spheres have had before. Go on and select the larger sphere and edit the current material we have so it would match that of the settings as seen in the image below. You’ll notice I added a Color Ramp to each of the materials, this is to slightly give the color a color transition as would be seen in the natural world, in addition to the current diffuse it already has. The vital part of the shading process of the Spheres is the reflectivity and mirror options as you can see in the following table:     Ray Mirror Freshnel Green Sphere 0.12 0.76 Blue Sphere 0.21 0.99     Green Sphere Material Settings   Blue Sphere Material Settings Our render would now look like this: Reflections to Simulate Mirror Effect and Smoothness To nearly finalize this part, we now deal with adding a texture to the world and varying the colors that would affect the Occlusion effect. To do so, let’s first change the Horizon and Zenith color of our World and change the Ambient Occlusion diffuse energy to the color we’ve just set by changing from “Plain” to “Sky Color”, as seen below. World Settings Rendering now will lead us to: New World Settings Render Notice the subtle difference between the previous render and the latest one where the slight bluish hue is more distinguishable. And then lastly, since we've already added some decent reflective material over to our spheres, it would be best if we can also see some environment being reflected over, to add to the already existent character as one of the objects being reflected. To do this, we're going to add a texture to our World.  This is one nifty tool in simulating an environment since we don't have to do the hard work in manually creating the objects that are going to be reflected.  Not only does it save us a lot of time but also the ease by which we can alter these environment is already a big advantage that we have at our hands. So to do this, let's go ahead and go to our Shading (F5) and select World Buttons.  Scroll to the far left side and you'll see tabs labeled “Texture and Input” and “Map To”, both of these tabs are essential in setting up our World texture so pay close attention to them. Below is an image that further shows you what we need to set up (sorry for the sudden theme change). World Texture You might have already guessed what we should do next, if not, I'll continue on.  After heading over to the “Texture and Input” and “Map To” tabs, let's first focus on what's active by default, that is, “Texture and Input”.  In this part, we'll only need a few things to get started.  First is to click “Add New” to add a new texture datablock to our blender scene, after which, let's edit the name of our texture and name it “environment”, then change the coordinates from “View” to “AngMap” to use a 360 degree angular coordinate, you'll see why in awhile. Adding a World Texture After applying these initial settings, we'll go ahead and proceed to the actual texturing process, which, as far as the World is concerned is just a very quick process.  I suppose you're still on the same Buttons window that we're on last time.  Click on the Texture button or press F6 on your function keys. Bam! Another set of Windows.  You'll see here that the texture we named “environment” awhile back is now reflected over to one of the texture slots, just like what we previously did with texturing the character we have.  But this time, instead of choosing procedural textures like Clouds, Voronoi, Noise, etc., we'll now be dealing with an image texture, as in our case, an HDRi (High Dynamic Range Image).  Our purpose in using an HDR image is to simulate the wide range of intensity levels (brightness and darkness) that is seen in reality and apply these settings over to our world, thus reflected upon by our objects.  As in our case, we'll be using high dynamic range images as light probes which are oriented 360 degrees and that's the very reason why we chose “AngMap” as our World texture coordinate. More info about HDRi can be seen at http://en.wikipedia.org/wiki/High_dynamic_range_imaging and you can download Light Probe Images over at Paul Debevec's collection at http://www.debevec.org/Probes.  Save your downloaded light probe images somewhere you can easily identify them with.  I couldn't stress enough how file organization can greatly help you in your career.  You could just imagine how frustrating it is to find assets among a thousand you already have, without properly placing them in their right places, this counts for every project you have as well . So to open up our Light Probe Image as texture to our World, click the drop down menu and choose “Image” as your texture type.  This tells Blender to use an image instead of the default procedural textures.  Then head to the far right side to locate the Image tab with a Load button on it.  Let's skip the Map Image tab for now. Image as Texture Type Loading an Image Texture Browse over at your downloaded HDR image (which should have an extension of .hdr) and confirm.  Now that the image is loaded, let's leave the default settings as they are since we wouldn't be using them that much.  You'll see on the far left Preview just how wonderful looking our image is.  But rendering your scene right now would yield to nothing but the same previous render we've had.  So if you're itching to get this image right at our scene (which I am too), go back to your World Settings and head over to the “Map To” tab just beside “Texture and Input” then deselect “Blend” and select “Hori” instead.  Kabam! Now we're all set! World Texture Mapping options And now, the moment we've all been eagerly waiting for, the Render! Yup, go ahead and render and it would (luckily) look like the image below. Render with HDRi Environment Then finally, on the next and last part of this article, we'll look on how we can even further add realism to our scene by simulating camera lenses and further enhancing the tone of the image with Composite Nodes.  

Among the daily deluge of spam, sometimes there is a real email – and sometimes even an important one. I received such an email at the end of October 2003 from Louay Fatoohi, presenting himself as the Editorial Director of a new publishing company, Packt Publishing. He was asking if I would be interested in writing a book about phpMyAdmin (which is a web interface for the MySQL database). At this time, our software product was already popular with about 150,000 downloads per month, but the lack of serious and complete user documentation held it back. This translated into many requests from users about how to accomplish some tasks with the software. Moreover, the small team of five active developers (all volunteers) was using all its free time for planning and coding new features so we did not see how to improve the documentation with volunteer work only. I did not know Mr Fatoohi but his email (and the following ones) looked serious so I committed myself to this project. I noticed that producing a clear book outline was very important. I already had in mind that each chapter should focus on a specific task or group of similar tasks. In the next days, a few iterations of the outline were done to balance the chapter lengths. Receiving a paper contract from England was new for me – in fact the whole process felt new to me as this was my first book. Writing for others is challenging and dealing with the comments of reviewers and editors can be a humbling experience; I had to defend some ideas I had put in the book but gladly accepted suggestions for improvement or clarification. It took about three months, working over the weekends (I have a day job) to write the first edition, Mastering phpMyAdmin for Effective MySQL Management, which was published in April 2004. Since this manual covers the complete interface, I was in fact testing the whole software, finding bugs along the way, which forced me to fix them in order to produce correct screenshots! Needless to say, I was very proud of being the first published author for this company. Meanwhile, an interface redesign was taking place in phpMyAdmin for version 2.6.0; this meant that many screenshots in the book would no longer reflect the upcoming version of the software. The decision was made by Packt to publish a book update. During the summer of 2004, I already knew what the new version would look like so I started preparing this update. The new version of the software was released at the end of September and the book update (bearing the same ISBN as the first edition) somewhere in October! In June 2005 we organized the first phpMyAdmin team meeting in Germany during the LinuxTag event where we demoed our software. I was happy to meet Mr Fatoohi, Damian Carvill and other representatives from Packt who had a booth there too. I found the whole team very dynamic and very much readers-oriented. I know that publishing is a business but they understand that quality is important; they also respect me as an author (especially about schedules). Over the years, translations of the book were published, directly by Packt or via other publishers. I wrote the French version, while two phpMyAdmin developers did the German one. Versions in Czech, Italian and Spanish were also born and they are all proudly displayed in my house near the workstation. Meanwhile, phpMyAdmin’s march was continuing, with an average of 250,000 downloads per month. It should be noted that many users do not need to download it directly because host providers install the software for them, or Linux distributors include it on their DVD, this is why we cannot know exactly the number of users. The development team changed a bit but new features were appearing so I guess Packt Publishing resigned itself about the need to update this title from time to time. This is why I took to my pencil again – rather my keyboard – to produce the October 2006 edition, Mastering phpMyAdmin 2.8 which covered a new setup mechanism. In March 2008, Mastering phpMyAdmin 2.11 was published, followed by Mastering phpMyAdmin 3.1 in March 2009. My family is now accustomed to my new habit of taking a few weeks per year to produce the new update. I would like to express my wholeheartedly gratitude to Packt Publishing for their support into this ongoing project. --Marc Delisle

The Rotating Post Titles with Post Preview gadget lists all your blog posts classified according to labels or categories. Blogger uses Labels to classify posts while Wordpress uses Categories for the same. Clicking on a Label or a category in the sidebar of a blog brings up all posts associated with that particular label or category. However, you will see only posts associated with that one label. In this gadget post titles are grouped under their respective labels. In this gadget in one look you can see all the post titles in that blog and all the labels in it. Thus you get a full summary of the blog. Hovering on a post title shows the Post Preview in the top pane. You can then click on it to go to that post to read it in full detail. What is Google AJAX feed API? AJAX (shorthand for asynchronous JavaScript and XML) is a web development technique which retrieves data from the server asynchronously in the background without interfering with the display, and behaviour of the existing page. The whole page is not refreshed when data is retrieved. Only that section of the page which is a part of the gadget shows the data brought. With the Google AJAX Feed API, you can retrieve feeds and mash them up using Javascript. In this gadget, we will retrieve the post titles from the label feeds and display them using Javascript code. See picture below: This gadget shows list of posts grouped by label from my blog http://www.blogdoctor.me. Four post titles from three labels are shown but the code can be modified to show all posts from all labels (categories). This label is also shown as Gadget No 4 in My Gadget Showcase blog. The cursor autoscrolls down the post titles, and each post preview is shown at the top as an excerpt for five seconds before moving on to the next post. Obtaining the Google AJAX API Key The first step in installing the above gadget is to get the Google AJAX API Key. It is free and you can easily obtain it for any site blog or page by signing up for the key at the API key signup page. Type in your blog address in the My web site URL text box and click the "Generate API Key" button. On the resulting page copy the key and paste it in code below as shown. Customizing the code In the code below replace PASTE AJAX API KEY HERE with your actual key obtained above. <!-- ++Begin Dynamic Feed Wizard Generated Code++ --><!-- // Created with a Google AJAX Search and Feed Wizard // http://code.google.com/apis/ajaxsearch/wizards.html --> <!-- // The Following div element will end up holding the actual feed control. // You can place this anywhere on your page. --><div id="content"> <span style="color:#676767;font-size:11px;margin:10px;padding:4px;">Loading...</span> </div><!-- Google Ajax Api --> <script src="http://www.google.com/jsapi?key=PASTE AJAX API KEY HERE" type="text/javascript"></script> <!-- Dynamic Feed Control and Stylesheet --> <script src="http://www.google.com/uds/solutions/dynamicfeed/gfdynamicfeedcontrol.js" type="text/javascript"></script> <style type="text/css"> @import url("http://www.google.com/uds/solutions/dynamicfeed/gfdynamicfeedcontrol.css"); </style><script type="text/javascript"> google.load('feeds', '1'); function OnLoad() { var feeds = [ { title: 'LABEL_1', url: 'http://MYBLOG.blogspot.com/feeds/posts/default/-/LABEL1?max-results=100' }, { title: 'LABEL_2', url: 'http://MYBLOG.blogspot.com/feeds/posts/default/-/LABEL2?max-results=100' }, { title: 'LABEL_3', url: 'http://MYBLOG.blogspot.com/feeds/posts/default/-/LABEL3?max-results=100' } ]; var options = { stacked : true, horizontal : false, title : "Posts from BLOG_TITLE" }; new GFdynamicFeedControl(feeds, 'content', options); document.getElementById('content').style.width = "200px"; } google.setOnLoadCallback(OnLoad); </script> In the above code replace LABEL_1, LABEL_2 and LABEL_3 and LABEL1, LABEL2 and LABEL3 by respective Label Names and BLOG_TITLE by the actual title of your blog. Also replace MYBLOG by actual blog subdomain. This is for blogspot blogs only. For Wordpress blog you will have to replace the label feeds:: http://MYBLOG.blogspot.com/feeds/posts/default/-/LABEL1?max-results=100 http://MYBLOG.blogspot.com/feeds/posts/default/-/LABEL2?max-results=100 http://MYBLOG.blogspot.com/feeds/posts/default/-/LABEL3?max-results=100 by the Category feed URLs from Wordpress blog. After customizing the above code in Blogger paste it in a HTML gadget while in Wordpress paste it in a Text widget. Further Customization To show more than four posts per label or category, you will have to modify the following Javascript code file: http://www.google.com/uds/solutions/dynamicfeed/gfdynamicfeedcontrol.js In the above mentioned Javascript code file, alter the following code line in it : GFdynamicFeedControl.DEFAULT_NUM_RESULTS = 4; Change '4' to '1000' and save the file as a MODgfdynamicfeedcontrol.js file in a text editor like Notepad. Upload the file to a free host and replace the link of the file in the above code. To change the styling of the gadget, you will have to modify the following file: http://www.google.com/uds/solutions/dynamicfeed/gfdynamicfeedcontrol.css Then save the modified file and upload it to a free host and replace its link in the above code. Working Example Posts from The Blog Doctor Change Post Formatting According to Author. by Vin - 30 Apr 2008 If you have a Team Blog made up of two authors you can change the formatting of the posts written by one author to ... Template Calling all Newbie Bloggers! Upgrade Classic Template without the "UPGRADE YOUR TEMPLATE" button. The Minimalist Minima Photoblog Template. Making the COMMENTS Link more User Friendly. CSS Change Post Formatting According to Author. Free CSS Navigation Menus in Blogger. Fix the Page Elements Layout Editor No Scrollbar Problem. Frame the Blog Header Image. Blogger Hacks Rounded Corner Headers for Blogger. Timestamp under the Date and Other Hacks. Add Icon to Post Titles. Many Headers In One Blog.   Summary The Rotating Post Titles with Post Preview gadget provides an at-a glance summary of your blog. All the posts are grouped by label or category and are linked to their post pages. The constantly rotating post excerpts at the top draws the attention of the reader and gets him/her more involved and eager to explore your blog. This increases the traffic and decreases the bounce rate of visitors from your blog.nara

The unsung heroes of every application are the simple things like buttons, menus, and toolbars. In this article by Shea Frederick, Steve 'Cutter' Blades, and Colin Ramsay, we will cover how to add these items to our applications. Our example will contain a few different types of buttons, both with and without menus. A button can simply be an icon, or text, or both. Toolbars also have some mechanical elements such as spacers and dividers that can help to organize the buttons on your toolbars items. We will also cover how to make these elements react to user interaction. A toolbar for every occasion Just about every Ext component—panels, windows, grids can accept a toolbar on either the top or the bottom. The option is also available to render the toolbar standalone into any DOM element in our document. The toolbar is an extremely flexible and useful component that will no doubt be used in every application. Ext.Toolbar: The main container for the buttons Ext.Button: The primary handler for button creation and interaction Ext.menu: A menu Toolbars Our first toolbar is going to be rendered standalone in the body of our document. We will add one of each of the main button types, so we can experiment with each: Button—tbbutton: This is the standard button that we are all familiar with. Split Button—tbsplit: A split button is where you have a default button action and an optional menu. These are used in cases where you need to have many options in the same category as your button, of which there is a most commonly used default option. Menu—tbbutton+menu: A menu is just a button with the menu config filled in with options. Ext.onReady(function(){ new Ext.Toolbar({ renderTo: document.body, items: [{ xtype: 'tbbutton', text: 'Button' },{ xtype: 'tbbutton', text: 'Menu Button', menu: [{ text: 'Better' },{ text: 'Good' },{ text: 'Best' }] },{ xtype: 'tbsplit', text: 'Split Button', menu: [{ text: 'Item One' },{ text: 'Item Two' },{ text: 'Item Three' }] }] });}); As usual, everything is inside our onReady event handler. The items config holds all of our toolbars elements—I say elements and not buttons because the toolbar can accept many different types of Ext components including form fields—which we will be implementing later on in this article. The default xtype for each element in the items config is tbbutton. We can leave out the xtype config element if tbbutton is the type we want, but I like to include it just to help me keep track. The button Creating a button is fairly straightforward; the main config option is the text that is displayed on the button. We can also add an icon to be used alongside the text if we want to. Here is a stripped-down button: { xtype: 'tbbutton', text: 'Button'} Menu A menu is just a button with the menu config populated—it's that simple. The menu items work along the same principles as the buttons. They can have icons, classes, and handlers assigned to them. The menu items could also be grouped together to form a set of option buttons, but first let's create a standard menu. This is the config for a typical menu config: { xtype: 'tbbutton', text: 'Button', menu: [{ text: 'Better' },{ text: 'Good' },{ text: 'Best' }]} As we can see, once the menu array config is populated, the menu comes to life. To group these menu items together, we would need to set the group config and the boolean checked value for each item: menu: [{ text: 'Better', checked: true, group: 'quality'}, { text: 'Good', checked: false, group: 'quality'}, { text: 'Best', checked: false, group: 'quality'}] Split button The split button sounds like a complex component, but it's just like a button and a menu combined, with a slight twist. By using this type of button, you get to use the functionality of a button while adding the option to select an item from the attached menu. Clicking the left portion of the button that contains the text triggers the button action. However, clicking the right side of the button, which contains a small down arrow, triggers the menu. { xtype: 'tbsplit', text: 'Split Button', menu: [{ text: 'Item One' },{ text: 'Item Two' },{ text: 'Item Three' }]} Toolbar item alignment, dividers, and spacers By default, every toolbar aligns elements to the leftmost side. There is no alignment config for a toolbar, so if we want to align all of the toolbar buttons to the rightmost side, we need to add a fill as the first item in the toolbar. If we want to have items split up between both the left and right sides, we can also use a fill: { xtype: 'tbfill'} Pop this little guy in a tool-bar wherever you want to add space and he will push items on either side of the fill to the ends of the tool bar, as shown below: We also have elements that can add space or vertical dividers, like the one used between the Menu Button and the Split Button. The spacer adds a few pixels of empty space that can be used to space out buttons, or move elements away from the edge of the toolbar: { xtype: 'tbspacer'} A divider can be added in the same way: { xtype: 'tbseparator'} Shortcuts Ext has many shortcuts that can be used to make coding faster. Shortcuts are a character or two that can be used in place of a configuration object. For example, consider the standard toolbar filler configuration: { xtype: 'tbfill'} The shortcut for a toolbar filler is a hyphen and a greater than symbol: '->' Not all of these shortcuts are documented. So be adventurous, poke around the source code, and see what you can find. Here is a list of the commonly-used shortcuts:

So, let's take a step-by-step approach on how to accomplish this on the Flash side, which as far as I am concerned, is the fun side! Click here to access all the codes used in this article. Step 1: Creating our Flash application With our chapter2 project open, we can shift our focus to the Actions panel within the Flash IDE. Although working with the Actions panel is great for small applications, we will eventually build onto this Flash application, which might make it impractical to keep all of our ActionScript code within the Actions panel. Because of this, we will first need to create a separate ActionScript file that will serve as our main entry point for our Flash application. This will allow us to easily expand our application and add to the functionality without modifying the Actions panel for every addition we make. Step 2: Creating a main.as ActionScript file For this step, we will simply create an empty file next to our chapter2.fla file called main.as. After you have created this new file, we will then need to reference it within our Actions panel. To do this, we will use the include keyword in ActionScript to include this file as the main entry point for our application. So, shifting our focus back to the chapter2.fla file, we will then place the following code within the Actions panel: include "main.as";stop(); Now that we are referencing the main.as file for any of the ActionScript functionality, we will no longer need to worry about the Actions panel and add any new functionality directly to the main.as file. Now, for the following sections, we will use this main.as file to place all of our ActionScript code that will connect and extract information from our Drupal system, and then populate that information in a TextField that we will create later. So, let's jump right in and write some code that connects us with our Drupal system. Step 3: Connecting to Drupal For this step, we will first need to open up our empty main.as file so that we can add custom functionality to our Flash application. With this file open in our Flash IDE, our first task will be to connect with Drupal. Connecting to Drupal will require us to make a remote call to our Drupal installation, and then handle its response correctly. This will require the use of asynchronous programming techniques along with some standard remoting classes built into the ActionScript 3 library. I will spend some time here discussing the class used by ActionScript 3 to achieve remote communication. This class is called NetConnection. Using the NetConnection class The NetConnection class in ActionScript 3 is specifically used to achieve remote procedure calls within a Flash application. Luckily, this class is pretty straight forward and does not have a huge learning curve on understanding how to utilize it for communicating with Drupal. Using this class requires that we first create an instance of this class as an object, and then initialize that object with the proper settings for our communication. But let's tackle the creation first, which will look something like this in our main.as file: // Declare our Drupal connectionvar drupal:NetConnection = new NetConnection(); Now, you probably noticed that I decided to name my instance of this net connection drupal. The reason for this is to make it very clear that any place in our Flash application where we would like to interact with Drupal, we will do so by simply using our drupal NetConnection object. But before we use this connection, we must first specify what type of connection we will be using. In any NetConnection object, we can do this by providing a value for the variable objectEncoding . This variable lets the connection know how to structure the XML format when communicating back and forth between Flash and Drupal. Currently, there are only two types of encoding to choose from: AMF0 or AMF3. AMF0 is used for ActionScript versions less than 3, while AMF3 is used for ActionScript 3. ActionScript 1 and 2 are much less efficient than version 3, so it is highly recommended to use ActionScript 3 over 1 or 2. Since we are using ActionScript 3, we will need to use the AMF3 format, and we can provide this as follows: // Declare our Drupal connectionvar drupal:NetConnection = new NetConnection();drupal.objectEncoding = ObjectEncoding.AMF3; Now that we have an instance ready to go, our first task will be to connect to the Drupal gateway that we set up in the previous section. Connecting to a remote gateway Connecting to a remote gateway can be performed using the connect command on our drupal NetConnection object. But in order for us to connect, we must first determine the correct gateway URL to pass to this function. We can find this by going back to our Drupal installation and navigating to Administer | Services. In the Browse section, you will see a link to the servers available for remote procedure calls as shown in the following screenshot: For every listed server, we can click on each link to verify that the server is ready for communication. Let's do this by clicking on the link for AMFPHP, which should then bring up a page to let us know that our AMFPHP gateway is installed properly. We can also use this page to determine our AMFPHP gateway location, since it is the URL of this page. By observing the path of this page, we can add our AMFPHP server to our main.as file by combining the base URL of our site and then adding the AMFPHP services gateway to that base. // Declare our baseURL and gateway string.var baseURL:String = "http://localhost/drupal6";var gateway:String = baseURL + "/services/amfphp";// Declare our Drupal connectionvar drupal:NetConnection = new NetConnection();drupal.objectEncoding = ObjectEncoding.AMF3;// Connect to the Drupal gatewaydrupal.connect( gateway ); It is important to note that the connect routine is synchronous, which means that once this function is called, we can immediately start using that connection. However, any remote procedure call that we make afterwards, will be asynchronous, and will need to be handled as such. The function that can be used to make these remote procedure calls to Drupal is called call.

Besides public web sites, it would be nice if we could provide a personal community, that is, My Community, for each registered user. In My Community, users can have a set of public and private pages. Here you can save your favorite games, videos and playlists, and the My Street theme — your background color. As shown in the following screenshot, there is a page my_street with a portlet myStreet, where an end user can sign up, log in, or handle an issue such as Forgot Your Password? o When you click on the SIGN UP button, this My Street portlet will allow the end users to set up his/her account such as creating a nickname, password hint question, password hint answer, your password, and verify password. Further, as the end user, you can set up favorite games, videos and playlists, and background color. You can have your own favorites: number of favorites displayed in My Street (for example, 4, 20, and 35) and My Street theme, (for example, default, Abby Cadabby, Bert, Big Bird, Cookie Monster, and so on). A set of default My Street themes is predefined in /cms_services/images/my_street/ under the folder $CATALINA_HOME/webapps/ and you can choose any one of them at any time. At the same time, you can upload a photo to your own Book Street web page. When logged in, the My Street theme will be applied on Home page, Games landing page, Videos landing page, and Playlist landing page. For example, current user's My Street theme could be applied on Playlist landing page. You may play videos, games, and playlists when you visit the web site www.bookpubstreet.com. When you find your favorite videos, games, and playlists, you can add them into My Street. As shown in the following screenshot, you could be playing a playlist Tickle Time. If you are interested in this playlist, just click on the Add to My Street button. The playlist Tickle Time will be added into My Street as your favorite playlist. In this section, we will show how to implement these features. Customizing user model First, let's customize user model in service.xml in order to support extended user and user preferences. To do so, use the following steps: Create a package com.ext.portlet.user in the /ext/ext-impl/src folder. Create an XML file service.xml in the package com.ext.portlet.comment and open it. Add the following lines in this file and save it: <?xml version="1.0"?><!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 5.2.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_5_2_0.dtd"><service-builder package-path="com.ext.portlet.user"> <namespace>ExtUser</namespace> <entity name="ExtUser" uuid="false" local-service="true" remote-service="true" persistence-class="com.ext.portlet.user.service. persistence. ExtUserPersistenceImpl"> <column name="userId" type="long" primary="true" /> <column name="favorites" type="int" /> <column name="theme" type="String" /> <column name="printable" type="boolean" /> <column name="plainPassword" type="String" /> <column name="creator" type="String" /> <column name="modifier" type="String" /> <column name="created" type="Date" /> <column name="modified" type="Date" /> </entity> <entity name="ExtUserPreference" uuid="false" local-service="true" remote-service="true" persistence-class="com.ext.portlet.user.service. persistence.ExtUserPreferencePersistenceImpl"> <column name="userPreferenceId" type="long" primary="true" /> <column name="userId" type="long"/> <column name="favorite" type="String" /> <column name="favoriteType" type="String" /> <column name="date_" type="Date" /> </entity> <exceptions> <exception>ExtUser</exception> <exception>ExtUserPreference</exception> </exceptions></service-builder> The code above shows the customized user model, including userId associated with USER_ table, favourites, theme, printable, plainPassword, and so on. This code also shows user preferences model, including userPreferenceId, userId, favorite (for example, game/video/playlist UID), favoriteType (for example, game/video/playlist), and the updated date date_. Of course, these models are extensible. You can extend these models for your unique current needs or future requirements. Enter the following tables into database through command prompt: create table ExtUser ( userId bigint not null primary key, creator varchar(125), modifier varchar(125), created datetime null,modified datetime null, favorites smallint, theme varchar(125), plainPassword varchar(125), printable boolean ); create table ExtUserPreference ( usePreferenceId bigint not null primary key, userId bigint not null,favorite varchar(125), favoriteType varchar(125),date_ datetime null ); The preceding code shows the database SQL script for customized user model and user preference model. Similar to XML model ExtUser, this code shows the userId, favorites, theme, plainPassword, and printable table fields. Again, for the XML model ExtUserPreference, this code shows the userId, favorite, favoriteType, date_, and userPreferenceId table fields. Afterwards, we need to build a service with ServiceBuilder. After preparing service.xml, you can build services. To do so, locate the XML file /ext/ext-impl/buildparent.xml, open it, add the following lines between </target> and <target name="build-service-portlet-reports">, and save it: <target name="build-service-portlet-extUser"> <antcall target="build-service"> <param name="service.file" value="src/com/ext/portlet/user/service.xml" /> </antcall></target> When you are ready, just double-click on the Ant target build-service-portletextUser. ServiceBuilder will build related models and services for extUser and extUserPreference.   Building the portlet My Street Similar to how we had built portlet Ext Comment, we can build the portlet My Street as follows: Configure the portlet My Street in both portlet-ext.xml and liferay-portlet-ext.xml files. Set title mapping in the Language-ext.properties file. Add the My Street portlet to the Book category in the liferay-display.xml file. Finally, specify Struts actions and forward paths in the struts-config.xml and tiles-defs.xml files, respectively. Then, we need to create Struts actions as follows: Create a package com.ext.portlet.my_street.action in the folder /ext/ext-impl/src. Add the Java files ViewAction.java, EditUserAction.java, and CreateAccountAction.java in this package. Create a Java file AddUserLocalServiceUtil.java in this package and open it. Add the following methods in AddUserLocalServiceUtil.java and save it: public static ExtUser getUser(long userId){ ExtUser user = null; try{ user = ExtUserLocalServiceUtil.getExtUser(userId); } catch (Exception e){} if(user == null){ user = ExtUserLocalServiceUtil.createExtUser(userId); try{ ExtUserLocalServiceUtil.updateExtUser(user); } catch (Exception e) {} } return user;}public static void deleteUser(long userId) { try{ ExtUserLocalServiceUtil.deleteExtUser(userId); } catch (Exception e){} }public static void updateUser(ActionRequest actionRequest, long userId) { /* ignore details */}public static List<ExtUserPreference> getUserPreferences( long userId, int limit){ /* ignore details */}public static ExtUserPreference addUserPreference( long userId, String favorite, String favoriteType){ /* ignore details */ } As shown in the code above, it shows methods to get ExtUser and ExtUserPreference, to delete ExtUser, and to add and update ExtUser and ExtUserPreference. In addition, we need to provide default values for private page and friendly URL in portal-ext.properties as follows: ext.default_page.my_street.private_page=falseext.default_page.my_street.friend_url=/my_street The code above shows the default private page of my_street as false, default friendly URL of my_street as /my_street. Therefore, you can use VM service to generate a URL in order to add videos, games, and playlists into My Street. Adding Struts view page Now we need to build Struts views pages view.jsp, forget_password.jsp, create_account.jsp, congratulation.jsp, and congrates_uid.jsp. The following are main steps to do so: Create a folder my_street in /ext/ext-web/docroot/html/portlet/ext/. Create JSP file pages view.jsp, view_password.jsp, userQuestions.jsp, edit_account.jsp, create_account.jsp, congratulation.jsp, and congrates_uid.jsp in /ext/ext-web/docroot/html/portlet/ext/my_street/. Note that congrates_uid.jsp is used for the pop-up congratulation of Add to My Street. When you click on the Add to My Street button, a window with congrates_uid.jsp will pop up. userQuestions.jsp is used when the user has forgotten the password of My Street, view_password.jsp is for general view of My Street, congratulation.jsp is used to represent successful information after creating user account, and edit_account.jsp is used to create/update user account.

Implementing Tables and Lists The first actual sample is very common in applications: tables and lists. In this sample, the table is populated using the DWR utility functions, and a remoted Java class. The sample code also shows how DWR is used to do inline table editing. When a table cell is double-clicked, an edit box opens, and it is used to save new cell data. The sample will have country data in a CSV file: country Name, Long Name, two-letter Code, Capital, and user-defined Notes. The user interface for the table sample appears as shown in the following screenshot: Server Code for Tables and Lists The first thing to do is to get the country data. Country data is in a CSV file (named countries.csv and located in the samples Java package). The following is an excerpt of the content of the CSV file (data is from http://www.state.gov ). Short-form name,Long-form name,FIPS Code,CapitalAfghanistan,Islamic Republic of Afghanistan,AF,KabulAlbania,Republic of Albania,AL,TiranaAlgeria,People's Democratic Republic of Algeria,AG,AlgiersAndorra,Principality of Andorra,AN,Andorra la VellaAngola,Republic of Angola,AO,LuandaAntigua andBarbuda,(no long-form name),AC,Saint John'sArgentina,Argentine Republic,AR,Buenos AiresArmenia,Republic of Armenia,AM,Yerevan The CSV file is read each time a client requests country data. Although this is not very efficient, it is good enough here. Other alternatives include an in-memory cache or a real database such as Apache Derby or IBM DB2. As an example, we have created a CountryDB class that is used to read and write the country CSV. We also have another class, DBUtils, which has some helper methods. The DBUtils code is as follows: package samples;import java.io.BufferedReader;import java.io.File;import java.io.FileReader;import java.io.FileWriter;import java.io.IOException;import java.io.InputStream;import java.io.InputStreamReader;import java.io.PrintWriter;import java.util.List;import java.util.Vector;public class DBUtils { private String fileName=null; public void initFileDB(String fileName) { this.fileName=fileName; // copy csv file to bin-directory, for easy // file access File countriesFile = new File(fileName); if (!countriesFile.exists()) { try { List<String> countries = getCSVStrings(null); PrintWriter pw; pw = new PrintWriter(new FileWriter(countriesFile)); for (String country : countries) { pw.println(country); } pw.close(); } catch (IOException e) { e.printStackTrace(); } } } protected List<String> getCSVStrings(String letter) { List<String> csvData = new Vector<String>(); try { File csvFile = new File(fileName); BufferedReader br = null; if(csvFile.exists()) { br=new BufferedReader(new FileReader(csvFile)); } else { InputStream is = this.getClass().getClassLoader() .getResourceAsStream("samples/"+fileName); br=new BufferedReader(new InputStreamReader(is)); br.readLine(); } for (String line = br.readLine(); line != null; line = br.readLine()) { if (letter == null || (letter != null && line.startsWith(letter))) { csvData.add(line); } } br.close(); } catch (IOException ioe) { ioe.printStackTrace(); } return csvData; }} The DBUtils class is a straightforward utility class that returns CSV content as a List of Strings. It also copies the original CSV file to the runtime directory of any application server we might be running. This may not be the best practice, but it makes it easier to manipulate the CSV file, and we always have the original CSV file untouched if and when we need to go back to the original version. The code for CountryDB is given here: package samples;import java.io.FileWriter;import java.io.IOException;import java.io.PrintWriter;import java.util.Arrays;import java.util.List;import java.util.Vector;public class CountryDB { private DBUtils dbUtils = new DBUtils(); private String fileName = "countries.csv"; public CountryDB() { dbUtils.initFileDB(fileName); } public String[] getCountryData(String ccode) { List<String> countries = dbUtils.getCSVStrings(null); for (String country : countries) { if (country.indexOf("," + ccode + ",") > -1) { return country.split(","); } } return new String[0]; } public List<List<String>> getCountries(String startLetter) { List<List<String>> allCountryData = new Vector<List<String>>(); List<String> countryData = dbUtils.getCSVStrings(startLetter); for (String country : countryData) { String[] data = country.split(","); allCountryData.add(Arrays.asList(data)); } return allCountryData; } public String[] saveCountryNotes(String ccode, String notes) { List<String> countries = dbUtils.getCSVStrings(null); try { PrintWriter pw = new PrintWriter(new FileWriter(fileName)); for (String country : countries) { if (country.indexOf("," + ccode + ",") > -1) { if (country.split(",").length == 4) { // no existing notes country = country + "," + notes; } else { if (notes.length() == 0) { country = country.substring(0, country .lastIndexOf(",")); } else { country = country.substring(0, country .lastIndexOf(",")) + "," + notes; } } } pw.println(country); } pw.close(); } catch (IOException ioe) { ioe.printStackTrace(); } String[] rv = new String[2]; rv[0] = ccode; rv[1] = notes; return rv; }} The CountryDB class is a remoted class. The getCountryData() method returns country data as an array of strings based on the country code. The getCountries() method returns all the countries that start with the specified parameter, and saveCountryNotes() saves user added notes to the country specified by the country code. In order to use CountryDB, the following script element must be added to the index.jsp file together with other JavaScript elements. <script type='text/javascript' src='/DWREasyAjax/dwr/interface/CountryDB.js'></script> There is one other Java class that we need to create and remote. That is the AppContent class that was already present in the JavaScript functions of the home page. The AppContent class is responsible for reading the content of the HTML file and parses the possible JavaScript function out of it, so it can become usable by the existing JavaScript functions in index.jsp file. package samples;import java.io.ByteArrayOutputStream;import java.io.IOException;import java.io.InputStream;import java.util.List;import java.util.Vector;public class AppContent { public AppContent() { } public List<String> getContent(String contentId) { InputStream is = this.getClass().getClassLoader().getResourceAsStream( "samples/"+contentId+".html"); String content=streamToString(is); List<String> contentList=new Vector<String>(); //Javascript within script tag will be extracted and sent separately to client for(String script=getScript(content);!script.equals("");script=getScript(content)) { contentList.add(script); content=removeScript(content); } //content list will have all the javascript //functions, last element is executed last //and all other before html content if(contentList.size()>1) { contentList.add(contentList.size()-1, content); } else { contentList.add(content); } return contentList; } public List<String> getLetters() { List<String> letters=new Vector<String>(); char[] l=new char[1]; for(int i=65;i<91;i++) { l[0]=(char)i; letters.add(new String(l)); } return letters; } public String removeScript(String html) { //removes first script element int sIndex=html.toLowerCase().indexOf("<script "); if(sIndex==-1) { return html; } int eIndex=html.toLowerCase().indexOf("</script>")+9; return html.substring(0, sIndex)+html.substring(eIndex); } public String getScript(String html) { //returns first script element int sIndex=html.toLowerCase().indexOf("<script "); if(sIndex==-1) { return ""; } int eIndex=html.toLowerCase().indexOf("</script>")+9; return html.substring(sIndex, eIndex); } public String streamToString(InputStream is) { String content=""; try { ByteArrayOutputStream baos=new ByteArrayOutputStream(); for(int b=is.read();b!=-1;b=is.read()) { baos.write(b); } content=baos.toString(); } catch(IOException ioe) { content=ioe.toString(); } return content; }} The getContent() method reads the HTML code from a file based on the contentId. ContentId was specified in the dwrapplication.properties file, and the HTML is just contentId plus the extension .html in the package directory. There is also a getLetters() method that simply lists letters from A to Z and returns a list of letters to the browser. If we test the application now, we will get an error as shown in the following screenshot: We know why the AppContent is not defined error occurs, so let's fix it by adding AppContent to the allow element in the dwr.xml file. We also add CountryDB to the allow element. The first thing we do is to add required elements to the dwr.xml file. We add the following creators within the allow element in the dwr.xml file. <create creator="new" javascript="AppContent"> <param name="class" value="samples.AppContent" /> <include method="getContent" /> <include method="getLetters" /> </create> <create creator="new" javascript="CountryDB"> <param name="class" value="samples.CountryDB" /> <include method="getCountries" /> <include method="saveCountryNotes" /> <include method="getCountryData" /></create> We explicitly define the methods we are remoting using the include elements. This is a good practice, as we don't accidentally allow access to any methods that are not meant to be remoted. Client Code for Tables and Lists We also need to add a JavaScript interface to the index.jsp page. Add the following with the rest of the scripts in the index.jsp file. <script type='text/javascript' src='/DWREasyAjax/dwr/interface/AppContent.js'></script> Before testing, we need the sample HTML for the content area. The following HTML is in the TablesAndLists.html file under the samples directory: <h3>Countries</h3><p>Show countries starting with <select id="letters" onchange="selectLetter(this);return false;"> </select><br/>Doubleclick "Notes"-cell to add notes to country.</p><table border="1"> <thead> <tr> <th>Name</th> <th>Long name</th> <th>Code</th> <th>Capital</th> <th>Notes</th> </tr> </thead> <tbody id="countryData"> </tbody></table><script type='text/javascript'>//TO BE EVALEDAppContent.getLetters(addLetters);</script> The script element at the end is extracted by our Java class, and it is then evaluated by the browser when the client-side JavaScript receives the HTML. There is the select element, and its onchange event calls the selectLetter() JavaScript function. We will implement the selectLetter() function shortly. JavaScript functions are added in the index.jsp file, and within the head element. Functions could be in separate JavaScript files, but the embedded script is just fine here. function selectLetter(selectElement){ var selectedIndex = selectElement.selectedIndex; var selectedLetter= selectElement.options[selectedIndex ].value; CountryDB.getCountries(selectedLetter,setCountryRows);}function addLetters(letters){dwr.util.addOptions('letters',['letter...']);dwr.util.addOptions('letters',letters);}function setCountryRows(countryData){var cellFuncs = [ function(data) { return data[0]; }, function(data) { return data[1]; }, function(data) { return data[2]; }, function(data) { return data[3]; }, function(data) { return data[4]; }];dwr.util.removeAllRows('countryData');dwr.util.addRows( 'countryData',countryData,cellFuncs, { cellCreator:function(options) { var td = document.createElement("td"); if(options.cellNum==4) { var notes=options.rowData[4]; if(notes==undefined) { notes='&nbsp;';// + options.rowData[2]+'notes'; } var ccode=options.rowData[2]; var divId=ccode+'_Notes'; var tdId=divId+'Cell'; td.setAttribute('id',tdId); var html=getNotesHtml(ccode,notes); td.innerHTML=html; options.data=html; } return td; }, escapeHtml:false });}function getNotesHtml(ccode,notes){ var divId=ccode+'_Notes'; return "<div onDblClick="editCountryNotes('"+divId+"','"+ccode+"');" id=""+divId+"">"+notes+"</div>";}function editCountryNotes(id,ccode){ var notesElement=dwr.util.byId(id); var tdId=id+'Cell'; var notes=notesElement.innerHTML; if(notes=='&nbsp;') { notes=''; } var editBox='<input id="'+ccode+'NotesEditBox" type="text" value="'+notes+'"/><br/>'; editBox+="<input type='button' id='"+ccode+"SaveNotesButton' value='Save' onclick='saveCountryNotes(""+ccode+"");'/>"; editBox+="<input type='button' id='"+ccode+"CancelNotesButton' value='Cancel' onclick='cancelEditNotes(""+ccode+"");'/>"; tdElement=dwr.util.byId(tdId); tdElement.innerHTML=editBox; dwr.util.byId(ccode+'NotesEditBox').focus();}function cancelEditNotes(ccode){ var countryData=CountryDB.getCountryData(ccode, { callback:function(data) { var notes=data[4]; if(notes==undefined) { notes='&nbsp;'; } var html=getNotesHtml(ccode,notes); var tdId=ccode+'_NotesCell'; var td=dwr.util.byId(tdId); td.innerHTML=html; } });}function saveCountryNotes(ccode){ var editBox=dwr.util.byId(ccode+'NotesEditBox'); var newNotes=editBox.value; CountryDB.saveCountryNotes(ccode,newNotes, { callback:function(newNotes) { var ccode=newNotes[0]; var notes=newNotes[1]; var notesHtml=getNotesHtml(ccode,notes); var td=dwr.util.byId(ccode+"_NotesCell"); td.innerHTML=notesHtml; } });} There are lots of functions for table samples, and we go through each one of them. The first is the selectLetter() function. This function gets the selected letter from the select element and calls the CountryDB.getCountries() remoted Java method. The callback function is setCountryRows. This function receives the return value from the Java getCountries() method, that is List<List<String>>, a List of Lists of Strings. The second function is addLetters(letters), and it is a callback function for theAppContent.getLetters() method, which simply returns letters from A to Z. The addLetters() function uses the DWR utility functions to populate the letter list. Then there is a callback function for the CountryDB.getCountries() method. The parameter for the function is an array of countries that begin with a specified letter. Each array element has a format: Name, Long name, (country code) Code, Capital, Notes. The purpose of this function is to populate the table with country data; and let's see how it is done. The variable, cellFuncs, holds functions for retrieving data for each cell in a column. The parameter named data is an array of country data that was returned from the Java class. The table is populated using the DWR utility function, addRows(). The cellFuncs variable is used to get the correct data for the table cell. The cellCreator function is used to create custom HTML for the table cell. Default implementation generates just a td element, but our custom implementation generates the td-element with the div placeholder for user notes. The getNotesHtml() function is used to generate the div element with the event listener for double-click. The editCountryNotes() function is called when the table cell is double-clicked. The function creates input fields for editing notes with the Save and Cancel buttons. The cancelEditNotes() and saveCountryNotes() functions cancel the editing of new notes, or saves them by calling the CountryDB.saveCountryNotes() Java method. The following screenshot shows what the sample looks like with the populated table: Now that we have added necessary functions to the web page we can test the application. Testing Tables and Lists The application should be ready for testing if we have had the test environment running during development. Eclipse automatically deploys our new code to the server whenever something changes. So we can go right away to the test page http://127.0.0.1:8080/DWREasyAjax. On clicking Tables and lists we can see the page we have developed. By selecting some letter, for example "I" we get a list of all the countries that start with letter "I" (as shown in the previous screenshot). Now we can add notes to countries. We can double-click any table cell under Notes. For example, if we want to enter notes to Iceland, we double-click the Notes cell in Iceland's table row, and we get the edit box for the notes as shown in the following screenshot: The edit box is a simple text input field. We didn't use any forms. Saving and canceling editing is done using JavaScript and DWR. If we press Cancel, we get the original notes from the CountryDB Java class using DWR and saving also uses DWR to save data. CountryDB.saveCountryNotes() takes the country code and the notes that the user entered in the edit box and saves them to the CSV file. When notes are available, the application will show them in the country table together with other country information as shown in the following screenshot: Afterword The sample in this section uses DWR features to get data for the table and list from the server. We developed the application so that most of the application logic is written in JavaScript and Java beans that are remoted. In principle, the application logic can be thought of as being fully browser based, with some extensions in the server. Implementing Field Completion Nowadays, field completion is typical of many web pages. A typical use case is getting a stock quote, and field completion shows matching symbols as users type letters. Many Internet sites use this feature. Our sample here is a simple license text finder. We enter the license name in the input text field, and we use DWR to show the license names that start with the typed text. A list of possible completions is shown below the input field. The following is a screenshot of the field completion in action: Selected license content is shown in an iframe element from http://www.opensource.org. Server Code for Field Completion We will re-use some of the classes we developed in the last section. AppContent is used to load the sample page, and the DBUtils class is used in the LicenseDB class. The LicenseDB class is shown here: package samples;import java.util.List;import java.util.Vector;public class LicenseDB{ private DBUtils dbUtils=new DBUtils(); public LicenseDB() { dbUtils.initFileDB("licenses.csv"); } public List<String> getLicensesStartingWith(String startLetters) { List<String> list=new Vector<String>(); List<String> licenses=dbUtils.getCSVStrings(startLetters); for(String license : licenses) { list.add(license.split(",")[0]); } return list; } public String getLicenseContentUrl(String licenseName) { List<String> licenses=dbUtils.getCSVStrings(licenseName); if(licenses.size()>0) { return licenses.get(0).split(",")[1]; } return ""; }} The getLicenseStartingWith() method goes through the license names and returns valid license names and their URLs. Similar to the data in the previous section, license data is in a CSV file named licenses.csv in the package directory. The following is an excerpt of the file content: Academic Free License, http://opensource.org/licenses/afl-3.0.phpAdaptive Public License, http://opensource.org/licenses/apl1.0.phpApache Software License, http://opensource.org/licenses/apachepl-1.1.phpApache License, http://opensource.org/licenses/apache2.0.phpApple Public Source License, http://opensource.org/licenses/apsl-2.0.phpArtistic license, http://opensource.org/licenses/artistic-license-1.0.php... There are quite a few open-source licenses. Some are more popular than others (like the Apache Software License) and some cannot be re-used (like the IBM Public License). We want to remote the LicenseDB class, so we add the following to the dwr.xml file. <create creator="new" javascript="LicenseDB"> <param name="class" value="samples.LicenseDB"/> <include method="getLicensesStartingWith"/> <include method="getLicenseContentUrl"/></create> Client Code for Field Completion The following script element will go in the index.jsp page. <script type='text/javascript' src='/DWREasyAjax/dwr/interface/LicenseDB.js'></script> The HTML for the field completion is as follows: <h3>Field completion</h3><p>Enter Open Source license name to see it's contents.</p><input type="text" id="licenseNameEditBox" value="" onkeyup="showPopupMenu()" size="40"/><input type="button" id="showLicenseTextButton" value="Show license text" onclick="showLicenseText()"/><div id="completionMenuPopup"></div><div id="licenseContent"></div> The input element, where we enter the license name, listens to the onkeyup event which calls the showPopupMenu() JavaScript function. Clicking the Input button calls the showLicenseText() function (the JavaScript functions are explained shortly). Finally, the two div elements are place holders for the pop-up menu and the iframe element that shows license content. For the pop-up box functionality, we use existing code and modify it for our purpose (many thanks to http://www.jtricks.com). The following is the popup.js file, which is located under the WebContent | js directory. //<script type="text/javascript"><!--/* Original script by: www.jtricks.com * Version: 20070301 * Latest version: * www.jtricks.com/javascript/window/box.html * * Modified by Sami Salkosuo. */// Moves the box object to be directly beneath an object.function move_box(an, box){ var cleft = 0; var ctop = 0; var obj = an; while (obj.offsetParent) { cleft += obj.offsetLeft; ctop += obj.offsetTop; obj = obj.offsetParent; } box.style.left = cleft + 'px'; ctop += an.offsetHeight + 8; // Handle Internet Explorer body margins, // which affect normal document, but not // absolute-positioned stuff. if (document.body.currentStyle && document.body.currentStyle['marginTop']) { ctop += parseInt( document.body.currentStyle['marginTop']); } box.style.top = ctop + 'px';}var popupMenuInitialised=false;// Shows a box if it wasn't shown yet or is hidden// or hides it if it is currently shownfunction show_box(html, width, height, borderStyle,id){ // Create box object through DOM var boxdiv = document.getElementById(id); boxdiv.style.display='block'; if(popupMenuInitialised==false) { //boxdiv = document.createElement('div'); boxdiv.setAttribute('id', id); boxdiv.style.display = 'block'; boxdiv.style.position = 'absolute'; boxdiv.style.width = width + 'px'; boxdiv.style.height = height + 'px'; boxdiv.style.border = borderStyle; boxdiv.style.textAlign = 'right'; boxdiv.style.padding = '4px'; boxdiv.style.background = '#FFFFFF'; boxdiv.style.zIndex='99'; popupMenuInitialised=true; //document.body.appendChild(boxdiv); } var contentId=id+'Content'; var contents = document.getElementById(contentId); if(contents==null) { contents = document.createElement('div'); contents.setAttribute('id', id+'Content'); contents.style.textAlign= 'left'; boxdiv.contents = contents; boxdiv.appendChild(contents); } move_box(html, boxdiv); contents.innerHTML= html; return false;}function hide_box(id){ document.getElementById(id).style.display='none'; var boxdiv = document.getElementById(id+'Content'); if(boxdiv!=null) { boxdiv.parentNode.removeChild(boxdiv); } return false;}//--></script> Functions in the popup.js file are used as menu options directly below the edit box. The show_box() function takes the following arguments: HTML code for the pop-up, position of the pop-up window, and the "parent" element (to which the pop-up box is related). The function then creates a pop-up window using DOM. The move_box() function is used to move the pop-up window to its correct place under the edit box and the hide_box() function hides the pop-up window by removing the pop-up window from the DOM tree. In order to use functions in popup.js, we need to add the following script-element to the index.jsp file: <script type='text/javascript' src='js/popup.js'></script> Our own JavaScript code for the field completion is in the index.jsp file. The following are the JavaScript functions, and an explanation follows the code: function showPopupMenu(){ var licenseNameEditBox=dwr.util.byId('licenseNameEditBox'); var startLetters=licenseNameEditBox.value; LicenseDB.getLicensesStartingWith(startLetters, { callback:function(licenses) { var html=""; if(licenses.length==0) { return; } if(licenses.length==1) { hidePopupMenu(); licenseNameEditBox.value=licenses[0]; } else { for (index in licenses) { var licenseName=licenses[index];//.split(",")[0]; licenseName=licenseName.replace(/"/g,"&quot;"); html+="<div style="border:1px solid #777777;margin-bottom:5;" onclick="completeEditBox('"+licenseName+"');">"+licenseName+"</div>"; } show_box(html, 200, 270, '1px solid','completionMenuPopup'); } } });}function hidePopupMenu(){ hide_box('completionMenuPopup');}function completeEditBox(licenseName){ var licenseNameEditBox=dwr.util.byId('licenseNameEditBox'); licenseNameEditBox.value=licenseName; hidePopupMenu(); dwr.util.byId('showLicenseTextButton').focus();}function showLicenseText(){ var licenseNameEditBox=dwr.util.byId('licenseNameEditBox'); licenseName=licenseNameEditBox.value; LicenseDB.getLicenseContentUrl(licenseName,{ callback:function(licenseUrl) { var html='<iframe src="'+licenseUrl+'" width="100%" height="600"></iframe>'; var content=dwr.util.byId('licenseContent'); content.style.zIndex="1"; content.innerHTML=html; } });} The showPopupMenu() function is called each time a user enters a letter in the input box. The function gets the value of the input field and calls the LicenseDB. getLicensesStartingWith() method. The callback function is specified in the function parameters. The callback function gets all the licenses that match the parameter, and based on the length of the parameter (which is an array), it either shows a pop-up box with all the matching license names, or, if the array length is one, hides the pop-up box and inserts the full license name in the text field. In the pop up box, the license names are wrapped within the div element that has an onclick event listener that calls the completeEditBox() function. The hidePopupMenu() function just closes the pop-up menu and the competeEditBox() function inserts the clicked license text in the input box and moves the focus to the button. The showLicenseText() function is called when we click the Show license text button. The function calls the LicenseDB. getLicenseContentUrl() method and the callback function creates an iframe element to show the license content directly from http://www.opensource.org, as shown in the following screenshot: Afterword Field completion improves user experience in web pages and the sample code in this section showed one way of doing it using DWR. It should be noted that the sample for field completion presented here is only for demonstration purposes.

Introduction Is it possible to easily run a half-dozen internet services on a single piece of hardware and make sure that if one is compromised the others will remain unharmed? Can this be done without a mountain of administrative overhead and customization? Can I configure my services the way I have grown accustomed? Absolutely! This article will outline how to achieve this, through the use of FreeBSD Jails. Over the course of this article I will outline how to install a list of production services on a single piece of hardware, securing each one from the next, all with only one additional administrative tool: ezjail Before we get to the ezjail tool we need to define FreeBSD Jails. What are they? What do they do? Why do I care? FreeBSD Jails are a kernel-level security tool used widely in the FreeBSD community to segregate processes. An easy way to think of a Jail is that it is very much like a chroot environment, but much more hardened. While a standard chroot environment can often be escaped, FreeBSD has added code to their kernel which hardens the chroot environment into a "Jail"—Inescapable. Within this Jailed environment processes are unable to identify, access or otherwise communicate with processes on the outside of the Jail. Networking is limited within the Jail as well. A Jail cannot affect any underlying network configuration other than that which it has been assigned. A Jail can also be thought of in many ways like a virtualized machine in that the virtual "guest" cannot interact with the physical "host". Jails allow us the opportunity to run processes in a secure manner separate from our host environment. If that sounds appealing to you may be wondering how to activate and use this Jail system. That, my friend, is the focus of this article. Get settled because by the time we're done here you will have all the tools you need to segregate processes for security, sandboxing or even create custom environments for other users. By default the Jail system is part of the FreeBSD kernel. The kernel customizations to make the system possible have such a minimal footprint that it was decided it should be a default, always-on feature of FreeBSD. Your FreeBSD installation already has the ability to do everything described above, you just need to know how to use it. Ezjail The tool that I use to create, manage and interact with my FreeBSD jails is called "ezjail". It simplifies much of the underlying configuration of a Jail system to the extent that you can create a Jail and be working within it in just three steps! To install the ezjail port you need to make sure you have your ports tree updated and then run: cd /usr/ports/sysutils/ezjail && make install clean Before we can create any Jails we'll need to create the base Jail environment. This is the template environment from which all other Jails will be created. This is simplified by the ezjail-admin tool: ezjail-admin install This will download the components of a base Jail system. Also, the -m, -s and -p options install the man pages, source packages and ports tree respectively. If you want access to these within your Jail environments then be sure to append them to the command above. Before any Jails will be able to start you'll also need to activate the ezjail system within the /etc/rc.conf. This is done using the command: echo 'ezjail_enable="YES"' >> /etc/rc.conf" Now that you've got the proper tools installed you'll need to keep in mind the following requirements for creating and maintaining a Jail environment. These items will need to be defined for each Jail environment that you want to create, and will need to persist for the life of the Jail. jailname IP Address(es) custom jailroot (storage directory) - optional For our purposes here we'll create three Jail environments. We'll call them "larry", "curly" and "moe". Each of these will be assigned an IP address on our internal LAN using "192.168.0.100", "192.168.0.101" and "192.168.0.102" respectively. We will also use the default jailroot path, which is /usr/jails/jailname/. Based on the above decisions we have three of the items defined, but only two configured. We will need to create interface aliases for our network device which will bind to the addresses we've decided on. There are two ways this can be done. The first method, the temporary method, will work for quickly testing Jails and creating environments that you don't need to keep. The second method, the persistent method, will define the interface aliases in your system configuration and define them persistently across reboots. The persistent method is what you will need if you plan on continuing to use your Jails long term. Note: FreeBSD defines network interfaces by their device name or module name. Replace hme0 with your interface name as required. Temporary Network Alias To create temporary network aliases for the three Jails you would run these commands (replacing each IP as needed): ifconfig hme0 alias 192.168.0.100 netmask 255.255.255.255ifconfig hme0 alias 192.168.0.101 netmask 255.255.255.255ifconfig hme0 alias 192.168.0.102 netmask 255.255.255.255 Persistent Network Alias To create persistent network aliases (aliases that will persist across reboots) you would add the following to your /etc/rc.conf file (replacing your IP as needed): ifconfig_hme0_alias0="inet 192.168.0.100/32"ifconfig_hme0_alias1="inet 192.168.0.101/32"ifconfig_hme0_alias2="inet 192.168.0.102/32" Creating a Jail environment Once you have activated your network aliases and the third and final configuration requirement is met we're ready to create these Jails. You can create a Jail environment using the command below. Repeat for each Jail, replacing jailname and jailip as needed: ezjail-admin create jailname jailip In this situation we would have run the following commands: ezjail-admin create larry 192.168.0.100ezjail-admin create moe 192.168.0.101ezjail-admin create curly 192.168.0.102 You will see a bunch of output on your screen. This is normal. The output shows that files are being put into place and underlying configuration is happening. Remember, without the ezjail-admin tool you'd need to do that configuration by hand. No thanks! Your Jail environments are now ready to use! Wasn't that easy! To move from you host system to your Jail environment simply use the command: ezjail-admin console jailname This command will give you a console connection into the Jail environment. It will act just as if you had sat down and logged into the machine (although no login credentials are required). You should now be within one of your Jails, logged in as root, with a base FreeBSD system. No ports are installed. Nothing is configured. None of the host customizations are adopted. You have a pristine, minimal FreeBSD installation to begin building your services. Configure and activate SSH Let's configure a service and make this Jail more accessible. First, SSH: SSH is part of the base FreeBSD installation so all we need to do is configure and activate the service. It should work out of the box, but it can't hurt to take a look at the configuration for the SSH daemon, located in /etc/ssh/sshd_config. You may want to update the following lines: ListenAddress 0.0.0.0 For this example we'll change the port to 2200 and the ListenAddress to 192.168.0.100. Update the Jail environment to launch the SSH daemon at startup by adding the following line to your /etc/rc.conf: sshd_enable="YES" Finally, start the service manually by running: /etc/rc.d/sshd start If you now logout of your Jail (type "exit") and take a look at your host system using netstat you should find that it is listening on 192.168.0.100:2200—the host address and port (assuming ssh is configured on the host system). You can find this information using: netstat -nat | less