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

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

SSAS Look in range ???

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

Hi,  I am working on my very first star schema based SSAS cube.  I think it would be very simple for you, but I could use a lot of your help.

Here are the tables.  Two dimension table. One fact.  

In addition to the two dimensions, I will have region->country, year-qtr-week, BU-customerid as hierarchies.  I know the fact table is not pure star schema, I will keep it as it is as a start with.  so I will actually build the  region->country, year-qtr-week, BU-customerid hierachies off the fact table.  The fact table is inherited.  I want to leave as it is. 

I will build two dimensions on the dim tables.   what I need is to be able to take the count of week, amount of any region->country, year-qtr-week, BU-customerid , find the range in dim_dollarrange to get the description of the dollar range , also find the range in dim_weekrange to get the frequency description.

How do I do the range look up in SSAS?

Can you please help ASAP? I would really appreciate it.  this is my first sort of star schema prototyping.  I will expand later to get into more depth of development.  This is a start.  Thanks for your help !


ID    MinRange    MaxRange    Description
1    1

View Complete Post

More Related Resource Links

Range or List Filtering via SSAS cube in Excel 2007

Hi All, A client has posed an interesting question, as current users of business objects webi they can filter the results of a cube/universe by copy and pasting a range or list of values seperated by a ';' into a list filter that is availble. e.g. product2;product533,;product029;product8389 etc etc. The list can sometimes be hundreds long. How is that same function acheived in browsing a ssas cube in excel 2007, all I can see is that a user has to use the drop down list and individualy select the values they want. This does not seem like a great method! Any ideas? They are using SQL / SSAS/ SSIS/ SSRS 2008 R2 Cheers DC

SSAS date-range ownership of assets (many-to-many table)


The basis for this post is here: http://social.msdn.microsoft.com/Forums/en/sqlanalysisservices/thread/b586e9cb-2e4e-4bc8-be33-0b80d57c13bc

A many-to-many relationship exists in the database to record the ownership of an asset. 

The result I want to produce in the cube is (Company is divided by pipes, we see comp A owend asset 1 in 1996, 1997, 2002 and 2003; Company B in 1998, 1999, and 2000): (imagine data for each column is financial)

Asset 1
Company A         | Company B                    |  Company C    |   Company A     |     Company D
1996        1997    | 1998      1999    2000      |  2001               |   2002               |       2003


range inclusive ssrs report builder using SSAS as source unsolved



I am trying to use MDX query designer in SSRS report builder. I choose a date dimension and i need to include a range for it . So i selected range inclusive as the filter.

When i do that and give default dates it works fine. When i select parameters it and hit ok. it immediately throws a error.

"Query (1, 216) The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER function were violated."

So i went ahead changed it to scripting mode and removed the "constrainted" flag and ran it . but then a different error comes up


then i get this error.

"Query (1, 216) The STRTOMEMBER function expects a member expression for the 1 argument. A tuple set expression was used."

what do i need to do to make this compile correctly. Please suggest. I am working on this for 2 weeks. 

ListViewControl - how to group items by their value, for eg. date range



I have a table containing courses, columns are 

Name - "Yoga Classes"

DateStart - "2/23/2010"

DateEnd - "2/24/2010"

I am using ListView Control to bind it, is there a way to display them and group them by Month? 

for eg.


Piano Class (10th Jan 2010 - 11th Jan 2010)
Guitar Class (10th Jan 2010 - 11th Jan 2010)


Yoga Class (15th Feb 2010 - 16th Feb 2010)

Thanks for help.

System.DateTime.AddDays: Value to add was out of range.


I'm getting the following error:

[ArgumentOutOfRangeException: Value to add was out of range.
Parameter name: value]
   System.DateTime.Add(Double value, Int32 scale) +7657639
   System.DateTime.AddDays(Double value) +19

The line from which this error originates has the following code:

expires = Now.AddDays(30)

It occurs irregularly and irreproducably. The server date/time is correct and set to GMT. I've heard that it could be some curiosity to do with timezones, but that is speculative.

It seems that we're some time from the year 10,000 yet, so I can't understand why adding thirty days to the current time could cause this! Any ideas? Culture settings perhaps?

How i find out light and dark color range??



I am showing preview of text in text box.

But if text color is light(Like white) and i want to show that text preview than text is not visible because textbox background color is white.

Can i get the range of dark and light color so i'll change textbox backgroun color accordingly.

Thanks in advance.

Kerberos between MOSS 2007 and SSAS 2005


I realize this is probably going to be one of those vague questions that I am not going to get much help on here, but I thought I'd give this a shot before we go the MS Incident route on monday.

We have tried to setup Kerberos between MOSS 2007 AND SSAS 2005 to no avail.  We have been through the knowledge base articles outlining the setup multiple times with all the experts on MOSS and Security here where I work.  We've used other materials we have on kerberos here.  But the end result is that the double hop is not happening.  We are trying to connect three ways: excel services, ssrs 2005 in integrated mode, and Sharepoint KPI's (using analysis services).  In every case the connection is not happening.

Other details are that the ssrs integrated mode seems to be setup right because I do get a report (albiet all it has is a connection error message).  Excel services works fine if I use the unattended service account, but when I switch the odc file to windows (should cause kerberos to kick in) it fails.  When I try to add a kpi to the kpi list it can't retrieve a list of kpi's from ssas.

