.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

unable to design the cube and query

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server
I am v new to MDX and its driving me crazy. I designed a fact table with count, region id. and there is a dimension table for region which has continent, countryname,countryCode,region_id as fields..
In the cube, I hav Location as one of the dimension and Continent,Country(map to Country Code column in table) are the levels under the default hierarchy.I want to know the count for all the countries I have. so i used

WITH MEMBER Measures.[Towers] AS '[Measures].[TowerCount]' select NON EMPTY ({Measures.[Unique Towers]}) ON COLUMNS, NON EMPTY ({ [Location].[Country].MEMBERS}) ON ROWS FROM [Cube] WHERE [Time].[2010].[4].[OCT]

It worked with results like
Axis #0:
Axis #1:
Axis #2:
Row #0: 111
Row #1: 222
Row #2: 22333
Row #3: 34354
Row #4: 1212
Row #5: 11
Row #6: 41
Row #7: 11

The o/p has the country code and the count, but I also want to have the country name in the o/p...I dont know how to change the cube and query...Could some1 help me here?

Thank you

View Complete Post

More Related Resource Links

Query Performance & Overall Design - SSAS MDX WCF

Hi All, We have a Cube which is to be queried by the Online system , using WCF service. Peroformance of the queries running on the Cube is not really very good , as we have to calculate various percentile (which are all calculated members) on the cube, this howver works well within limits of 5-6 secs for small sample size. But this goes beyond the threshold of 30 secs when the record counts increases. What we have is our SSAS Cubes , and we have WCF Service querying the Cube using ADOMD.NET. This may not be one of the best way to achieve this requirement , but we are kind of completed with development and it may not be feasible to work on another approach, what we are looking for is optimize this design and make it work with in expected time limits of 5-8 secs. Kindly let me know, if i am not clear or if you need any more info to suggest something ! Thanks a lot for your help !   Kindly

SQL server 2008 - Unable to tune a query with DTA

We are on:Verison: SQL 2008 [10.0.2531] on windows server 2008. Both are enterprise editions.When I highlight the query and right-click, then select Analyze Query in Database Engine Tuning Advisor, then click "Start Analysis",  I get the following error in the "Message" column of "Progress" tab of DTA: Cannot initialize tuning. [.Net SqlClient Data Provider] If I click on this, it displays this message: "Cannot find either column 'msdb' or the user-defined function or aggregate 'msdb.dbo.fn_DTA_unquote_dbname', or the name is ambiguous. Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count=0, current count=1. (Microsoft SQL Server Error: 4121)Please let me know if you guys have any ideas on this error. Thanks,Sam. 

Unable to open design view

I have SSRS 2008(native), BIDS and VS  2008 (9.0.30729.1 SP) on Windows 7 Pro.I have a working report and today I cannot "View Designer" for my report.When I load the project, it automatically goes into rdl:code view.When I right click on the report, I see the option to view code or view designer,and select view designer, it produces a second copy of the rdl:code view.The rdl is good because: - I can build the project and the build succeeds. - I can run the rdl (right click, run) and it produces the preview of the report.Then, I started a new simple project, went through the wizard and still - no design view.This simple project also builds correctly and produces a preview report.I have checked this, and other forums but I find myself (sort of) alone in this quandary!So, how (and why) did I lose the designer view?Please help....Usman Suleman

Cube design problems

Hi. I am working on an assignment which is aimed at building a cube which will be used by 14\15 different department of the organization.As per meeting with the users, and their needs, we decided to have 15 dimension and one measure group for the prototype. Now , the users from those departments are different and the way they see this cube is also very diff. The finance wants to slice and dice data and seems to have understanding about BI.but the some departements want to see detailed data after a certain level.. they are providing huge challenge to us. they want the cube to deliver them detailed information about those high cardinal members with very less processing time. We thought of designing actions , but  the user can following multiple navigation to reach the detail data.We thought of putting that in a dimension table but it killed the cube..like the users always faced out of memory issues ..to me it seems, we do not have proper knowledge on design..I would request your help to understand this real time challenge, which i am sure ,some one must have come accross.  

