Analysis Service: filter fact table ?

I have a big table with several types of transsactions: PO (Puchase Orders, SO,( Sales orders), INV (Invoices) ...
I want to create cubes with only one type of transactions (1 cube for PO,...)
Where and how can I filter the rows I want to use in my cube ?
Thankscreate view, and for fact table in AS point at the view.|||create view, and for fact table in AS point at the view.

AMO Question - Distinct members in Cube

Hi,

How to filter members in a dimension.

To meet a requirement, I made a table as a Fact and a Dimension. In the said table, I have a column which is varchar and contains unique values always. They can be said as 'A' or 'B' or 'C'. For all records in the said table, any of these three values can be present. But they are not from any lookup table but come from a third party system.

Using the AMO object, I am listing all the values are members. But the problem I am facing is, I am getting all values as members. As per my requirement, I must list only distinct of them

I am setting the DimensionAttribute object's Usage property to Key, OrderBy to OrderBy.Name and Type to Regular. What other property need to be set to list only the unique values as members?

I request a quick response to this query. I am desperately looking for the reply to solve the problem.

Thanks

Sekhar PC

Sorry Sekhar.

But the description of your problem is not very clear. Can you try to describe what you are trying to acheve.

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

|||

Hi,

Thank you for your reply.

In my cube, I have a fact table which has a column of varchar type. Assume that, the possible values are A,B,C,D.

While building the cube using the AMO and VB.Net, I am required to list them as members in the cube. Essentially, I am required to fetch the distinct values from that column and list as members.

How to that using AMO and VB.Net?

Thanks in advance

Sekhar

|||

You dont use AMO for retreiving data from Analysis Services. For that you use ADOMD.NET object model ( not the only option, but it might be easiest for you).

Here is some information on how to use ADOMD.NET

http://msdn2.microsoft.com/en-us/library/ms123483.aspx
http://msdn2.microsoft.com/en-us/library/ms123477.aspx

You can try and get a book on Analysis Services: Teo Lachev's "Applied Microsoft Analysis Services 2005.. " is good one you can use.

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

AMO Question - Distinct members in Cube

Hi,

How to filter members in a dimension.

To meet a requirement, I made a table as a Fact and a Dimension. In the said table, I have a column which is varchar and contains unique values always. They can be said as 'A' or 'B' or 'C'. For all records in the said table, any of these three values can be present. But they are not from any lookup table but come from a third party system.

Using the AMO object, I am listing all the values are members. But the problem I am facing is, I am getting all values as members. As per my requirement, I must list only distinct of them

I am setting the DimensionAttribute object's Usage property to Key, OrderBy to OrderBy.Name and Type to Regular. What other property need to be set to list only the unique values as members?

I request a quick response to this query. I am desperately looking for the reply to solve the problem.

Thanks

Sekhar PC

Sorry Sekhar.

But the description of your problem is not very clear. Can you try to describe what you are trying to acheve.

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

|||

Hi,

Thank you for your reply.

In my cube, I have a fact table which has a column of varchar type. Assume that, the possible values are A,B,C,D.

While building the cube using the AMO and VB.Net, I am required to list them as members in the cube. Essentially, I am required to fetch the distinct values from that column and list as members.

How to that using AMO and VB.Net?

Thanks in advance

Sekhar

|||

You dont use AMO for retreiving data from Analysis Services. For that you use ADOMD.NET object model ( not the only option, but it might be easiest for you).

Here is some information on how to use ADOMD.NET

http://msdn2.microsoft.com/en-us/library/ms123483.aspx
http://msdn2.microsoft.com/en-us/library/ms123477.aspx

You can try and get a book on Analysis Services: Teo Lachev's "Applied Microsoft Analysis Services 2005.. " is good one you can use.

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

AMO - measure numeric Value

I'm building a cube dynamically and i want to list just the columns in the fact table that are numeric in order to make Sum's, AVG's and so on.

How can i do this using AMO ?, i'm wondering if i can do this from the FactTable's (Data Table) DataColumns, but there is no metadata that tells me if the values there are or not valid numeric measures.

Regards Lisber.

Hi. I don't know the answer to your AMO question. However, I do know that you can get the metadata about your fact table from the system views in SQL Server 2005. For example, if you want to know the columns in a fact table with data type Money or Float the following query works against the sales fact in AdventureWorks schema:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'FactInternetSales'
AND (DATA_TYPE = 'float' OR DATA_TYPE = 'money')

A more extensive article about SQL Server 2005 metadata is available from Database Journal at this link:

http://www.databasejournal.com/features/mssql/article.php/3508881

Hope this helps - Paul Goldy

|||Thanks for your answer Paul,

The Problem that i have needs another solution because :( , i'm creating the cube dynamically:

I have the DataTable related to the fact table, but when i start creating the measures i need to do this:

(Copied from AdventureWorks - AMO Example)

meas.Source = CreateDataItem(db.DataSourceViews[0], "FactInternetSalesReason", "SalesOrderLineNumber");

but when i'm doing this dynamically i need to verify if "SalesOrderLineNumber" is or not a measure.

So in my case a have the factTable lets say "FactInternetSalesReason ", and i can make a loop through all of its dataTableColumns, here is where i need to know if that column is or not a valid measure in order to add it.

I'm following the AdventureWorks example, but as far as i can see all the cube creation is hard coded.|||HI. I understand the issue better now. Sorry, but I don't know how to determine if a column is a valid measure in your scenario. Paul Goldy|||Hey Lisber..

I'm not sure i fully understand, but what i gather is you want to add the measures from the fact table if it is some sort of numeric datatype in the table?

If that's the case, you've already got the datatable, so you can do something like this:

Dim DC as new DataColumn

For each DC in YOURDATASET.tables("YOURTABLENAME").Columns
If DC.DataType.Name = "Int32" '//Just an example
'//Add your measure here
End if
Next DC

If that's not what you mean, sorry :) If that is what you mean, and need more additional information, let me know..
C|||

Hi

Chris !, exactly i was thinking in the solution you described before , but i was having some troubles, here is the code that i use to figure out if a

data column is or not a measure ( ie, if its numeric ).

// 1o I make a loop through all the columns of my fact table ("FactResellerSales"), here is the code:

private void showMeasures()
{
for (int i = 0; i < this.dataSet.Tables["FactResellerSales"].Columns.Count; i++)
{
DataColumn column = this.dataSet.Tables["FactResellerSales"].Columns;
if (isMeasure(column.DataType))
Response.Write(column.ColumnName + "<br>");
}

}

private bool isMeasure(Type type)
{

switch (type.Name)
{
case "Int16": return (true);
case "Int32": return (true);
case "Int64": return (true);
case "Decimal": return (true);
case "Double": return (true);
case "Byte": return (true);
case "Boolean": return (true); // see better
default: return (false);

}
}

If anyone knows another way to figure out which columns in the fact table are measures or not, please tell me, because this solution is for sure not the best one !