f9_2009_jun_q

March 2009High Quality DXT Compression using OpenCL for CUDAIgnacio Castaño*******************Document Change HistoryVersion Date Responsible Reason for Change0.1 02/01/2007 Ignacio Castaño First draft0.2 19/03/2009 Timo Stich OpenCL versionAbstractDXT is a fixed ratio compression format designed for real-time hardware decompression of textures. While it’s also possible to encode DXT textures in real-time, the quality of the resulting images is far from the optimal. In this white paper we will overview a more expensivecompression algorithm that produces high quality results and we will see how to implement it using CUDA to obtain much higher performance than the equivalent CPU implementation.MotivationWith the increasing number of assets and texture size in recent games, the time required to process those assets is growing dramatically. DXT texture compression takes a large portion of this time. High quality DXT compression algorithms are very expensive and while there are faster alternatives [1][9], the resulting quality of those simplified methods is not very high. The brute force nature of these compression algorithms makes them suitable to be parallelized and adapted to the GPU. Cheaper compression algorithms have already been implemented [2] on the GPU using traditional GPGPU approaches. However, with the traditional GPGPU programming model it’s not possible to implement more complex algorithms where threads need to share data and synchronize.How Does It Work?In this paper we will see how to use CUDA to implement a high quality DXT1 texturecompression algorithm in parallel. The algorithm that we have chosen is the cluster fit algorithm as described by Simon Brown [3]. We will first provide a brief overview of the algorithm and then we will describe how did we parallelize and implement it in CUDA.DXT1 FormatDXT1 is a fixed ratio compression scheme that partitions the image into 4x4 blocks. Each block is encoded with two 16 bit colors in RGB 5-6-5 format and a 4x4 bitmap with 2 bits per pixel. Figure 1 shows the layout of the block.Figure 1. DXT1 block layoutThe block colors are reconstructed by interpolating one or two additional colors between the given ones and indexing these and the original colors with the bitmap bits. The number of interpolated colors is chosen depending on whether the value of ‘Color 0’ is lower or greater than ‘Color 1’.BitmapColorsThe total size of the block is 64 bits. That means that this scheme achieves a 6:1 compression ratio. For more details on the DXT1 format see the specification of the OpenGL S3TCextension [4].Cluster FitIn general, finding the best two points that minimize the error of a DXT encoded block is a highly discontinuous optimization problem. However, if we assume that the indices of the block are known the problem becomes a linear optimization problem instead: minimize the distance from each color of the block to the corresponding color of the palette.Unfortunately, the indices are not known in advance. We would have to test them all to find the best solution. Simon Brown [3] suggested pruning the search space by considering only the indices that preserve the order of the points along the least squares line.Doing that allows us to reduce the number of indices for which we have to optimize theendpoints. Simon Brown provided a library [5] that implements this algorithm. We use thislibrary as a reference to compare the correctness and performance of our CUDAimplementation.The next section goes over the implementation details.OpenCL ImplementationPartitioning the ProblemWe have chosen to use a single work group to compress each 4x4 color block. Work items that process a single block need to cooperate with each other, but DXT blocks are independent and do not need synchronization or communication. For this reason the number of workgroups is equal to the number of blocks in the image.We also parameterize the problem so that we can change the number of work items per block to determine what configuration provides better performance. For now, we will just say that the number of work items is N and later we will discuss what the best configuration is.During the first part of the algorithm, only 16 work items out of N are active. These work items start reading the input colors and loading them to local memory.Finding the best fit lineTo find a line that best approximates a set of points is a well known regression problem. The colors of the block form a cloud of points in 3D space. This can be solved by computing the largest eigenvector of the covariance matrix. This vector gives us the direction of the line.Each element of the covariance matrix is just the sum of the products of different colorcomponents. We implement these sums using parallel reductions.Once we have the covariance matrix we just need to compute its first eigenvector. We haven’t found an efficient way of doing this step in parallel. Instead, we use a very cheap sequential method that doesn’t add much to the overall execution time of the group.Since we only need the dominant eigenvector, we can compute it directly using the Power Method [6]. This method is an iterative method that returns the largest eigenvector and only requires a single matrix vector product per iteration. Our tests indicate that in most cases 8iterations are more than enough to obtain an accurate result.Once we have the direction of the best fit line we project the colors onto it and sort them along the line using brute force parallel sort. This is achieved by comparing all the elements against each other as follows:cmp[tid] = (values[0] < values[tid]);cmp[tid] += (values[1] < values[tid]);cmp[tid] += (values[2] < values[tid]);cmp[tid] += (values[3] < values[tid]);cmp[tid] += (values[4] < values[tid]);cmp[tid] += (values[5] < values[tid]);cmp[tid] += (values[6] < values[tid]);cmp[tid] += (values[7] < values[tid]);cmp[tid] += (values[8] < values[tid]);cmp[tid] += (values[9] < values[tid]);cmp[tid] += (values[10] < values[tid]);cmp[tid] += (values[11] < values[tid]);cmp[tid] += (values[12] < values[tid]);cmp[tid] += (values[13] < values[tid]);cmp[tid] += (values[14] < values[tid]);cmp[tid] += (values[15] < values[tid]);The result of this search is an index array that references the sorted values. However, this algorithm has a flaw, if two colors are equal or are projected to the same location of the line, the indices of these two colors will end up with the same value. We solve this problem comparing all the indices against each other and incrementing one of them if they are equal:if (tid > 0 && cmp[tid] == cmp[0]) ++cmp[tid];if (tid > 1 && cmp[tid] == cmp[1]) ++cmp[tid];if (tid > 2 && cmp[tid] == cmp[2]) ++cmp[tid];if (tid > 3 && cmp[tid] == cmp[3]) ++cmp[tid];if (tid > 4 && cmp[tid] == cmp[4]) ++cmp[tid];if (tid > 5 && cmp[tid] == cmp[5]) ++cmp[tid];if (tid > 6 && cmp[tid] == cmp[6]) ++cmp[tid];if (tid > 7 && cmp[tid] == cmp[7]) ++cmp[tid];if (tid > 8 && cmp[tid] == cmp[8]) ++cmp[tid];if (tid > 9 && cmp[tid] == cmp[9]) ++cmp[tid];if (tid > 10 && cmp[tid] == cmp[10]) ++cmp[tid];if (tid > 11 && cmp[tid] == cmp[11]) ++cmp[tid];if (tid > 12 && cmp[tid] == cmp[12]) ++cmp[tid];if (tid > 13 && cmp[tid] == cmp[13]) ++cmp[tid];if (tid > 14 && cmp[tid] == cmp[14]) ++cmp[tid];During all these steps only 16 work items are being used. For this reason, it’s not necessary to synchronize them. All computations are done in parallel and at the same time step, because 16 is less than the warp size on NVIDIA GPUs.Index evaluationAll the possible ways in which colors can be clustered while preserving the order on the line are known in advance and for each clustering there’s a corresponding index. For 4 clusters there are 975 indices that need to be tested, while for 3 clusters there are only 151. We pre-compute these indices and store them in global memory.We have to test all these indices and determine which one produces the lowest error. In general there are indices than work items. So, we partition the total number of indices by the number of work items and each work item loops over the set of indices assigned to it. It’s tempting to store the indices in constant memory, but since indices are used only once for each work group, and since each work item accesses a different element, coalesced global memory loads perform better than constant loads.Solving the Least Squares ProblemFor each index we have to solve an optimization problem. We have to find the two end points that produce the lowest error. For each input color we know what index it’s assigned to it, so we have 16 equations like this:i i i x b a =+βαWhere {}i i βα, are {}0,1, {}32,31, {}21,21, {}31,32 or {}1,0 depending on the index and the interpolation mode. We look for the colors a and b that minimize the least square error of these equations. The solution of that least squares problem is the following:∑∑⋅∑∑∑∑= −i i i i i ii i i i x x b a βαββαβαα122 Note: The matrix inverse is constant for each index set, but it’s cheaper to compute it everytime on the kernel than to load it from global memory. That’s not the case of the CPU implementation.Computing the ErrorOnce we have a potential solution we have to compute its error. However, colors aren’t stored with full precision in the DXT block, so we have to quantize them to 5-6-5 to estimate the error accurately. In addition to that, we also have to take in mind that the hardware expands thequantized color components to 8 bits replicating the highest bits on the lower part of the byte as follows:R = (R << 3) | (R >> 2); G = (G << 2) | (G >> 4); B = (B << 3) | (B >> 2);Converting the floating point colors to integers, clamping, bit expanding and converting them back to float can be time consuming. Instead of that, we clamp the color components, round the floats to integers and approximate the bit expansion using a multiplication. We found the factors that produce the lowest error using an offline optimization that minimized the average error.r = round(clamp(r,0.0f,1.0f) * 31.0f); g = round(clamp(g,0.0f,1.0f) * 63.0f); b = round(clamp(b,0.0f,1.0f) * 31.0f); r *= 0.03227752766457f; g *= 0.01583151765563f; b *= 0.03227752766457f;Our experiment show that in most cases the approximation produces the same solution as the accurate solution.Selecting the Best SolutionFinally, each work item has evaluated the error of a few indices and has a candidate solution.To determine which work item has the solution that produces the lowest error, we store the errors in local memory and use a parallel reduction to find the minimum. The winning work item writes the endpoints and indices of the DXT block back to global memory.Implementation DetailsThe source code is divided into the following files:•DXTCompression.cl: This file contains OpenCL implementation of the algorithm described here.•permutations.h: This file contains the code used to precompute the indices.dds.h: This file contains the DDS file header definition. PerformanceWe have measured the performance of the algorithm on different GPUs and CPUscompressing the standard Lena. The design of the algorithm makes it insensitive to the actual content of the image. So, the performance depends only on the size of the image.Figure 2. Standard picture used for our tests.As shown in Table 1, the GPU compressor is at least 10x faster than our best CPUimplementation. The version of the compressor that runs on the CPU uses a SSE2 optimized implementation of the cluster fit algorithm. This implementation pre-computes the factors that are necessary to solve the least squares problem, while the GPU implementation computes them on the fly. Without this CPU optimization the difference between the CPU and GPU version is even larger.Table 1. Performance ResultsImage TeslaC1060 Geforce8800 GTXIntel Core 2X6800AMD Athlon64 DualCore 4400Lena512x51283.35 ms 208.69 ms 563.0 ms 1,251.0 msWe also experimented with different number of work-items, and as indicated in Table 2 we found out that it performed better with the minimum number.Table 2. Number of Work Items64 128 25654.66 ms 86.39 ms 96.13 msThe reason why the algorithm runs faster with a low number of work items is because during the first and last sections of the code only a small subset of work items is active.A future improvement would be to reorganize the code to eliminate or minimize these stagesof the algorithm. This could be achieved by loading multiple color blocks and processing them in parallel inside of the same work group.ConclusionWe have shown how it is possible to use OpenCL to implement an existing CPU algorithm in parallel to run on the GPU, and obtain an order of magnitude performance improvement. We hope this will encourage developers to attempt to accelerate other computationally-intensive offline processing using the GPU.High Quality DXT Compression using CUDAMarch 2009 9References[1] “Real-Time DXT Compression”, J.M.P. van Waveren. /cd/ids/developer/asmo-na/eng/324337.htm[2] “Compressing Dynamically Generated Textures on the GPU”, Oskar Alexandersson, Christoffer Gurell, Tomas Akenine-Möller. http://graphics.cs.lth.se/research/papers/gputc2006/[3] “DXT Compression Techniques”, Simon Brown. /?article=dxt[4] “OpenGL S3TC extension spec”, Pat Brown. /registry/specs/EXT/texture_compression_s3tc.txt[5] “Squish – DXT Compression Library”, Simon Brown. /?code=squish[6] “Eigenvalues and Eigenvectors”, Dr. E. Garcia. /information-retrieval-tutorial/matrix-tutorial-3-eigenvalues-eigenvectors.html[7] “An Experimental Analysis of Parallel Sorting Algorithms”, Guy E. Blelloch, C. Greg Plaxton, Charles E. Leiserson, Stephen J. Smith /blelloch98experimental.html[8] “NVIDIA CUDA Compute Unified Device Architecture Programming Guide”.[9] NVIDIA OpenGL SDK 10 “Compress DXT” sample /SDK/10/opengl/samples.html#compress_DXTNVIDIA Corporation2701 San Tomas ExpresswaySanta Clara, CA 95050 NoticeALL NVIDIA DESIGN SPECIFICATIONS, REFERENCE BOARDS, FILES, DRAWINGS, DIAGNOSTICS, LISTS, AND OTHER DOCUMENTS (TOGETHER AND SEPARATELY, “MATERIALS”) ARE BEING PROVIDED “AS IS.” NVIDIA MAKES NO WARRANTIES, EXPRESSED, IMPLIED, STATUTORY, OR OTHERWISE WITH RESPECT TO THE MATERIALS, AND EXPRESSLY DISCLAIMS ALL IMPLIED WARRANTIES OF NONINFRINGEMENT,MERCHANTABILITY, AND FITNESS FOR A PARTICULAR PURPOSE.Information furnished is believed to be accurate and reliable. However, NVIDIA Corporation assumes no responsibility for the consequences of use of such information or for any infringement of patents or otherrights of third parties that may result from its use. No license is granted by implication or otherwise under any patent or patent rights of NVIDIA Corporation. Specifications mentioned in this publication are subject to change without notice. This publication supersedes and replaces all information previously supplied. NVIDIA Corporation products are not authorized for use as critical components in life support devices or systems without express written approval of NVIDIA Corporation.TrademarksNVIDIA, the NVIDIA logo, GeForce, and NVIDIA Quadro are trademarks or registeredtrademarks of NVIDIA Corporation in the United States and other countries. Other company and product names may be trademarks of the respective companies with which they are associated. Copyright© 2009 NVIDIA Corporation. All rights reserved.。

