Welcome :Guest
Congratulations!!!

Top 5 Contributors of the Month
david stephan
yasminpriya
Gaurav Pal
Ram
christianasteves

### Getting counts by 2nd Date Dimension Attribute with Snapshot Style Fact Table

I have an MDX question finding hard to solve.  I have a Snapshot Fact Table with a snapshot of the records in the source system for each batch date.  All records in the fact table are assigned the batch date with the batch date key.  There are many records for each day and each batch date is an entire copy of the source records.  So, the grain of the fact table is one record for each batch date that exists in the source system.  These facts rows have another date in them for when the record was entered.  This date is different from the batch date in that the batch date is based on the day the batch was processed and the entered date is based on when the record was entered.  If a record was entered many days before, its batch date will be today but its entered date will be several days ago.  Therefore each day a copy of all the records entered the previous batch date and all the records added on today's batch date are present. Fact Table : FactSnaphshotKey (surrogate for easier administration) BatchDateKey (link to batch date dimension – date dimension, first in dimension list so it is used for semi aggregate measures) EnteredDateKey (link to entered date dimension – date dimension) Facts Count – measure for fact table - default measure from Analysis Services cube 2 Dim

### Running sum calculation based off date dimension?

I have a fairly simple calculated measure which does a running sum of a particular measure : --This does a running sum of the Net Measure up until the previous date (basically a "Starting Count" for a period. Sum(PeriodsToDate([Change Date].[Year - Month - Date].Levels(0), [Change Date].[Year - Month - Date].PrevMember), [Measures].[Net]) The problem is I have added a second hierarchy in the date dimension that includes quarter called [Year - Quarter - Month - Date]. Is there anyway around not having to create a seperate running sum calculated measure to use this new hierarchy? The PrevMember in the initial calculated measure works, although it reports the wrong number when used in conjunction with the Quarter hierarchy because it gets the PrevMember in the other hierarchy. What is the best practice for this situation?Craig

### Isolate only the Date part from MDX returned dimension

Hello,

I have an MDX query which returns a date dimension values as below

02 Aug 2010
03 Aug 2010
04 Aug 2010

Is there a MDX function i can apply only to isolate only the date part of the values like 02 Aug, 03 Aug.

I can do this quite eaily in SQL using Datepart..but i wasn't able to find a similar function for mdx.

### MDX Calculations error - The 'Date Calculations' dimension contains more than one hierarchy, there

Hi all,

I am using http://www.obs3.com/pdf/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf to make a seperate dimension for the data calculations, which works in my Sales cube. But when I use the same dimension in an other cube (Called 'Procurement') and copy the MDX in the calculations tab, I am getting some errors:

Error    57    MdxScript(Procurement) (9, 8) The 'Date Calculations' dimension contains more than one hierarchy, therefore the hierarchy must be explicitly specified.        0    0

But the 'Date Calculations' dimension does not have a hierarchy!

Now I don't know why I am getting these errors, could you guys help me out so I can understand them and fix the problem?

Daniel

### MDX Date Dimension Help

rewriting my original question...

when i don't include the [Dim Date].[Month Number Of Year]  on the columns, i get the correct value for Amount which is the amount between the date range in my where clause by days.

when i include it, it returns all of the amounts belonging to the [Month Number Of Year] which may be outside the date range.

how do i write this statement so that i can return the month, but only with the amounts entered within the number of days?

do i need to convert the [Dim Date].[Day] value?  if so, how?

-- incorrect results
select

non empty
{
[Measures].[Amount]
}

on columns,

non empty
{
[Dim Date].[Month Number Of Year].members
}

on rows
from myCube
where
(
{
[Dim Date].[2007/01/01]:[Dim Date].[2007/01/15]
}
)

-- correct results, but displays the days, i want it aggregated by month.

select
non empty
{

### Unalbe to create date dimension with fiscal start date 29 Sept

I am trying to create date dimension with 29-Sept as starting date of fiscal year. But the dimension wizard always displays errors related date conversion. The following is the environment I tried.

OS : Windows XP

SQL Server R2

Hamlin Stephen

### Multiple companies and fiscal calendars in date dimension table

We are creating an application where different companies can have their own fiscal calendar starting on different dates. For example one company’s fiscal year may start in April and some others in Sep. Also fiscal calendars may start at any date. (E.g. 29 Sept).

These fiscal calendars would be created for parent companies. We have added the parent company ids to the date dimension table. Please suggest the correct way of setting the attribute relationship in the above scenario. If this is not the right method to do this, please suggest the correct design approach for achieving this.

Hamlin Stephen

### SCD Type II with Date Dimension

We have emp_elig table as mentioned below

```SurKey	GroupID		ID		ID_Seq		datebegin				dateend			elig_status

19833	100000-01		100-01		000		2006-01-01 00:00:00		2010-12-30 00:00:00		1

19835	100000-01		100-01		000		2010-12-31 00:00:00		2025-01-01 00:00:00		0

19837	100000-01		100-01		001		2006-01-01 00:00:00		2006-12-31 00:00:00		1

19838	100000-01		100-01		001		2007-01-01 00:00:00		2025-01-01 00:00:00		0

19841	100000-01		100-01		002		2006-01-01 00:00:00		2009-02-16 00:00:00		1

19843	100000-01		100-01		002		2009-02-17 00:00:00		2025-01-01 00:00:00		0```

Note: elig_status 1 = Eligible

I do have SCD in SSAS which shows the hierarchy as mentioned above. Now I want to see something like given below:

Q1     Q2     Q3     Q4

Emp_Elig_Count       1        2       1       1

I don't know how can I link my DimTime (Date) between DateBegin and DateEnd of the above tabl

### MDX - Find last leaf member of Date dimension under selected member of Date dimension, independently

Hi all

I need to reproduce the behaviour of the averageofchildren function with the exception that the function should always build the average of all members (even when the value of a member equals null). As you know average of children function always build the average at the lowest level of the time dimension.

My date dimension looks like the following:

[Date].[Fiscal] user defined hierarchy:

-Year

-Period

-Week

if context of [Date].[Fiscal].CurrentMember corresponds to a Week, I can retrieve all weeks before the current week using

In Period: MTD([Date].[Fiscal].CurrentMember)  -- PeriodsToDate also works fine

In Year: YTD([Date].[Fiscal].CurrentMember)

if context of [Date].[Fiscal].CurrentMember corresponds to a Period

How can I get all weeks (NOT Periods!) contained in all Periods before and within current period since start of year?

What would be a generic expression that retrieves all weeks before and in independently of level of selected Date dimension member

Thanks

### Date Dimension

Hello guys,

I have created a date dimension in sql server and it is now part of my cube dimension.

The clarification I need is that, is it still necessary for me to add a dimension intelligence of type Time to this dimension before it is fully ready for use in the cube?

Thank you

### Dimension Date filter SCD

I have 2 date fields - AuditStartDate & AuditEndDate in Dimension DimCustomer.

This has foreign key relationship to DateKey in the DimDate dimension table

In DimDate Dimension table you have the dimension key. Datekey is derived based on date (YYYYMMDD) as integer.

DateKey Date

20101019 19/10/2010

20101020 20/10/2010

I want to filter out records in where AuditStartDate <= Current Date(Converted to (YYYYMMDD) integer) and AuditEndDate >= Current Date(Converted to (YYYYMMDD) integer)

### Time Dimension for YTD using assigned Month and Year integers without Date datatype

Hi, most time dimensions are setup using a base Date field in the fact table, and they have plenty of issues for time analysis as it is. However my fact sales records have the time aspect assigned by pre-calculated periods, because depending on various factors, monthly final invoices are all raised on varying days (usually 2nd friday of month but can change). The monthly period is therefore not a straight calendar month. Probably a very common scenario.

So, the invoicing system already assigns the year (ie 2009, 2010, 2011) and monthly period (1, 2, 3 ... 12 with 3 representing march, even though that might represent 13th march to 9th april) and I want to use those as Time dim so we can do YTD, growth-on-prev-year etc.

It looks like its best to setup 2 Dimensions to link to 2 DataColumns/Attributes in the Fact table (say, FYear and FMonth, both integers). That way I can assigned attribute names like March to key column 3. If I combined them into 1 dimension with both fields making up a single key column, would have to either repeat the month names or link it to another Star schema I believe.

I can use the Add Business Intelligence wizard to make the Dimensions into Time ones instead of regular but I'm still not totally sure if this is the best structure/method and once done, how to use the YTD calcs to show in the cube browser (and my MDX knowle

### Max Date value from fact dimension

I have a fact table FactSales & it joins to a dimension table DimCustomer.

The fact table has a date column besides other columns.  This will go as a fact dimension

My Query is like this

SELECT Measures.[Sales Count] ON columns,
non empty
{(
[DimCustomer].[Customer Name].[Customer Name].ALLMEMBERS *
............
............
.........Other members.........
............
)} ON ROWS
FROM [DATA MART]
WHERE <<Where Clause>>

I need a value which is the maximum date in the factdimension for each particular set of row that is returned.

Any ideas?

### Excel 2010 OLAP Pivot Tables - Can a Date Dimension name be used as a Date not String?

Scenario:  Have built a SSAS 2008 cube and am using Excel 2010 pivot tables as the UI to access the data.  The end-users want to be able to treat the Date Dimension name value as an Excel friendly date, mainly to apply custom formats for pivot charts (such as mmm-yy or dd/mmm/yyyy) and to be able to sort ascending and descending.

Problem:  Although the Date dimension attribute is setup with 'mmm-yy' formatted text as the name and a datetime value as the value, Excel refuses to treat the dimension name as a date.  The result being custom formatting is ignored and pivot sorting results in text sorting only (Apr-00, Apr-01 etc).

Investigation:  Have tried several string combinations for dimension attribute name (even using the Excel datevalue numbers) with no success. Have found some earlier blogs which go part of the way in explaining.  It appears that no matter what you set as the name value you are always going to end up with a string label that can't be formatted or sorted in any other way other than text, as indicated by Darren Gosbell answer to a similiar question.

“...When you display a dimension attribute in an Excel pivot table what you are seeing is the name of each member. The name property of

### Time, not just date, is part of PK--should I create a time dimension?

Hello,

Should I create a time dimension if the time is part of the primary key?  I'm trying to create a fact table of successful user connections.  Since each user can connect their device more than once per day, I don't have a complete primary key.  What's the correct way to handle this?

I just thought of something: should I be using storing an aggregate by date key instead, so that each line has a "ConnectionCount" column but not a "ConnectionDate" column?

Eric

CREATE

TABLE MyFact (
UserKey

### Prefixes on column headings of date attributes using shared dimension

When reusing a date dimension multiple times as in the Adventure Works cube, the attribute names are prefixed with the cube dimension name in the selector screen. However, when they are brought into the query, the prefix is dropped. So "Delivery Date.Date" becomes just "Date". This is also the case in Excel 2007 which can be confusing for users. Any way ar ound this?

Paul

### how to sort date dimension in SSAS 2008?

Hi I have SSAS 2008 DimDate dimension.

At source DimDate table has:
DateID : 20100518 (primary key), 20100519, 20100520,... (and SortKey too at T-SQL level)
MonthName : April, May, June,....
MonthNameYear : April,2010  May,2010  June,2010

I would like to sort dimension by MonthNameYear's attributes (currently I have order like January 2001, January 2002, January 2003.... but I want order like January 2001, Feburary,2001,March,2001 .......)
In SSAS visual studio, in Date dimension structure >> Attributes panr>> Month Name Year (properties : OrderBy, I set to Key, Name, AttributeKey, AttributeName and deployed but no luck in sorting).

Any idea how to sort date dimesion attributes? Thanks.

 Categories:
 ASP.Net Windows Application .NET Framework C# VB.Net ADO.Net Sql Server SharePoint Silverlight Others All