In all cases I am the user trying to perform these operations, and I have total access to the cube -- I'm the developer.  I have no problems connecting to the cube directly through excel, so the security at that end passes t

Calculated Columns in a Form Library receive error: Value does not fall within the expected range.


I have a forms library that posts the date the form was created. We would like to create a calculated column that allows us to look at these forms based on the month and year they were created. I had created a calculated column that extracts the month and replaces the number with a text string by using an if statement; "IF(MONTH([Proposal Date])=1,"January","") It goes on to evaluate each month and replace the number with the text string. This formula worked for quite some time, though now when I try to add any calculated columns or edit any existing calculated columns on this form library I receive the following error: Value does not fall within the expected range. Now my existing calculated columns (like the one above) do not work and I can not create new calcualted columns. Any insight would be awesome as this has been plaguing me for a couple of months now.

When create a suvery using type Rating Scale, is it possible to create 5 range text?


When create a suvery using type Rating Scale, is it possible to create 5 range text?

Default range text only show 3 fields

SSAS logistic regression vs. vanilla logistic regression

I recently ran the same data set through the SSAS logistic regression (Neural network - Hidden layer disabled) as well as a vanilla logistic regression procedure. I am aware that different processes are followed in order to obtain the resulting co-efficients, however, has anyone got further information as to how close the results of the SSAS logistic regression equation are in comparison to logistic regression equations contained in other statistical packages such as SAS or SPSS? I do understand that the respective procedures are dependant on the quality of the data that they are applied to. I assume that the SSAS logistic regression may perform more favourably on one dataset in comparison to a vanilla logistic regression and vice versa. I am aware of the following post however it does not go into much detail concerning the results of the comparison. http://social.msdn.microsoft.com/Forums/en/sqldatamining/thread/4f381c6b-2471-4d4c-a022-316a1073184d Further info would certainly be appreciated.

how ssas generate sql queries when processing dimensions and partitions?

hi all, how does ssas2008 generate sql queries to read data from the source, where processing dimensions and partitions? is there any reading meterial that clearly explains how the sql queries are constructed according to properties of dsv, dimensions and measures and partitions.   thanks in advance.Andrew Chen Interested in BI related technologies

how to move SSAS applications between windows servers

Hi All, We have separate windows servers hosting production and development environments.  Each environment consists of MS SQL Server and SSAS, studios, etc.  What would be the best way to move MS SQL Server databases and SSAS objects from one server to another?  I have been looking for some import/export applications but have not been able to find anything. Thanks for any help, Thanks, Roscoe

Java Hibernate and OLAP(SSAS)

I am trying to create hibernate connection using olap4j driver I am getting this error- java.lang.IllegalAccessException: Class org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection can not access a member of class org.olap4j.driver.xmla.XmlaOlap4jDriver with modifiers "protected"     at sun.reflect.Reflection.ensureMemberAccess(Reflection.java:65)     at java.lang.Class.newInstance0(Class.java:349)     at java.lang.Class.newInstance(Class.java:308)     at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.createConnection(JDBCConnection.java:205)     at org.eclipse.datatools.connectivity.DriverConnectionBase.internalCreateConnection(DriverConnectionBase.java:104)     at org.eclipse.datatools.connectivity.DriverConnectionBase.open(DriverConnectionBase.java:53)     at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnection.open(JDBCConnection.java:72)     at org.eclipse.datatools.connectivity.drivers.jdbc.JDBCConnectionFactory.createConnection(JDBCConnectionFactory.java:53)     at org.eclipse.datatools.connectivity.internal.ConnectionFactoryProvider.createConnection(ConnectionFactoryProvider.java:83)     at org.eclipse.datatools.connectivity.internal.ConnectionProfile.createConnection(Connectio


I want to develop one report for that I connected cube from the Excel, my requirement is I want to show data BSM wise, ASM wise, FO wise, Distributor wise, Sales man wise nothing but it is salesman hierarchy. monthly retailing Quantity and retailing value. April 10, may10,june 10 and July10 for the following products No1 90 g all variants,Nupur,Expert,Haircolour,Cinthol How to achieve this plz give suggestion it is an urgent requirement    

DR Setup for SSAS 2008

Hello Guru's, I'm new to SSAS env. and want to set up its DR env. Can anybody guide me with any good strategis and implementation practices. We are using SSAS 2008 on 2 node Cluster Server. Thank you in Advance.- Nilesh

Query Performance & Overall Design - SSAS MDX WCF

Hi All, We have a Cube which is to be queried by the Online system , using WCF service. Peroformance of the queries running on the Cube is not really very good , as we have to calculate various percentile (which are all calculated members) on the cube, this howver works well within limits of 5-6 secs for small sample size. But this goes beyond the threshold of 30 secs when the record counts increases. What we have is our SSAS Cubes , and we have WCF Service querying the Cube using ADOMD.NET. This may not be one of the best way to achieve this requirement , but we are kind of completed with development and it may not be feasible to work on another approach, what we are looking for is optimize this design and make it work with in expected time limits of 5-8 secs. Kindly let me know, if i am not clear or if you need any more info to suggest something ! Thanks a lot for your help !   Kindly

Determining the status os the Batch job that process SSAS Cubes

Hi All, I have a cube and that is processed through a batch job. The problem I am facing is there are many packages which are calling this Batch job to process the cube. Is there a way to determine the state of the job before the packages call this batch job to process the cube. And in which database can I find all the information about the jobs on the server. Thanks for your help. Vineeshvineesh1701
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