Introduction _______________________________________________________________ 2 About Autodesk® Stitcher™ Unlimited 2009 _________________________________________ 2 Goal of this tutorial ___________________________________________________________ 2 1.Loading Images _________________________________________________________ 3The Autodesk Stitcher Unlimited 2009 interface ____________________________________ 3 Loading your shots ____________________________________________________________ 4 2.Stitching _______________________________________________________________ 6Auto-Stitching ________________________________________________________________ 6 Manual-Stitching _____________________________________________________________ 73.Alignment ____________________________________________________________ 124.Equalization ___________________________________________________________ 145.Render _______________________________________________________________ 15Render area and rendering options ______________________________________________ 15Load a Spherical panorama and convert to QTVR® _________________________________ 20IntroductionAbout Autodesk® Stitcher™ Unlimited 2009Autodesk Stitcher Unlimited 2009 is the way to build high-quality panoramas for the Web, film, print, and 3D.With advanced features, Autodesk Stitcher Unlimited 2009 gives photographers and artists the power to deliver the most impressive panoramas in the formats they need. Autodesk Stitcher creates wide-angle, high-resolution 360° × 180° panoramic images in seconds from horizontally and vertically overlapping photos. You can create new image sets from the panorama using a virtual camera with zoom, pan, and roll motion. Results can be rendered as a cube, plane, cylinder, sphere projection, and as a QuickTime® movie (Cylindrical QTVR and Cubic QTVR), and in VRML format for creating high-impact Web pages, definition mattes, environment maps, and 3D models.Goal of this tutorialThis 5-step tutorial will guide you through the creation of a full spherical panorama image in 360° and a QTVR interactive file. This complete project will take you step by step through the main workflow and features of Autodesk Stitcher Unlimited 2009.This tutorial was prepared and illustrated using Autodesk Stitcher Unlimited 2009.1. Loading ImagesThe Autodesk Stitcher Unlimited 2009 interfaceLoading your shotsThe first step in the creation of a Stitcher project is to load the image files that you will use to create the panorama.To load the pictures:1.Select File > Load Images from the main menu or click the Load Images icon in the toolbar.The Load Images browser opens.2.Select all the files you will use for your panorama.3.Click Open.When the images are loaded, Stitcher tries to read the EXIF data and proposes theappropriate camera lens type. The following dialog lets you choose between keeping thesettings and adjusting them manually.The current example use images with EXIF data so Stitcher correctly detects all theparameters. Click Yes to keep the settings Stitcher has read.NOTE If for any reason you need to change the parameters you can right-click anywhereinside the Stitcher interface and open the Properties dialog.Your images will be loaded into the Thumbnail View, as demonstrated in the screen shot below.Open the loaded.rzs file to see the image files already loaded for this project.2.StitchingAuto-StitchingAutodesk Stitcher Unlimited 2009 has a fully automated stitching engine. Run the Stitch Shots function by either:•clicking the icon in the toolbar, or•press ENTER (make sure no images are selected),or•Stitch > Stitch ShotsThe Stitching Window is a 3D environment in which you can navigate around using the navigation controls (see “Navigating in Stitcher” in the Stitcher User Guide for more information on navigation). NOTE The display can appears smoother in the Stitching window if your graphic card allows real timelinear blending; the GPU options can be set from the preferences. The icon is a status indicator to specify that the GPU blending is activated.Manual-StitchingIn this example almost all the pictures are automatically stitched, but one is missing. For situations like this, you can use the Manual Stitch tool to complete the panorama.To manually stitch the missing image, you need to find a stitched image which overlaps it. You will use the overlapping features in the shots to match the images one to each other.1.Select the unstitched image2.Add to the selection by holding down the Shift key and clicking an already stitched imagewhich has features that overlap the unstitched image (in our example use the venitian08.jpg)3.Do one of the following:•Click Manual Stitch in the Toolbar, or•Select Stitch > Manual Stitch, or•Right-click and choose Manual Stitch from the contextual menuWhen stitching images manually, you need to find at least two common points in both images. The points should not be on the same line, but be distributed in the image for greater precision.The manual stitch workflow is:1.Click and place a marker on a particular detail in the first image2.Click to add the same marker inside the second image, then repeat step 1 and 2 to create 3points inside each image as shown in the screen shots.positioned correctly inside the panorama.NOTE Manually stitched images have a yellow highlight to distinguish them from auto-stitched images.Load the Stitcher project file, stitched.rzs, to see this step completed.3.AlignmentBefore rendering the panorama, you can change the viewpoint to determine what you see in the final panorama. Stitcher automatically aligns the panorama.You can align the panorama by clicking the Auto-Align icon or by selecting Tool > Automatically Align Panorama, or pressing “A”.NOTE the status indicator is displayed when the panorama is aligned.4.EqualizationWhen you click the Equalization icon, the Equalization tool launches in all of the images. This step is significant and makes it possible to correct certain differences in exposures, especially in the levels of blue in the sky.NOTE The Equalization process can be reverted at any time by selecting Render > Unequalize. The equalization factor can be defined in the rendering preferences.5.RenderRender area and rendering optionsIf you need to render only a part of the panorama, do the following steps:1.Select Render > Render Area > Set Render Area tool.2.Right-click in the panorama, and choose Spherical View from the contextual menuNOTE the contextual menu in the fully-stitched panorama lets you choose the projection you want to use for rendering.3.Now draw a rectangular area in order to define and adjust the borders of the final image.4.To quit the Render Area tool, press the Space key or Render > Render Area > Set RenderArea.5.Click Render Panorama to set the render parameters.6.The last step before launching the rendering process is to define the parameters for theimage.Stitcher gives you the option of the following rendering types:•Cubical or cylindrical QuickTime® VR (QuickTime Player installation is required)•Images with a spherical, a cylindrical, or a cubical projection•Snapshot image•3D formats such as VRML or Pure Player® projectionWhen rendering a panorama as an image, you can choose between the following file formats:•Jpg•TIF•PSD Photoshop®•…Each file format has specific options which can be adjusted in the related tab.Before rendering, you must define:•The path and filename where you want to save the image file•The size in pixels (for this tutorial you will set the width to 2048, the height value will update automatically).NOTE The size of the panorama will have an impact on the rendering time. The optimal size is calculated as a function of the original image size that you have loaded in Stitcher, in order to maximize quality.•The image format.Now verify that the Type is set to Spherical and that the Use Defined Viewport option ischecked. The Use Defined Viewport option is active when a render area is set.7.Click Render.Spherical panorama 360° with render area.Load the Stitcher project file, render_area.rzs, to see this step completed.You can also see the result with the file Venitian_area.jpg.Load a Spherical panorama and convert to QTVR®This part of the tutorial guides you through loading a full 360° panorama in Stitcher, using the authoring control tool to set the viewing parameters of your movie and then converting the panorama to a QTVR® file.1.Select File > Load PanoramaThe browser dialog opens.2.Select the image file you want to load and click Open.In our example load Venitian_Spherical.jpg.NOTE If you are working with a cubical panorama, select one of the 6 face files.e Tools > Authoring Controls to activate the interactive controller.The General Information expands to display the Authoring Controls options.e the navigation control inside the stitching window to author (in real time) the view youwant your QTVR file to have. Adjust the zoom out and lock it by clicking Max FOV. Do the same with the min FOV. You can real-time preview the zoom control constraint and adjust them at any time.NOTE The zoom out constraint is a good way to define the maximum zoom view and thereby stop viewers from setting a huge field of view (sometimes an inelegant display for your movie). The zoom in constraint prevents viewers from magnifying the view past the point where you feel the image quality of your movie is compromised (too pixilated, and also an inelegant display for your movie).Use the Tilt constraint and the Pan constraint the same way.In our example, the bottom part of the panorama is a black area, so we want to prevent viewers from tilting to that position. We have therefore locked the Min TILT value.5.To quit the Authoring Controls tool, close the edit dialog, then select Tools > AuthoringControls, or click the white cross at the top right corner of the General Information.NOTE Do not change the point of view after setting the Authoring Control otherwise your Authoring Control values will not make sense anymore without the initial points of reference.6.Open the Render Parameters dialog by clicking , or select Render > Render.7.Set the render parameters path, filename, size and QTVR options as you see them in thenext screen shot.unch the Convert process.Congratulations! You have successfully completed the full Stitcher workflow from stitching a panorama, and to creating a QTVR movie.View the result with the file Venitian.mov.。