Pipeline Data Cube Design Question



The Analysis team wants to analyze sales pipeline data. Our CRM system(Source system) send the Data Warehouse team transaction as the Product moves through the different stages.


To mention a few: Creation, CallMade,LeadEngaged,CreditCheck,PapersReceived,Closed.There are more than 100 tracking statuses like mentioned above.


Sample transactions fact table:


New DSV named query or copy and reuse an existing cube dimensions (is the latter valid) - SSAS 2008


I have a [DateAsAt] table in the DSV. This is linked to 3 fact tables to analyse and slice them from an as at date perspective (lets call them FactA, FactB and FactC). But I want to reuse this dimensions (almost like an alias) and although I have tried this and it seems to work, I just want to follow some due dilligence to make sure what I am doing is valid.

I have not created a new Named Calculation for a new date dimension that will be used in a Transactional way (not an as at way), and in stead simply copied the [DateAsAt] dimension, and pasted it back into the dimensions section of the cube (the default name it was given was [DateAsAt 1], but I changed it to [DateTransaction].

Although the relationship between the [DateTransaction] dimenion's source in the DSV (I.e. [DateAsAt]) is linked to FactA, FactB and FactC, I set a new dimension relationship between [DateTransaction] and FactTransactionD in the Dimension Usage area of the cube.

Is this valid? or MUST I create a seperate source for [DateTransaction] in the DSV and set its relationships there?

how to design this cube? HELP Please!!


Hi I am new to SSAS. I was able to build a cube if the data view includes only the clear-cut type of fact table and dimensions.
Now I am confused as what to do when thing get a little bit "complicated". Here is the made-up simplified version of the scenario.
A delivery company that deliver goods to several states. Some work orders require delivery to multiple states.

There are 3 tables:

Table 1 (a measure) - WorkOrder w/ fields: workOrderId (pk), Amount.

              Example:            WorkOrderId               Amount
                                         1                   5,000
                                         2   &nbs

Measures for Additive vs Non-Additive Data. What is cube design best practice?


I'm looking more for SSAS 2008 best practice design advice, rather than for an answer to a specific question (although I have a specifc set of examples).

First issue:  Creating a non-additive measure group and an additive measure group.  We have some fact data in our current cube that is additive, and some fact data that is non-additive; all stored in the same fact table.  We do not currently have measures implemented that reflect this aggregation distinction.  Question:  Is it considered good practice to segregate additive and non-additive fact data into a) different fact tables and/or b) different measure groups?  My thought is that it would be an acceptable design approach, but am looking for feedback.

Second issue:  Non-additive fact data is only available at a non-leaf grain.  The example here is that we have non-additive fact data which is only available for the 4th or 5th levels of our 6-level geography dimension.  Our solution has been to create a custom geography branch, which now essentially serves as our 'aggregation treatment' for non-additive fact data.  I don't believe it's a good practice to have the geography dimension serve this function because we end up having to create a custom geo member for each non-additive fact data element.  Question:  What is considered best practice

How to query SSAS cube based on a time span?



sorry, I'm new to SSAS. something i couldn't figure out, plase help me.

I have a fact table: Log   and a dimision table: Time

in the Time table, all the record are in date format detailed to hour. such as 2010-06-01 23:00:00

and in the log table, each log record has a time column.

Now I want to query based on two time parameters other program passed me.

e.g. , I want to know how many log records between 2010-01-01 02:00:00 and 2010-10-27 12:00:00.

but when i do the query, the problem is on the time spot '2010-01-01 02:00:00', maybe no log record avaliable.

so the query return nothing! The following is my query.

select NON EMPTY{[Log].[Subject].children} on 1,
NON EMPTY{[Measures].[Quantity]} on 0
 ( SELECT ( STRTOMEMBER('[Time].[Date].&[2010-05-01T23:00:00]') : STRTOMEMBER('[Time].[Date].&[2010-05-24T23:00:00]') ) on 0
   from [Db TMIC])

