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

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

how to design this cube? HELP Please!!

Posted By:      Posted Date: September 24, 2010    Points: 0   Category :Sql Server

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

View Complete Post

More Related Resource Links

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:


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

unable to design the cube and query

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

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.

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 ?





Abstract Factory Design in C# , Vb.NET

Provide an interface for creating families of related or dependent objects without specifying their concrete classes

Prototype Design Pattern in C#. Vb.NET

Specify the kind of objects to create using a prototypical instance, and create new objects by copying this prototype

Adapter Design Pattern in C#, VB.NET

Convert the interface of a class into another interface clients expect. Adapter lets classes work together that couldn't otherwise because of incompatible interfaces.

Singleton Design Pattern in C#, Vb.NET

Ensure a class has only one instance and provide a global point of access to it.

Solidify Your C# Application Architecture with Design Patterns

design pattern can solve many problems by providing a framework for building an application. Design patterns, which make the design process cleaner and more efficient, are especially well-suited for use in C# development because it is an object-oriented language. Existing design patterns make good templates for your objects, allowing you to build software faster. This article describes several popular design patterns you can use in your own applications, including the singleton, the decorator, the composite, and the state classes, which can improve the extensibility of your applications and the reuse of your objects.

What Are Design Patterns and Do I Need Them?

Software professionals may be familiar with the term "Design Patterns," but many have no idea of where they come from and what they truly are. Consequently, some do not see the value and benefits design patterns bring to the software development process, especially in the areas of maintenance and code reuse.

Design Patterns for .NET

It is not the intent of the Design Pattern Series to focus on providing a theoretical knowledge dump of all there is to know about design patterns. There are many books that do that already. Instead, this series will focus on providing lots of practical examples. However, there will be some theory to help address important points concerning design patterns. I use the theory of design patterns mostly as a guide and instead make references to good design pattern books for more detail explanation.

Singleton Design Pattern in Asp.net using C#

When we want to make a only one instance of a class and also making sure that there is a global access point to that object then the design pattern we user is called Singleton. The pattern ensures that the class is instantiated only once and that all requests are directed to that one and only object.

The Factory Design Pattern

Software architects generally think in terms of high-level abstractions rather than low-level programming details. Representing a system in terms of high-level abstractions promotes understanding of the system and reduces its perceived complexity. One such set of abstractions is software design patterns. They have been successfully applied in the past to simplify and solve recurring problems in software design.

Abstract Factory Design Pattern (Sample in C# and VB .NET)

An abstract factory provides an interface for creating families of related objects without specifying their concrete classes. Sometimes one wants to construct an instance of one of a suite of classes, deciding between the classes at the time of instantiation. In order to avoid duplicating the decision making everywhere an instance is created, we need a mechanism for creating instances of related classes without necessarily knowing which will be instantiated.

GOF Creational Design Patterns with C#

The GOF design patterns help address the following challenges :

design ready to accommodate change & growth

design flexible systems which come ready to handle reconfiguration and run time tailoring

code in manner to facilitate reuse during the development and extension phases ... ie. both external and internal reuse, so that we are rewarded by efficiencies as the project progresses, coming from investments made earlier in the project.

implement change in a way that doesn't overly shorten the system's useful lifespan
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