Science &Technology Vision 科技视界0引言智能餐厅是科技创新,物联网技术发展的必然产物[1]。

智能机器人是智能餐厅服务系统的关键,直接关系到该系统能否实现完全智能化服务。

与国内外现有餐厅服务机器人相比较,该机器人不仅具有个体适中、成本低等特点,而且基于单片机控制,简单实用,送餐稳定,定位准确,语音与显示可以同步提示顾客取自己所需的餐饮,使得整个餐厅服务稳定而自动化运行,达到智能化餐饮服务的效果。

1结构设计智能机器人在测控系统控制下能实现自主送餐全过程,自厨房出发自动移动到对应餐桌定位后,餐盘旋转90°,提示顾客取餐,取走后,餐盘归位,回到厨房控制中心等待下次送餐。

整体结构大致包括载物盘、支撑板、控制电路板、无线收发模块四个部分。

其模型结构简图如图1所示。

图1模型结构简图2测控系统硬件设计智能机器人采用飞思卡尔公司的MC9S12单片机为核心控制芯片[2],由电磁传感器检测路径,再通过放大器放大微弱信号传输到单片机的AD 通道,经过数据处理实现机器人的方向控制,加以驱动就可以使机器人循迹行驶,同时配备检测、定位和语音等功能。

51单片机辅助实现餐桌号接收及显示功能。

