Current Time

Hi. I have a critical design issue. Not a regular time dimension case.

i work for a set of schools where each school academic has a different academic calendar. say some schools start in august, others in september.. i generated the date keys per school to populate the time dimension. so my time dimension looks like this:

DATE_KEY,

DATE_SCHOOL,

DATE_DATE,

DATE_YEAR,

DATE_MONTH,

DATE_WEEK,

DATE_DAY_NUMBER,

DATE_FISCAL_ACAD_YEAR,

DATE_FISCAL_TERM, DATE_FISCAL_WEEK,

DATE_FISCAL_WEEKDAY_NUMBER,

DATE_FISCAL_ACAD_YEAR_DAY_NUMBER,

DATE_HOLIDAY, (y/n)

DATE_WEEKEND, (y/n)

DATE_DAY_NAME,

DATE_FISCAL_TERM_FIRST_DAY,

DATE_FISCAL_TERM_LAST_DAY,

DATE_FISCAL_WEEK_FIRST_DAY,

DATE_FISCAL_WEEK_LAST_DAY

I need help in finding a design to get the current term/current week/current day so that a school manager would get his current's school week's data or term data when he logs in to the system.

Thanks

Could it be that there are only a few sets of dates applicable? In which case you could create Calendars listing the relevant dates and link this back to the school.|||

I added fields to the schools dimension stating the current acad year, term,week,and day per school. and these fields are to be updated at each ETL process. I will use them in the queries.

thanks

Cube processing

I'm having a problem with a cube processing. When i process it with no aggregation design its ok, now i'm trying to process it with 5% of aggragation and it just took so long and after 12 hours it has not completed and analyses manager doesn't respond. Any suggestions?

Your aggregations are probably big, so they probably do not fit in memory and Anaysis Server starts swapping data to disc in order to complete aggregations building.

You should partition your data (Ent. Edition feature), in this case aggregations should be smaller, they would fit in memory and you should see faster processing times.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

cube design/MDX for Product Owns vs does not Own

I would really appreciate if you could give us your input for following design, I am having hard time to visualize the cube design.

I have a fact table and dimension tables as follows with rows.

CREATE TABLE [dbo].[Fact_ProductOwnership](
[OrgID] [int] NULL,
[SegmentID] [int] NULL,
[SubSegmentID] [int] NULL,
[ProductID] [int] NOT NULL
) ON [PRIMARY]

OrgID SegmentID SubSegmentID ProductID 1 1 1 2 1 1 1 3 1 1 1 6 2 2 2 2 2 2 2 3 2 2 2 6 3 1 1 1 3 1 1 3 3 1 1 5

Segment table rows

CREATE TABLE [dbo].[Segment](
[ID] [int] NULL,
[SegmentName] [varchar](20) NULL
) ON [PRIMARY]

ID SegmentName

1 Enterprize

2 Small Business

SubgSegment table and Rows

CREATE TABLE [dbo].[SubSegment](
[ID] [int] NULL,
[SubSegmentName] [varchar](20) NULL
) ON [PRIMARY]

ID SubSegmentName

1 Enterprize

2 Small Business

Product table and Rows

CREATE TABLE [dbo].[Products](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductDesc] [varchar](20) NULL
) ON [PRIMARY]

ID ProductDesc

1 Access_Yes

2 Access_No

3 Excel_Yes

4 Excel_No

5 SqlServer_Yes

6 SqlServer_No

What I would like achieve from above design is to get the list orgs for the following scenarios

1) Show the companies who owns Access_Yes & Excel_No

Above should display 2 orgs (1 and 2 qualify for this scenario)

2) Show the companies who owns SqlServer_yes & SqlServer_Ye

Above query should display 0 orgs(no org qualifies)

Could you please suggest me thtat is my above design wrong or is it too hard to ahieve from cube? Is there any alogorithm this can solve above problem ?

I would really appreciate if you could answer to above problem ?

You can design your cube with dimensions for Org and Product (and optionally Segment and SubSegment) and then create a row count measure. Then you would query for Orgs and use NONEMPTYCROSSJOIN to filter by products.

