Max Date value from fact dimension

  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

This is quite difficult for me to communicate so please bear with me.

I have created my data source view in my solution. I have a central fact table surrounded by the dimensions. Several of the dimensions have dates which I converted to integers. Thinking that I would need separate date dimension tables so the date integers could find their dates I created a data dimension table "snowflaked" to the dimensions with dates.

An example is my dimPolicy table has dates of creation and ending.

I converted these to integers.

A table called dimPolicycalendar was made in the DSV and then linked. A dimension snowflaked to a dimension.

When I went to create a dimension for Policy it auto attached the dimPolicyCalendar. Then all my dates disappeared. And the generic values in the dimPolicyCalendar appeared.

HELP HELP HELP PLEASE!!!

How do I get my values back?

If I wasn't supposed to add a calendar table to the dimension how will I convert those integer values to the date values I want?

I am panicking a little because I can not find any helpful resources.

You are my last hope before we are pushed to an Oracle solution. Yuck!

 

 

---

BJ Gordon

I have 3 fact tables and about 10 dimensions. I want to relate both "Fact_Pop1" and "Fact_pop2" to Dim_AgeGroup. But Fact_pop1 has single age group and Fact_pop2 has no single age group (5 years age group). I spent about a day on this but I could't solve it.   Fact_Pop1 Age        Race_key            Gender_key      Geography_key              Pop_size 10           White                    M                                            CA                          10000    10           White                    F        

Hi all, Quite new to SSAS, wondering if anyone could help with the following... I've got a dimension with attributes that indicate geographical location based upon UK postal boundaries - so ~1.8m Postcodes > ~10000 Postcode Sectors (PCDS) > ~3000 Postcode Districts (PCD) > ~100 Postcode Areas (PCA). The problem I have is that the address information is not of the highest quality. I have matched 50% of my facts to postcodes, of the remaining 50% i've mapped them to PCDS, PCD or PCA, where possible, leaving the lower-levels as NULL in those cases. My attribute hierarchy works fine where I have complete records all the way down the hierarchy, but I only have a single UNKNOWN member at the top (GOR) level. I want an UNKNOWN member at each level. It makes sense to me how to do this using a Parent-Child dimension, but i'm keen to avoid that as the performance is terrible. I'm hoping there's a method of configuring this. Any help massively appreciated.

Hi, I am using a dimension derived out ot a fact table and the factt able primary key is dimension key. Issue is, there are large number of rows and so many attributes. SSAS issues distinct query and it takes large amount of time. Without the distinct statement, query takes only 3 min for 4 million rows. With the distinct, it takes 20 min. Becuase the fact primary key is the dimension key there is no need of a distinct statement. I know there is a option in the dimension to say "By Table" to avoid this. But unfortuantely, i breach the 4 GB limit for strings. Any suggestions for optimization? Thanks,  Sambath

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

I am using SSAS 2005. I have 1 fact table and 1 dimension. I would like to create multiple distinct counts in 1 Measure group, at least I would like them to appear as in 1 measure group to end-user. I have tried role-playing dimensions, and a roll your own approach that work in limitation but didn't scale. Any help and advice would be great.

Hello MDX gurus,

 

I have tried posting my problem before, however, I still haven’t gotten a response. Therefore I am going to try again... I am new to MDX, I have had a bit of experience, but nothing more complicating than creating simple calculated measures.

 

I have a data source which contains Injuries and their Status. An Injury can have it’s Status change depending on whether a doctor visit is involved. For example, an Injury can start out as a First Aid (FA) then change to a Medical Aid (MA) if the person seeks medical attention later on. It can then change to a Lost Time (LT) incident if told to take time off from their doctor. Then, if WCB declines their claim, the Injury can change back to a Medical Aid (MA). The LT/FA/MA status is called the Recordability by our business users.

 

Therefore, one Injury can have multiple Recordability records, and the ERD looks like this:

 

Injury -> InjuryStatus <- RecordableInjury

 

When a report is run, the Status (or RecordableInjury) that appears needs to be the latest one in the selected reporting period (based on StatusDate) for each Injury. Therefore, each Injury may be counted only once within a time period (i.e. Year, Quarter, Month, Week), with the most recent Status appearing for that time period.

&n

Hi,

Scenario: Bank Accounts and Customers. One Account can have many customers and many customers can have one joint Account. so its Many to Many relationship.

Special Scenaio: Bank provide us daily snapshot of all thier dimensions and facts, every night thier ETL run, and newsnapshot is available, previous is gone.

I am using SCD Transformation to update the dimensions, Type 1 for all the columns.

Tables1: DimAccounts (AccountsID(PK))

Table2:DimCustomers (CustomerID(PK))

Table3:DimBridge (AccountID (FK), CustomerID(FK), RelationShip (varchar10))

Question1: Are we supposed to treat bridge tables as Dimensions or Facts?

Question2:If it is to be treated as Dimension, How would I apply SCD Wizard to it, Since there are two business keys involved?

Question3: Do i need surrogate key in Bridge Table, like i have in other dimensions?

Thank You

 

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.

Thank you for your assistance.

 

 

 

 

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?

Thanks you in advance,

Daniel

I have one dimension table with over 600 item, but in my fact table only 40 of this member have lines (my fact have a filter for the 2 laste year)

 

How save on the dimension only this 40 members?

 

Best regards

 

 

Jaime

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

Please help.

 

Thanks in advance.

 

Hamlin Stephen

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.

 

Thanks in advance,

 

Hamlin Stephen

Hello Experts,

I am new to SSAS & I need to load Dim & Fact tables for a data warehouse. I've the basic idea to load them, but i dont have the exact picture to load a fact table. For eg. if we have 3 dimension tables like Dim_Time / Dim_Geography / Dim_Product and one Fact Table Fact_Product as described in most of the online examples. Then how to load them. I know for fact Tables we need to do aggregations. But how to apply those aggregations on what criteria.  In the said example if we need to load Fact_Product then we should be able to see sales by product, sales for a given point in time & sales for a given geographic location. Then how to do that. Do we need to apply aggregate for all the dimension tables

I know i am not much clear, but i hope you guys can understand. If not please let me know i will try to explain it more. Please clear my doubts.

Thanks & Regards,

Punu

Hello,

How do I need to handle a case in which the fields in a fact table that represent the foreign keys to the dimension tables might change? What kind of process do I have to do to the cube?

Thanks.