机器人测控系统硬件包含:①信息采集模块;②驱动模块;③显示模块;④语音模块。

其结构框图如图2所示。

2.1信息采集模块智能机器人是通过前端两个电感检测地面铺设的频率为50Hz 的交流信号实现循迹的。

将检测到的电压信号经运算放大器放大,送入主控单片机的AD 通道进行处理。

利用红外探测器检测车体前方障碍,一旦发现障碍就会触发单片机中断。

通过干簧管检测餐桌旁的强磁铁个数[3],来区分不同的餐桌。

其电气原理图如图3所示。

图2测控系统硬件结构框图图3采集模块电气原理图2.2驱动模块驱动模块执行单片机的PWM 信号。

电机驱动采用BTS7970[4],接收MC9S12单片机P3、P7口输出的PWM 信号实现餐车移动。

Microsoft Dynamics® AXTracing Dynamics AX 2009 Role Center KPI’sSummary: This document explains how to trace the data displayed in a Role Center page KPI to its source in the Microsoft Dynamics AX online transaction processing (OLTP) database.Author:Catherine McDade, Support Escalation EngineerDate Published: January, 2010Table of ContentsIntroduction (3)Terminology (4)KPI walkthrough (9)Description of KPI example (9)Step-by-step (9)Appendix A: Useful links (31)IntroductionThe prominent place of key performance indicators (KPIs) in Microsoft Dynamics AX 2009 Role Center pages has prompted questions about where the KPI data is drawn from. This document explains how to trace the data displayed in a KPI to its source in the Microsoft Dynamics AX online transaction processing (OLTP) database.Microsoft Dynamics AX relies on SQL Server Analysis Services (SSAS) for its business intelligence processing. In the following sections, we define terms of importance for SSAS, and then provide an example of how to trace the data for a KPI on a Role Center page.TerminologyKey Performance Indicator (KPI)A Key Performance Indicator is a measurement for gauging business success, or, in other words, ameasure of business metrics against targets. For example, a Sales Amount KPI could show sales from the last quarter and display a green icon if you are at budget, yellow if you are within 5% of budget, and red if you are under 5% of budget.Online Analytical Processing (OLAP)OLAP systems (such as that supported by SSAS) aggregate and store data at various levels across various categories.FactsFacts are predominantly numeric measurements, such as price or quantity, and represent the key business metrics that you want to aggregate and analyze. Facts form the basis of calculations, and you often aggregate them for members of a dimension.DimensionsDimensions form the contexts for the facts, and define the aspects of a business by which the facts are aggregated. For example, Items could be a dimension, while Price and Quantity could be facts of that dimension.Data sourceA data source stores the connection information for an SSAS project and/or database. WithMicrosoft Dynamics AX, the project or OLAP database that you create has a data source that points to your Microsoft Dynamics AX OLTP database.Data source viewA data source view contains the logical model of the schema used by an SSAS database object.Data source views can filter, apply calculations, and create joins on objects in the data source. In the OLAP database that Microsoft Dynamics AX creates, most of the data source views are simply views of a specific table, though some views may include a SQL statement that contains filters, calculations, or joins.MeasuresA measure represents a column that contains quantifiable data, usually numeric, that can beaggregated. A measure is generally mapped to a column in a fact table. An example of a measure would be Sales Amount or Cost of Goods Sold (COGS).CubeCubes store summarized fact and dimension data in structures that are multidimensional (that is, containing more than the two dimensions found in spreadsheets and normal database tables).Dimensions define the structure of the cube, and measures provide the numeric values of interest to an end user.Microsoft Dynamics AX 2009 ships with the following 10 default cubes:∙Accounts Receivable∙Human Resources Management∙General Ledger∙Production∙Project Accounting∙Purchase∙Sales∙Customer Relationship Management∙Expense Management∙Accounts payableMultidimensional Expressions (MDX)MDX is a query language, analogous to Structured Query Language (SQL), that is used to retrieve multidimensional data from a cube.Business Intelligence Development Studio (BIDS)An integrated development environment (IDE) based on Microsoft Visual Studio 2005 or 2008 and used to create and modify business intelligence solutions. This is the tool that you use to view and/or modify your Dynamics AX OLAP project or database.ProjectIn BIDS, a project is a collection of objects that make up your OLAP database. BIDS stores the objects (cubes, dimensions, etc) as files in the file system. It is recommended that you create a project for your OLAP database so that when you are making changes you are not affecting the database until you deploy.Below is a screen shot of BIDS opened to a project, followed by two screen shots that label the various sections of the BIDS environment.Detail view of BIDS, left side:Detail view of BIDS, right side:KPI walkthroughDescription of KPI exampleThis section walks you through an example of how to determine the origin of KPI values. We will use screen shots as needed to illustrate procedures.Scenario: Your CEO views her Role Center and wants to know where the numbers for the Production Cost KPI are coming from.Step-by-step1.In the Microsoft Dynamics AX client, go to the User profiles form (Administration >Setup > User profiles). On the form find and select CEO in the Profile ID column andthen click the View Role Center button.2. For the Production KPIs click the Manage KPIs link.3. Click the edit button on Production cost (the pencil icon). It will tell you that this ispulling from the Production Cube and the Production Cost KPI.4. To look at the KPI open SQL Server Business Intelligence Development Studio (BIDS). Ifyou are running SQL Server 2005, BIDS can be found at Start > All Programs >Microsoft SQL Server 2005 > SQL Server Business Intelligence DevelopmentStudio. If you are running SQL Server 2008, BIDS can be found at Start > All Programs > Microsoft SQL Server 2008 > SQL Server Business Intelligence Development Studio.5. Open your OLAP database (File > Open > Analysis Services Database).6. On the Connect To Database form, select Connect to existing database. Enter thename of the SQL Server Analysis Services Server in the Server field. In the Database field, enter Dynamics AX.Note: By default your OLAP database is named Dynamics AX. If you have applied adifferent name, use that name for Dynamics AX in the step above.7. Open the Production cube in the Solution Explorer section of BIDS. Find ProductionCube, right-click, and select Open.8. Click the KPIs tab.9. In the KPI Organizer, click the Production Cost KPI to open its setup form.10. The Value Expression section tells you what data the KPI is displaying.For this KPI we see that it displays the following:[Measures].[Actual vs. Planned Consumption]“M easures” could be a calculated measure or a measure on the cube structure. It istypically a calculated measure, so click the Calculations tab for the Production cube. 11. On the Calculations tab, find the Script Organizer and click the Actual vs. PlannedConsumption calculation.12.You will see in the Expression section it is doing the following:IF(ISEMPTY([Measures].[Cost of Planned Consumption]) OR [Measures].[Cost of Planned Consumption] = 0,NULL,([Measures].[Cost of Actual Consumption] / [Measures].[Cost of PlannedConsumption]) * 100)13. If we break the above statement down we see that the first part is:[Measures].[Cost of Planned Consumption] OR [Measures].[Cost of Planned Consumption] = 0, NULLWhat this tells us is that if these values return zero we will report null, otherwise we will do the calculation on the next line. First we need to find out if the above statement would return a zero, as shown in the following steps.14.Begin with the first part of the MDX query, [Measures].[Cost of PlannedConsumption]. On the Calculations tab you should see that Cost of PlannedConsumption breaks down to the following:[Measures].[Planned Cost Amount] + [Measures].[Planned Cost Markup]15. The Planned Cost Amount is another calculated measure that does the following:([Measures].[Cost amount], [Production Level].[Level].&[1])16.Cost amount is not a calculated measure, so we go back to the Cube Structure tab andin the Measures pane find CostCalcuation > Cost amount.17. Right-click Cost amount and select Properties.18. Expand Source, then expand Source again. The TableID is PRODCALCTRANS and theColumnID is COSTAMOUNT.19. To verify where the data is pulled from, go to the Solution Explorer and right-clickDynamics AX under Data Source Views. Select Open.20. On the Dynamics AX data source view tab, find PRODCALCTRANS under Tables.21. Right-click PRODCALCTRANS and select Edit Named Query.22.If you didn’t have an Edit Named Query option, it would mean the data was being pulledthe PRODCALCTRANS table using the following select statement:select costamount from prodcalctransHowever, since this is a named query, we need to find where the COSTAMOUNT column is coming from. To do this, look through the column labeled Column, find COSTAMOUNT, and then look at the Table column to see the source table.23.We see that the COSTAMOUNT column is pulling data from the PRODCALCTRANS table.The SQL statement would be:select costamount from prodcalctrans24.Now we need to trace the second part of the calculated measure, which is [ProductionLevel].[Level].&[1]. Find Production level in the Hierarchies tab underDimensions. Expand Production level and then click Edit Production Level.25.The Production Level hierarchy should appear under Production Level. Right click Leveland select Properties.26.In the Properties window, expand NameColumn and then expand Source. The sourceTableID is PRODCALCTRANS_LEVEL and the ColumnID is COLLECTREFLEVEL.27.We now know the data source that OLAP is using, but we want to find out where data isbeing pulled from in the Microsoft Dynamics AX OLTP database. To do this we can open the Dynamics AX option under Data Source Views in Solution Explorer.28.Scroll to PRODCALCTRANS_LEVEL, right-click, and select Edit Named Query29.The SQL statement for this data source is:SELECT DISTINCT COLLECTREFLEVEL FROM PRODCALCTRANS30.We now have enough information to build a SQL statement that would reflect the OLAPquery we saw in step 24 ([Production Level].[Level].&[1]). Adding the level of “1”from the end of the statement yields the SQL statement:select * from prodcalctrans where collectreflevel=1bining SQL statements yields:select sum (costamount) from prodcalctrans where collectreflevel=132.Now return to the second part of the MDX query [Measures].[Planned Cost Markup]from step 14.On the Calculations tab, find Planned Cost Markup. We see that this calculated measure is defined by([Measures].[Cost Markup], [Production Level].[Level].&[1])33.On the Cube Structure tab, navigate to CostCalcuation and then to Cost Markup.34.Right-click and select Properties for Cost Markup.35.In the Properties window, expand Source and then Source again. The displayedTableID is PRODCALCTRANS and the ColumnID is COSTMARKUP.36.Return to the Data Source View and look at PRODCALCTRANS and the Cost Markupcolumn (as in steps 20 through 23). The SQL statement turns out to be:select costmarkup from prodcalctrans37.We already found the SQL for production level.level &1 in steps 24-30. Combiningthat with COSTMARKUP yields:select sum (costmarkup) from prodcalctrans where collectreflevel=138.Now you can take the sum of COSTAMOUNT and COSTMARKUP (using the results fromsteps 31 and 37) where collectionreflevel=1. If that value is zero, then the KPI is null.39.If the value is not zero, then we continue tracing the KPI using the second part of thestatement from step 12:[Measures].[Cost of Actual Consumption] / [Measures].[Cost of Planned Consumption]) * 10040.The Cost of Actual Consumption is a calculated measure that has the followingexpression:[Measures].[Realized Cost Amount] + [Measures].[Realized Cost Adjustment]41.In the first part of the statement, the Realized Cost Amount measure is:([Measures].[Actual cost amount], [Production Level].[Level].&[1])42.Now look up the Actual cost amount measure. To find this measure, go back to theCube Structure tab and find Actual cost amount under CostCalculation.43.If we look at the properties of Actual cost amount we find that the source TableID isPRODCALCTRANS and the ColumnID is REALCOSTAMOUNT.44.This gives us a SQL statement of:select realcostamount from prodcalctransIf we add the production level of 1 (which we already found in steps 24 to 30) the SQL statement for all of step 41 is:select sum (realcostamount) from prodcalctrans where collectreflevel=145.Go back to step 40 and look at the second part of the statement[Measures].[Realized Cost Adjustment]Realized Cost Adjustment is a calculated measure equivalent to:([Measures].[RealCostAdjustment], [Production Level].[Level].&[1])46.The source of RealCostAdjustment can be found by going back to the Cube Structuretab and finding RealCostAdjustment under CostCalculation.47.If we look at the properties of RealCostAdjustment, we find that the source TableID isPRODCALCTRANS and the ColumnID is REALCOSTADJUSTMENT. Adding the production level of 1, we would see a SQL statement such as:select sum(realcostadjustment) from prodcalctrans where collectreflevel=148.To derive Cost of Actual Consumption, we would add the results of steps 44 and 47.49.Next we trace the second part of the statement from step 39:[Measures].[Cost of Planned Consumption]This results in the expression:[Measures].[Planned Cost Amount] + [Measures].[Planned Cost Markup]50.[Measures].[Planned Cost Amount] is a calculated measure equivalent to:([Measures].[Cost amount], [Production Level].[Level].&[1])51.To find [Measures].[Cost amount], go back to the Cube Structure tab and find Costamount under CostCalculation.52.If we look at the properties of Cost amount, we find that the source TableID isPRODCALCTRANS and the ColumnID is COSTAMOUNT. Adding the production level of 1, we would see the SQL statement:select sum (costamount) from prodcalctrans where collectreflevel=153.Return to step 49 for the next part of the statement:[Measures].[Planned Cost Markup]This is a calculated measure equivalent to the expression:([Measures].[Cost Markup], [Production Level].[Level].&[1])54.To trace this measure, go back to the Cube Structure tab and find Cost Markup underCostCalculation.31 TRACING DYNAMICS AX 2009 ROLE CENTER KPI ’S55. If we look at the properties of Cost Markup , we find that the source TableID isPRODCALCTRANS and the ColumnID is COSTMARKUP. Adding the production level of 1,we see the SQL statement:select sum (costmarkup) from prodcalctrans where collectreflevel=156. The results for [Measures].[Cost of Planned Consumption] would be the sum of steps 52and 55.57. Therefore, the results for [Measures].[Cost of Actual Consumption] / [Measures].[Cost ofPlanned Consumption]) * 100 This means we have to divide the results from step 48 bythe results of step 56. Then we take that number and multiply it by 100 to get our actualvs. planned consumption which is what gives us the Production Cost KPI.After following these steps you should now know where the data that makes up the Production Cost figure on the KPI is coming from. You should also have learned how to trace a KPI so that you can determine where any KPI is pulling data from.Appendix A: Useful links∙ Microsoft Dynamics AX 2009 Business Intelligence Cube Reference Guide: /downloads/details.aspx?FamilyId=6A685DF3-912D-4545-B990-CD2283C159FB&displaylang=en∙ Role Center reference for Microsoft Dynamics AX: https:///dynamics/ax/using/ax_rolecenterreference.mspxReferences:∙The information above was taken from SQL Server books online. For more information on SSAS please go to /en-us/library/ms130214.aspx32 TRACING DYNAMICS AX 2009 ROLE CENTER KPI ’SThis document is provided “a s-is.” Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it.Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred.This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes. You may modify this document for your internal, reference purposes. © 2009 Microsoft Corporation. All rights reserved.Microsoft Dynamics is a line of integrated, adaptable business management solutions that enables you and your people to make business decisions with greater confidence. Microsoft Dynamics works like and with familiar Microsoft software, automating and streamlining financial, customer relationship and supply chain processes in a way that helps you drive business success.U.S. and Canada Toll Free 1-888-477-7989Worldwide +1-701-281-6500/dynamics。