if Time.Date has no member of 2010-05-05 23:00:00, I got nothing!


I also tried use filter to get the nearest value, but still don't work.

select {[Measures].[Quantity]} on 0,

Filter([Time].[Date], [Time].[Date].CurrentMember < '2010-05-05T23:00:00') on 1
from [Db TMIC]


Can any one help how to solve the problem? To query based on two time values.

Cube Design in BIDS - Best Practise

Hi all,

Not a technical query more a 'how do you do it' type thing.

I have 3 AS databases on my test server that I use for the following:

Cube_Scratch        - This is my gash, can throw it away any time test bed
Cube_Development    - Proper development instance
Cube_Staging        - Deployed to users for testing

In BIDS I have one solution that I use for all my development work.

So if I am going to make changes to a cube/dimension I import a backup of say Development and
unload the project in BIDS.

Then I do another import of Development and rename that to just the cube I am working on.

In that project I remove all cubes, dsv's and dimensions except the one I am working on.

If it all goes pear shaped I can just redeploy the backup.

Does this sound like a good idea or am I missing an easier way of doing this.

Many thanks.

Strange results in SSRS query to Cube


I have discoverred a strange issue when running a MDX query from SSRS 2005. The (All) member of a dimension is comming back as "(null)" instead of "All". The identical query run from SSMS produces the correct results. Some thing to mention is that we are in a phase for the next few days where there are no fact records in my cube (essentially no data, but dimensions are populated). Below is my Query. The problem is with the Subscriber dimension. FYI - I am using SSAS 2005 with SSRS 2005
 SELECT { [Measures].[Total Volume In Bytes]} ON COLUMNS, 
  ({[Subscriber].[Role].Members } * [Traffic Class].[On Off].[On Off].Members) ON ROWS
from [Cube Name] 
Below is the result (correct) from SSMS
    Total Volume In Bytes

Process a SSAS cube's dimension by a T-SQL query



I need to process a SSAS 2008 cube's dimension by a T-SQL query, using a SQL agent job.

How can this be done.

Users use Excel-Pivot table to query the cube, how to capture those queries?


Most of our users use Excel'10 for querying the cube,

I've set up the querylog-properties in SSAS engine but it only captures the query from SSAS engine and not from the other platform such as Excel. So, when I use Usage based aggregation wizard, it shows the users/ueries from SSAS engine only. Is there any way to capture the user/query information that comes through Excel pivot table and somehow create aggregation through that information?


Any suggestion/help is very much appreciated,


Process SSAS cube's dimension by TSQL Query



I need to process a SSAS 2008 cube's dimension by a T-SQL query, using a SQL agent job.

How can this be done.



Cube design guidelines



My dataset have 1 Fact table with around 1000 columns and 1000 dimensions, for each dimension, i need non null count, percentage,  and mean.

What will be the most effecitve design? 1 cube or many cubes or 1 measure group or many measure groups, will partitions help ?

Currently i created 1 cube, 1 measuregroup when output all, the quesry runs for ever.

Can I put count in 1 mearure group, percentage in 2nd and mean in 3rd measure group ?





unable to see design of custom list form



I create CustomList form and save it on associated list folder, but I can’t  see design view of CustomList form on sharepoint designer, it show me Master Page error:

“The page contains markup that is not valid when attached to a Master Page.

Correct the problem in Code view.”

Why this is happen in some of sharepoint site(not all of them)?! How I can solve it?


excel pivot table linked to cube, unable to expand/collaps single field


I’m re-posting question. I’m using excel pivot table to browse a cube, for example the adventuresworks example. I drag dimension ‘Product Categories’ and ‘Sales Territory’ to Row Labels area, a date dimension to column, and any measure to Values area. Now I only want to expand ‘Europe’ under ‘Bikes’, but the excel  will expand ‘Europe’ under every ‘Product Categories’, is there any way to avoid that? In visual studio it works fine.

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