Something like:

SELECT NONEMPTYCROSSJOIN(NONEMPTYCROSSJOIN([Org].Members, {[Product].[Access_Yes]}, 1) {[Product].[Excel_No], 1) ON COLUMNS,

{} ON ROWS

FROM [MyCube]

cube design question (beginner)

Hi,

Let's say that i have a fact table with sales and one time dimension and i want to display the count of sales over the bands of sales; something like this:

0-750 750-1000 1000-1250

2004 1 12 5

2005 0 9 11

2006 2 13 40

Any sugestion ?

i suggest you create a dimension of this fact table, and create an attribute with the sales measure and use the DiscretizationBucketCount and DiscretizationMethod to configure the bands.

hope this helps

Cube design question

I have a Fact table which stores details about each revision made on the document.

There is a one to many relationship between a document and the revisions

I need to display information based on the last revision made by a particular UserType.

How do I select records from the fact table based on a particular UserType and the maximum revision number for that usertype?

Hi Reena,

Are you using AS 2000 or AS 2005?

So you have RevisionFact table and also Dim_Dt, Dim_UserType, Dim_Users, Dim_Doc etc. and you store every revisions in fact table along with UserTypeID, Date_ID, Doc_ID, and User_ID.

It would be easy if you tell what measures you store in fact table. You can use "Count" Aggregate Function in measure and query using UserType , Date and measure count on the field you store for each revisions in fact table.

-Ashok

|||

I am using AS 2005

I need to display the count of documents that were written within the target time as well as the count of documents that were not written within the target time set for each user.

The user list has to be displayed along the rows and the count of documents along the columns ( 2 columns)

I have measures that would display time taken to write a document and target time for the document.

Using COUNT function I can get the count of documents written by a user.

But how do I break that count up into two separate columns displaying number of documents written within target time and number of documents written outside target time - for each user?

|||

There are a few ways you could do this, but here's one suggestion. Its a very simple pattern which csn be used again and again for similar problems.

You have a table or view with the time taken and target time in as columns.

Add another column (this is easier if you have a view) and define it as:

case when TimeTaken>TargetTime then 0 else 1 end as TargetMetId

create a lookup table as TargetMet with values

0,"Target Not Met"

1,"Target Met"

You can then link the 2 together and create a nice TargetMet Dimension. This will give you all of the counts you require.

You can expand this by changing the case statment, and building a lookup table like this

1,"Finished Same Day"

2,"Finished Early"

3,"On Time"

4,"A bit late"

5,"Do they still work here?"

etc

|||Thanks for the reply.

cube design issue

Hi.

I have two modules that have several dimensions in common but at the same time each has its own dimensions. is it best to design 2 cubes or one combining both?

thanks in advance

Christina

It really depends ;)

In many cases one will find useful to create a single cube with 2 measure groups.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

Cube design e best client tool to access olap database

Hi all,

My doubt is about the best client tool to use for access cube olap. Currently i use excel for some tests but when expand until third ou fourth level the query delays a time not acceptable many times hanging the virtual server.

Another doubt is about cube design the bahavor described above is normal?

Is my server i have a fact table with 211.000 rows, 17 columns (with two calculations columns) and 15 dimension tables ( I think tha is a small database). This tables are organized in a starschema inside a SQL Server 2005 database. The cube 15 dimensions (one with two hierarquies) and with five partitions based in the hierarquies.

Virtual Server Configuration:

512MB Physical Memory.

CPU Intel Core Centrino 1.66GHz

Windows 2003 SP1

SQLServer 2005 SP2

Apologies about my english.

Thanks

Hi!

It is a little bit hard to tell without knowing more about your cube. Have you designed proper attribute relationsships and aggregations? You can download the performance guide for AS2005 pointed to a the top of this group.

Next, I think that a cube of that size and that many dimensions will require more memory than 512 MB. I also run AS2005 on my laptop but not as a virtual server(because it is slower) and I have 2 GB of physical memory.

