.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Combine measures that are dependant on different dimensions

Posted By:      Posted Date: October 11, 2010    Points: 0   Category :Sql Server

I have a cube that tracks requests, including open and closing times (along with other info) and am building a dashboard for all metrics.

I need to include a measure for the # of requests Received, Completed and WIP.  I have based WIP calculated measure around Completed dimension and can use the same dimension for # Completed, but am unsure how i can represent the # Received in the same dataset.

I have tried using linkmember to convert the current member of Completed into Received dimension but this will only return items that are created and received in the same period.

I am experimenting with SCOPE, but think it is a dead end.

Does anyone know if what i am doing is possible with a separate dimension for received and completed?  Is this only possible to do if I have another date (ReportDate) that would allow me to tie it together?

Many thanks if you can show me the way....!


View Complete Post

More Related Resource Links

Combine, minify and compress JavaScript files to load ASP.NET pages faster

Websites are getting more interactive these days and most of this interactivity comes from JavaScript. People are using different JavaScript libraries and frameworks to make their websites more interactive and user friendly.

What Gives You the Right?: Combine the Powers of AzMan and WSE 3.0 to Protect Your Web Services


In this article, Niels Flensted-Jensen demonstrates how you can combine new and existing Microsoft technologies with minimal new code to provide flexible authorization for individual Web service methods. Windows 2003 Authorization Manager, Web Service Enhancements 3.0, and Enterprise Library all play a part.

Niels Flensted-Jensen

MSDN Magazine November 2005

Web Services: Increase Your App's Reach Using WSDL to Combine Multiple Web Services


The very tools that have helped drive the growing adoption of Web services, and the enabling abstractions that they provide, can often prevent developers from peeking behind the curtains at the XML standards that make up the Web services stack. This article will offer a solution that enables type sharing between proxies created for complementary Web services, while at the same time providing an opportunity to examine the Web Services Description Language (WSDL) and its interaction with the Web services tools you know and love.

Gerrard Lindsay

MSDN Magazine March 2005

ASP.NET: Combine Web and Windows Services to Run Your ASP.NET Code at Scheduled Intervals


If you want to schedule ASP.NET tasks, one solution is to use a Web service to provide an interface to your ASP.NET application and build a Windows service that calls to it at scheduled intervals. Thus the ASP.NET application doesn't have to own the scheduling logic. Here the author shows how to schedule your ASP.NET tasks using a Windows service to initiate the Web service call because Windows services can start themselves when Windows boots up.

Andrew Needleman

MSDN Magazine March 2005

how ssas generate sql queries when processing dimensions and partitions?

hi all, how does ssas2008 generate sql queries to read data from the source, where processing dimensions and partitions? is there any reading meterial that clearly explains how the sql queries are constructed according to properties of dsv, dimensions and measures and partitions.   thanks in advance.Andrew Chen Interested in BI related technologies

Calculated measures, calculated cells, and custom rollups issue

Hi,  I'm a little bit confused according to the purpose of Calculated measures, calculated cells, and custom rollups as they lead practically to the same goal, is there any differnce between those modes from a technical point of view. And for the last one, the custom roll up the question how to perform this, I need a walkthrough that lead me to undersand the way to set a custom roll up to a given dimesion, I visited microsoft site but the inforamtion that I have is poor, so is there any other source that exposes that as a step by step example? Thank youThe complexity resides in the simplicity

Server Time Dimensions - language mix

I'm making my first steps with SSAS and as part of my test project, I added a server based time dimension. During generation, I configured the langage to be German (Germany) (the machine and Visual Studio are set up in English). What is being generated is a mix between English and German though - the calendar year and quarters remain in English, and the months and day of week are in German. Shouldn't the whole hierarchy be in the language I chose? I'm using SQL Server 10.0.2531.

T-SQL linear regression in multiple dimensions

Hi! I'm trying to make linear regression T-SQL in multiple dimensions  (y = a + a1*x1+ ... a2*xn) like LINEST function in Excel.I need create a similar stored procedure in SQL Server.I also created the code using the article http://charlottecredittechnology.blogspot.com/2008/11/sql-2008-excel-like-linest-function.html but i have troubles in re-adapt to a model with multiple dimensions...thanks!

Combine two tables

I have two tables: tInv containing invoices. The fields I am interested in: id cusid ' customer id source ' invoice# dtjournal ' invoice date And tSal containing orders (not billed/shipped yet) with the fields: id cusid ' customerid salnr ' sales order number dtsalordr ' sales order date I need to query these two tables and return a single table ordered by date, containing: id,cusid,number,date,type ('in' or 'so') In other words I need to select id and cusid from both tables and dtjournal and dtsalordr from either table and combine into one column 'date' and get source & salnr and combine into 'number' I want a list, ordered by date, of all invoices & orders. Is this a possibility with a SELECT statement or do I need to fetch both tables, and then manually move the rows into a new table?   Thanks, Erwin

The Measures Hierarchy is used more than once in a CrossJoin Function

Hi Folks, I am encountering a rather frustrating error in my SSRS report. It deals with MDX so I am posting this question in the SSAS forum. I have to create a report which should look something like this.   YTD1  YTD2 Product Measure 1 Measure 2 Measure 3 Measure 1 Measure 2 Measure 3 A B C D             I am writing an MDX using cross join to fetch this data, while the MDX works fine in SQL Server Management Studio, as soon as I run in on the MDX Editor in SSRS, I get the error, "The Query must at least have one axis". The MDX that I am using is WITH SET   [Top 10 Manufacturers] AS TOPCOUNT   ( { [Product].[Manufacturer].[Manufacturer]. allMembers}, 10, ( [Measures].[SALES VALUES(000)], [PERIOD].[MAT].&[MAT01]) )   SELECT NON EMPTY   CROSSJOIN( {[PERIOD].[Months in Year].&[1],[PERIOD].[Months in Year].&[2]}, {[Measures].[AVG SALES PRICE], [Measures].[AVG SALES PRICE CU], [Measures].[SALES VALUES] } ) ON COLUMNS, [Top 10 Manufacturers] on rows   FROM [MYCUBE] P.S. I need both the measures as well as the Hierarchy (MAT) on columns. Any help will be greatly appreciated. Thanks, Suman Sarkar  

Created Calculated Measures Permenantly with MDX

I did some research on how to create a calculated measure in MDX, i found that by using MDX to create a Calculated Measure, the measure will not be persistent in the cube but only query-scoped or session-scoped. is it possible to create a persistent calculated measure using MDX? if not, where is a good point for me to start looking into xmla to create the calculated measure?

Combine two queries in one resultset

Hi,I want to combine two queries like :select name as name1 from table1 where id = 6 and select name as name2 from table1 where id = 2.I need a single resultset. How can I do that?Regards,ap.

How do I nest dimensions in scorecards in Dashboard Designer?

I have a scorecard where I have a KPI on columns and a dimension on rows. That works fine. I can also add another dimension on rows and add it as a parent for example. The only thing I miss is a drilldown "+". What I see is basically a scorecard showing all possible rows, while I want one where I can drill down (or through) and expand rows if I want. How do I get the parent to be expandable? Thanks

How do I combine multiple VB arraylists into one?

 Hi Everyone- I have three arraylists (datasets and subsets) that I need to combine into one arrraylist so I can databind it to a repeater control and then present data to the user.  As far as I know, you can only bind the repeater with one datasource (please correct me if this is wrong) that's why I'm combining these arraylists. Each arraylist has an ID from the database which I can use as a datakey to keep the data aligned.  How do I combine these lists? Thanks for your help!

daily complete cube rebuild four dimensions and fact table including remapping of all surrogate keys

Hi SSIS Engineers: Forgive me if this is a multi-forum question. Our primary activity in the next week is to automate the processing in SSIS, where I led the team to create complete processing flows for Full and Add in the order of Dimension, Measure Group, Partition, Cube, Database. These work. The problem occurs in a complete refresh of the ERP database that caused me manual effort inside SSAS, which I plan to find a way to automate in SSIS. I performed a complete refresh of our cube from the ERP source from a time perspective. We are automating this process in SSIS. In SSAS, I had to manually delete the four dimensions from the UDM view via the Solution Explorer. Since the complete refresh increased the surrogate keys in the dimensions and since the names were the same, I couldn't just drop the partition and reprocess the dimensions, since, in effect, new fact rows would have to be mapped to the new keys. SSAS held on to the old keys even with Full Processing of the Dimensions first, then the Cube. Until I dropped--deleted-- the dimensional tables from the Solution Explorer and the UDM then later readded the dimensions with the new surrogate keys (both add, update and delete dimensional attribute changes in full refresh) via the Add Dimension wizard, the cube kept the old surrogate keys and failed in measure group, fact, database and partition processing.

Need to Automate in SSIS Cube Drop and Recreate of Dimensions: Had to do on the Solution Explorer Ma

Hi SSAS Engineers: This question is in the context of a complete refresh of the SSAS cube. I'm OK on the SSIS approach thanks to TMcD and past successes (also thanks to him). The question is why did I have to manually drop/delete the dimensions in BIDS SSAS, then readd them with the cube wizard to pull in the dimensions with the same names, but new surrogate key relationships and more surrogate keys in total? We need to Automate in SSIS Cube Drop and Recreate of Dimensions. I originally had to perform this function on the Solution Explorer Manually in SSAS UDM, since the names are the same, but the structures and the data are changing for the dimensions on the refresh (and the fact table is subsequently increasing). Herein lies the problem. We had a keynotfound error in the cube processing, since the old keys were being kept. I had run Full processing on the dimensions, but since the dimensions have the same name (no drop old names and add new names), the old surrogate keys remained. The fact table had the old row count as well, since the surrogate key pipeline to look up the surrogate keys for the new dimensions only had the old dimensions, even though I truncated the fact table. Yes--I had already rebuilt the dimensions in SQL Server and the row counts were the new refresh row counts. The dimension counts were right according to refresh numbers after

Parent child hierarchy & MDX to fetch particular level & all members below it along with measures us

I have a Sales Territory dimension that has employee and parent employee attribute on which parent child hierarchy is defined and it gives below hierarchy while browsing - - Mark Rolls --- Lumin Jacs ----- Larry Gomes ------- Messica Owens ------- Tom Ted ----------- Jackson Lopez ----- Matthew Ron --- Fred jacob - Jason Ron --- Jecy Pedro   But beside this parent-child hierarchy I have other attributes like employee address, email and telephone. My facts related sales transaction is tagged to lowest level. For example here facts are available only for Jackson Lopez (being Field Executive). When use below query I get complete sales reporting hierarchy result with some measures like sales amount, sales volume. But while accesing other attributes like address or email, it's repeating address/email/telephone of jackson Lopes everywhere to whom fact record is linked, Actually I want address/email/telephone of each sales employee from that dimension within hierarchy. How do I get it? The query I used is: Here Parent Terr ID is parent child hierachy. SELECT ( Descendants( { [Dim SalesRegion].[Parent Terr ID].[Employee Level 01].&[538018] /* here Mark Rolls is 538018 */ }, 0,AFTER), NONEMPTY([Dim SalesRegion].[Emp Address].[Emp Address].Members), NONEMPTY([Dim SalesRegion].[Emp Email].[Emp Email].Members) ) ON ROWS , { ([Dim Date].[The Year].[The Year].[CY-2010], [Dim
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend