Welcome :Guest
Congratulations!!!

Top 5 Contributors of the Month
AndyV
webmaster
SOHINI DASGUPTA

### Aggregations based on Dimensions dragged for Calculated measure from different fact table Using MDX

Hi,

I am a newbie to Mdx, i am working on education domain. I have a requirement to calculate student counts based on different fact views.

Especially for state,district and school levels we created three different fact views based on applying some conditions on original fact table.

Now i am using only one calculated measure named "Student Count" in my cube. My requirement is, when i drag my district dimension student count count based on district dimension should appear in my measure, if drag both districts and schools together then school facts student count should appear in my count, if i didn't drag any of the two dimensions then state level count defaultly aggregated.

For this, i thought write an mdx basing on dimension i am dragging, so i started with scope function for getting a different fact view based on different fact view,

Scope([Districts].[Districts],[Mesures].[Student Count])

this= ([Districts].[Districts],[Mesures].[District Student Count])

end scope;

like wise for school dimension and so on, but i am getting same value for both of the cases.

could any one help me out in getting solution for this. Is there anyother way other than creating new cubes for each level.

Thanks in advance. Any suggestions are appreciated.

Lakshman A N, S

### getting at non-measure data in fact table

I am creating a cube with a single fact based on order payments.  THe fields order# and seq# in the fact table have no related dimension.  The payment amount in that fact table is the measure I'm using.  So I have several dimensions - date, location, customer, etc....not a problem but for some reports I would like to pick up the order number.  So my question is given two non-measure fields in a fact table, how do I make them accessible in my cube?  I am thinking that I need to create a dimension based on my fact table but not sure so I'd rather someone set me straight first.

### RadioButton Groups, Table Rows And NamingContainers in asp.net

The RadioButtonList. Very handy for inviting users to select just one item from a list although because of the screen real estate it takes up, used less and less in favour of the DropDownList. In plain HTML, the browser knows to enforce the unique selected value amongst a list of radio buttons because they all have the same value for their name attributes.

### RadioButton Groups, Table Rows And NamingContainers

(In which AJAX-enhanced CheckBoxes become more useful than RadioButtons but inheritance saves the day, and a simple RadioButton-derived control establishes the purpose of a control's naming container)

The RadioButtonList. Very handy for inviting users to select just one item from a list although because of the screen real estate it takes up, used less and less in favour of the DropDownList. In plain HTML, the browser knows to enforce the unique selected value amongst a list of radio buttons because they all have the same value for their name attributes.

### Fact table in DSV vs partitions pointed to a different table

I am seeing an issue in my cube for a partition that is based on a separate table than the Fact table in the DSV. I have 8 partitions all from different physical tables. In the DSV I used 1 of those 8 partition tables as the "source" of the DSV so I could model the relationships between the fact and the dimensions. On 1 of the 8 it loads over 1 million rows from the partition into the cube, but when I use the browser to show the count in that particular partition it shows the exact same number of records that are in the table that was used in the DSV. The strange thing is all the other partitions work fine except this 1. I have deleted the partition and added it back multiple times and cant get it to work right. Has someone seen this problem before?   I have run into this a couple times, one way of fixing it was to recreate the entire project in a new project, copy all objects from the old projects and rebuild. I cant seem to figure out another way of fixing this.Craig

### Building Fact and Dim table

How to build Fact and Dim tables for below requirement. Fact records (approx 800k) has to be analyzed every day w.r.t AgeGroup's Every record in fact will have a DOBSID and age/agegroup should be calculated every day based on getdate()

### 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

### Update an accumlating shapshot fact table

This is my first time implmenting an accumulating snapshot fact table and I require some guidance. Accumulating snapshot fact tables show the status at any given moment. It is useful to track items with certain life time, for example: status of order lines.eg everytime there is new piece of information about a particular purchase, we update the fact table record. We only insert a new record in the fact table when there is a new purchase requisition. What I really need to know is how best to handle the updates.  This really feels very similar to managing SCD-1's in dimension processing! Anyone able to advise? thanks in advance Here is a perfect example we can use  http://blog.oaktonsoftware.com/2007/03/accumulating-snapshot-use-accumulating.html Figure 1, below, shows an accumulating snapshot for the mortgage application process. The grain of this fact table is an application. Each application will be represented by a single row in the fact table. The major milestones are represented by multiple foreign key references to the Day dimension—the date of submission, the date approved by mortgage officer, the date all supporting documentation was complete, the date approved by an underwriter, and the date of closing.

### daily complete cube rebuild four dimensions and fact table including remapping of all surrogate keys

Hi SSIS Engineers: Forgive me if this is a multi-forum question. Our primary activity in the next week is to automate the processing in SSIS, where I led the team to create complete processing flows for Full and Add in the order of Dimension, Measure Group, Partition, Cube, Database. These work. The problem occurs in a complete refresh of the ERP database that caused me manual effort inside SSAS, which I plan to find a way to automate in SSIS. I performed a complete refresh of our cube from the ERP source from a time perspective. We are automating this process in SSIS. In SSAS, I had to manually delete the four dimensions from the UDM view via the Solution Explorer. Since the complete refresh increased the surrogate keys in the dimensions and since the names were the same, I couldn't just drop the partition and reprocess the dimensions, since, in effect, new fact rows would have to be mapped to the new keys. SSAS held on to the old keys even with Full Processing of the Dimensions first, then the Cube. Until I dropped--deleted-- the dimensional tables from the Solution Explorer and the UDM then later readded the dimensions with the new surrogate keys (both add, update and delete dimensional attribute changes in full refresh) via the Add Dimension wizard, the cube kept the old surrogate keys and failed in measure group, fact, database and partition processing.

### Avoiding a SELECT distinct query generated by SSAS when using dimension derived from fact table

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

### [SCD] fact table and SCD

Hello

I've read several articles about SCD, most of them exmplaining the standard SCD type 2 rules, like a customer adress change.

My problem is about PurchaseOrders changes :

I have a fact table "Internet Sales", with a Total property for each sale

Imagine that this Total property can change (ie. : missing product cannot be delivered) --> the order is recalculated, and its total changes. Of course, I need to keep both informations for the order, because if I do a request for the sum of the order totals for example, I won't get the same results if the request is done with data as they where BEFORE or AFTER the Total change. .... hope that's clear.

I wanted to add a "start", "end" and 'current" columns on my fact table, but I read that it's not the good way to do.

Can you help me with that ?

Thank you

Olivier

PS : I didn't consider the option of setting the order total in a dimension.... hope that's not the good way to do !

### Bridge Table dimension or fact? updating from snapshots

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

### Building Cubes with less granularity than the underlying fact table

If I have a fact table with a geography dimension where zip code is the primary key in the dimension table and the foreign key in the fact table, is there a way to create a cube where the leaf level in the geography dimension is at a higher level, say City (or State), even if City (or State) is not an attribute on the fact table?

Alternately, if I have a fact table with dates but I only want monthly summary data in my SSAS cube, is there a way to make the Month level the leaf level member of the Time dimension in the cube without adding a Month foreign key column to the fact table?

It would seem to me that people would want to do this wiht some frequency; however, I can't find a way to do it in the SSAS documentation.  This link seems to suggest that the grain of the cube has to be basically the same as the grain of the fact table in all cases: http://msdn.microsoft.com/en-us/library/ms166573.aspx

### SSAS_Cube Question..(How to update the Shipdate in Fact table..)

HI Friends,
I have a question regarding SSAS.
My question is...
Our business system has orders taking online and the order table has Order_Completedate, Which tells when the Order is completed.
We also have a Shipment table which tells which order is shipped on which date.
so while calculating my Fact table i am taking like

select .........
from Dim_Order o
left outer join Dim_Ordershipment ors on o.orderid=ors.orderid.

So my issue is
My cube will be processed daily at 6 Am.
So if any order is completed and shipped before next day 6 Am then my Fact table has a Shipmentkey which is a Primary key of Dim_Ordershipment.
but if any order is completed today and shipped tomorrow after 6AM then my Fact table has every attribute but the Shipmentkey is loaded as Null.
I Load my Fact table on basis of checking weather a particular order is present or not.
like if it is present then don't load the record with that orderid else load it...

And one More reason why i am taking Left outer join instead of join at the Dim_Ordershipment is that my finance team checks daily in my

### Datetime fields within a fact table

Morning all,

I'm currently defining a fact table in my first datacube and have a question (basic one albeit).  Can a datetime column be left in a fact table as a measure or should this be stored only within a dimension table?  The other columns I have left in thus far are foreign keys for the dimension tables.

Any pointers appreciated.

Thanks

http://www.final-exodus.net

### Save Dimension member only when this have lines in Fact Table

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

### How to create Fact Table and Dimention Table in SSAS 2005 ?

Hi friends,

I am very new to SSAS world.

My question, can any one guide me , how to create Fact Table and Dimention Table ?

Thanks.

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