The problem in Excel might be related to the physical limits of you machine. To do a real test of Excels limits you should run it from a workstation without any server software installed. In that case 512 MB can be enough even if most workstations today have at least 1 GB RAM installed.

HTH

Thomas Ivarsson

Cube Design - Number Max of Dimensions

Hi all,

Some cubes into my project have been designed containing 11 dimensions ? Is there a number max of dimensions? The performances look fine so far but I was wondering if it will be a good idea to split these cubes to have a limited number of dimension per cube.

Thanks a lot for your support

Juan

I think you're a long way off hitting the maximum:
http://msdn2.microsoft.com/en-us/library/ms365363.

The only thing you need to look out for when you start adding lots of dimensions to a cube is that aggregation design will take longer and the you're less likely to get good results using the Storage Design Wizard alone - you'll probably need to do Usage Based Optimisation to get the aggregation design you really need. It's also gradually emerging that putting all your data in one cube with multiple measure groups might not be the most efficient way of designing your cubes - see
http://prologika.com/CS/blogs/blog/archive/2006/06/28/1331.aspx

HTH,

Chris

|||

Chris,

Many Thanks for your feedback.

The tests of performance (processing of cubes) I have made meet our requirements.

Do you think that there will be an impact in the restitution of these cubes (with a lot of dimensions) via Excel (add-in) ?

Does the cube design with a lot of dimensions have an impact in the restitution performances ?

Thanks again.

Juan

|||

What do you mean by 'restitution', sorry? Do you mean query performance? If so, no there's no reason why a cube with many dimensions should perform worse than a cube with few dimensions, although as I said you need to be more careful with your aggregation design when you have many dimensions in order to get good performance.

Chris

|||

Sorry for my english...

I meant 'browse' the cube thanks to Excel (Add_in).

My tests of performance have been done to measure :

- performance to process cubes

- performance to access cubes with Excel

If I understood well, the design has a direct impact in the processing of the cube but not in the browsing of the cube.

Hope to be clearer...

Cheers

Juan

|||

Well, what I was trying to say was that adding dimensions to the cube doesn't necessarily cause worse query performance so long as you pay attention to your aggregation design. But it would not be true to say that cube design in general has no effect on query performance - it does.

Chris

Cube Design

Can two or more sets of 'non- related' tables be combine and form a cube? Currently I have only a query from the business domain and a cube was designed base on the query. If there are more unrelated queries coming up, should I add on to the same data source view or just add new data source view and new cube? Which is recommended?

Can I say one report model template (adhoc reporting) can only consist of one cube or possible to have many cubes?

Regards

Goh

Hello. By 'non-related tables' you mean that the result set of the queries do not share keys over their different source systems? Do the columns have the same type of information?

The standard solution for this is to build a data warehouse to create one version of the "truth" in the business. You do not load "information silos" or fragmented information into the same cube. In the data warehouse you can also keep history of changes in the source systems and be able trace what have happened.

If you, for some reason, cannot build a DW your best approach is to build separate cubes and separate dimensions for each query/source.

You can build a view and use TSQL UNION to build a general view of each source and create one cube on top of that. The problem with this approach is that in sales you must be sure that there are no internal transactions between each source. If so, your sales data will overestimate sales.

My recommendation is to build a DW and solve the problem with non-related tables there.

HTH

Thomas Ivarsson

Cube aggregations stop unexpectedly

I am trying to design agreegations for all the partitions of my MOLAP cube. The aggregation calculation process starts but stops at 2% performance (about 20MB).

I have tried to re-argregate so many times by selecting all the options :-

Estimated Storage reaches (100 MB) /
Performance gain reaches (30%)/
I click Stop

But, in all these cases it just stops at 2%. I cannot understand why? And its not one particular partition. Rather all the partitions (eight of them) stop at 2%. There is enough free hard disk space as well (27 gb)

Any, clues could be of great help. Thanks.

Any idea from the MS Analysis services group?|||

The problem is that not all attributes are considered during aggregation design.

Try creating more user hierarchies. Try marking cube attribute AggregationUsage property as Unrestricted.

Hope that helps

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights