How to create many-to-many relationship between two different attributes within a cube?

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question about how to create many-to-many relationship between two different attributes within a cube? (e.g. In insurance case, I want to create a many-to-many relationship between attribute broker and attribute assured company, as one broker may have many different assured companies insured under each of them, and each insurred company may be insured under different policies under different brokers). It seemed we could only create one way relationship between them, but cant make it another way around. But this is the common case in the data though. Could please any experts here shed me any light on it?

Thank you very much in advance and help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hi Helen,

take a look at this great paper by Marco Russo:

http://www.sqlbi.eu/Portals/0/Downloads/M2M%20Revolution%201.0.93.pdf

There are many examples in it, and probably also what you are looking for (may be the one about bank accounts).

Francesco

|||

Hi, Francesco,

Thanks a lot for the very helpful and great paper.

With best regards,

Yours sincerely,

How to create many-to-many relationship between two different attributes within a cube?

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question about how to create many-to-many relationship between two different attributes within a cube? (e.g. In insurance case, I want to create a many-to-many relationship between attribute broker and attribute assured company, as one broker may have many different assured companies insured under each of them, and each insurred company may be insured under different policies under different brokers). It seemed we could only create one way relationship between them, but cant make it another way around. But this is the common case in the data though. Could please any experts here shed me any light on it?

Thank you very much in advance and help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hi Helen,

take a look at this great paper by Marco Russo:

http://www.sqlbi.eu/Portals/0/Downloads/M2M%20Revolution%201.0.93.pdf

There are many examples in it, and probably also what you are looking for (may be the one about bank accounts).

Francesco

|||

Hi, Francesco,

Thanks a lot for the very helpful and great paper.

With best regards,

Yours sincerely,

How to create cube in sql2005?

Hi experts,

I want to create a cube using sql2005. I have read some articles which told me to create it through Visual Studio and still i cannot find where i should do that. I cannot find an "Analysis Manager" UI in sql2005 or Visual Studio. If any body could help i will really appreciate. Thanks

Hi paul_sh,

Which version of visual studio are you using now?

The "analysis manager" UI only exists in sql2000 and has migrated to Visual Studio since sql2005. Now if you want to create cubes , first open visual studio (the version should be 2003 and above), and click NewProjects. Choose project type as Bussiness Intelligence Projects and select the template of " Analysis Service Project" then click ok. After that you can edit your cube freely(note there are lots of pre-defined templates which you are make use of)

Hope my suggestion helps

How to create Cube

Hi All,
I want to create a cube using MSSQL2000 i dont know any thing how to
create and what is parameter required so if any body help me i will be
thankfull to him/her.For information : i have 4 tables
custdetailtable which contain all about a customer as per example
customer ID,name,add,CUSTOMERID IS PRIMARYKEY,
Proddetail table which contain all about a product mean
productcode,productname,productvalue,dman (dateof manufacture),dexp
date of expire,etc etc PRODUCTCODE IS PRIMARY.
Sales this table contain data aboutsale mean sales detail
cusomerid,productcode,noofpieces,priceperunit,prod uct name,date of
sale. etc etc SALEID IS PRIMARYKEY,PRODUCTCODE AND CUSTOMERID IS
FOREIGNKEY AND PRODUCTCODE IS PRIMARYKEY,
and atlast i have a table name stok detail which contain product
id,DTOFman and DTofexpire and amount of product
here product id,DTOFman and DTofexpire are foreign key.Hi

I am not an expert on this but I would expect a good start is to read:

http://msdn.microsoft.com/library/d...tbuild_6b77.asp
http://msdn.microsoft.com/library/d...tbuild_0lyq.asp

I would also recommend reading some of the background information.

John

"Biswajit Barik" <biswajitbarik@.rediffmail.com> wrote in message
news:311aa9ac.0307252240.5d3f4bd1@.posting.google.c om...
> Hi All,
> I want to create a cube using MSSQL2000 i dont know any thing how to
> create and what is parameter required so if any body help me i will be
> thankfull to him/her.For information : i have 4 tables
> custdetailtable which contain all about a customer as per example
> customer ID,name,add,CUSTOMERID IS PRIMARYKEY,
> Proddetail table which contain all about a product mean
> productcode,productname,productvalue,dman (dateof manufacture),dexp
> date of expire,etc etc PRODUCTCODE IS PRIMARY.
> Sales this table contain data aboutsale mean sales detail
> cusomerid,productcode,noofpieces,priceperunit,prod uct name,date of
> sale. etc etc SALEID IS PRIMARYKEY,PRODUCTCODE AND CUSTOMERID IS
> FOREIGNKEY AND PRODUCTCODE IS PRIMARYKEY,
> and atlast i have a table name stok detail which contain product
> id,DTOFman and DTofexpire and amount of product
> here product id,DTOFman and DTofexpire are foreign key.|||It is impossible to pass full data warehousing knowledge in a newsgroup
posting but here is my attempt :)

First get it out of your transactional environment (OLTP). Use DTS to
transfer this to another database (preferably another server depending
on size).

You will want to decide the subject matter of your cube which for you
would be the sales. This is your lowest granularity and you will use
this table to create what is called your fact table. Your fact table
will consist of the lowest granular aggregations (sales) and foreign
keys back to the other tables which will be used as dimension tables.

This is the structure that you will use to build the cube from. You need
to install Analysis Services for SQL Server 2000. Go into Analysis
Manager and say new database. call it whatever you want.Here you will
add a data source that points to wherever you sent your transformed
table. then right-click on cubes and say new cube, use the editor not
the wizard. it will ask you what tables you want to use, specify your
fact table (the one with sales data and your key columns) and add it.
You can then add your dimension tables (details - like customer, product
and possibly stock in your case). In the cube editor you will create
dimensions (all real cubes need a date dimension) dimensions are
typically what the customer says they want to see the data "by"
Examples:

I want to see the number of sales by region (region would be a
dimension)
I want to see the average sales by customer (customer would be a
dimension)

Again, these are examples, might not apply to you.

Then you will pick your measures, these are your aggregations, WHAT do
they want to see (dollars, total orders, etc.)

Calculated members are more advanced and you can get into those by
looking at MDX examples. From there you need to process the cube and
then you can connect to it by using Excel pivot tables (or something
better once you get the hang of it)

If you want to know the theory and use of data warehousing/cubing get
Ralph Kimball's data warehouse lifecycle toolkit book, if you want to
quickly learn how to use analysis services get Reed Jacobson's "Analysis
Services: Step by Step"

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||BTW: Before I get attacked :), my post, of course, if no where near full
data warehousing information. There are several things that were left
out, checking out those two books and websites like dmreview.com or
intelligententerprise.com will help get more DW knowledge.

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

how to create chart in the cube browser

Hi,
I have a cube and I am trying to browse the cube in the BI studio. When I slice and dice the cube the browser pane shows the data in a list format. If I want to see the data in a chart how do I do that.

thanks

I don't know how to create a chart within the cube browser of BI studio (not sure it's possible). However, a viable alternative is using the Cube Analysis add-in in Excel to query the data and use the native charting capabilities of Excel on your query results.

Hope this helps.

PGoldy

|||Thanks
And where and how do I get the cube analysis add-in?

It is really painful to see not to find any charting capabilities in the cube browser.|||

Hi. You can find the add-in at the link below on the microsoft site.

http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en

PGoldy

How to create a MAX Named Set for a date dimension?

I have an OLAP cube and I need to create a Named Set to return a latest date from a date dimension. For example I have the following dimension:

[Account Period].[Account Period].[Prescription Date].[Prescription Date].12-July-2006

Based on this dimension structure, how to create a named set only to return the latest date in any grouping of the other dimensions. Or to create a named set is not the correct approach to this?

You can simply use the Tail function unless your date dimension contains dates into the future for forecasting or other measures:

Tail([Account Period].[Account Period].[Prescription Date].Members,1)

If you have dates into the future you can use a measure group to filter the dates so that only dates with data for that measure group are considered:

Tail(Exists([Account Period].[Account Period].[Prescription Date].Members,,"Sales"),1)

HTH,

Steve

|||

Thank you for your reply. I guess that it is best to put down what I really want to achieve here and for the expert to give me the best advice. I am designing a report for a pharmist on a OLAP cube. the OLAP cube consists of the following dimensions:

Measure: Count of the logical primary key (treatment count)

Dimension: Patient_ID.Patient_ID.Patient_ID

Dimension: Account_Period.Prescription_Date.Prescription_Date

Dimension: Drug.Drug_Name.Drug_Name

Dimension: Hospital.Hospital.Hospital

Dimension: Drug.Drug_Ingriedient..Drug_Ingriedient

I need an MDX query for the report so that for a selected Drug_Name (first report single parameter) and the selected Drug_Ingriedient (second report multiple parameters) the "latest" Prescription_Date of the selected Drug_Name, the "earliest" Prescription_Date of the selected Drug_Name, the duration of the selected Drug_Name given to this Patient_ID, the Drug_Ingridient at the "latest" Prescription_Date (Drug_Ingridient is one to one relationship to the Drug_Name, different Drug_Ingridient can occour on the same Drug_Name but not on the same Prescription_Date), the hospital where the Drug_Name prescripted to the Patient_ID at the "latest" Prescription_Date (Patient can be prescripted the selected Drug_Name from different hospital over the period of several years) and the total count of the Drug_Name treatment.

The dataset should consists of eight columns, Patient_ID, Latest_Date, Earliest_Date, Duration, Drug_Name, Drug_Ingridient, Treatment_Count, Hospital.

Thanks.

|||

This will be tough to do via MDX on this discussion board. There are several questions that I need to ask.

1.) What is the formula for "Duration"?

2.) Are the "Earliest" and "Latest" dates for all prescriptions given to the patient?

My email is stevepon@.microsoft.com if you want to send me information off-line. A copy of your AS project files would be a big help.

Steve

How to create a dimension for multi-value fields

Hi,

I'm new to SSAS and really confused... I'm creating a cube for "Accounts", each account can belong to N categories, I created a "bridge" table on my model where I say:

Account 1 - Category 2
Account 1 - Category 3
Account 2 - Category 2
....

I dont know how to use SSAS to use the bridge table accordingly so whenever I'm browsing the cube using DimCategory and another dimension, lets say DimTerritory I get a proper count. Can you tell me how should I proceed?

Thanks.

Hi,

take a look into Many-to-many dimensional modeling paper from Marko Russo.

It's really good stuff about problems like your one.

HOw to create a CUBE

Hi,

I am quite familiar with Pivot Tables and understand the concept of dimensional analysis. I want to take my understanding to the next level and want to learn how to make cubes (and analyze them) using SQL server 2000.

I'll appreciate if you can suggest a URL and ideally a book or two which will help me in this regard.

Many thankxs u in advance.

-JAnalysis Services step-by-step by Reed Jacobson is real easy to follow. If you are wanting more on DW theory, data warehouse lifecycle toolkit by Ralph Kimball.

Also, don't forget that BOL has a lot of informaiton on it about analysis services.

HTH|||Originally posted by rhigdon
Analysis Services step-by-step by Reed Jacobson is real easy to follow. If you are wanting more on DW theory, data warehouse lifecycle toolkit by Ralph Kimball.

Also, don't forget that BOL has a lot of informaiton on it about analysis services.

HTH

Many thanks. I'll try to get these books. Can you in the meanwhile recommend and URL also?|||For DW theory and design this is good

http://intelligententerprise.com/ports/search_dw_fund.shtml

To download BOL, go here

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp?

How to count distinct members and keep good performance

Hi all,

I have a simple cube with only one measure : "Amount", I have three dimensions Supplier, Category and Company.

I would like to display a matrix with Categories as rows, company as columns, the amount and the number of distinct suppliers who appaers for each category/company.

I tried to create a calculated measure like this:

Count(Crossjoin({[D Supplier].[Supplier Code].members}, {[Measures].[Amount]}), EXCLUDEEMPTY)

The result is OK, but when I add this calculated Measure to my query the execution time jumps from a few seconds (<10) to a few minutes. Am I missing something ?

Anyone have an idea to do this in a better way ?

On-the-fly distinct counting will be always slow. Any reason not to implement a distinct count on a separate measure group?|||

Indeed adding a separate measure group did the trick.

Thanks.

|||

Now I have another problem, for aggregating the Distinct Count, in the Cube browser it works properly, but in SSRS in a matrix it doesn't. As the query returns flat result, if I Sum the distinct count measure I have more suppliers than I should in the totals, the matrix just sum the values from the query.

How can I keep my Distinct Count working in a matrix ?

Is there any way to create the same measure but without the link to a particular dimension. What I have in mind is returning two columns for my distinctcount, one with the detailed count, one for the aggregated total by category. But I don't see how I can achieve this. Any idea ?

|||Create a calculated measure that includes the distinct count measure and the ALL member of all dimensions included in the query.|||Try using the Aggregate() function in the matrix instead of Sum() as exlained in more details here.|||Thanks a lot that solved my problem.

How to count distinct members and keep good performance

Hi all,

I have a simple cube with only one measure : "Amount", I have three dimensions Supplier, Category and Company.

I would like to display a matrix with Categories as rows, company as columns, the amount and the number of distinct suppliers who appaers for each category/company.

I tried to create a calculated measure like this:

Count(Crossjoin({[D Supplier].[Supplier Code].members}, {[Measures].[Amount]}), EXCLUDEEMPTY)

The result is OK, but when I add this calculated Measure to my query the execution time jumps from a few seconds (<10) to a few minutes. Am I missing something ?

Anyone have an idea to do this in a better way ?

On-the-fly distinct counting will be always slow. Any reason not to implement a distinct count on a separate measure group?|||

Indeed adding a separate measure group did the trick.

Thanks.

|||

Now I have another problem, for aggregating the Distinct Count, in the Cube browser it works properly, but in SSRS in a matrix it doesn't. As the query returns flat result, if I Sum the distinct count measure I have more suppliers than I should in the totals, the matrix just sum the values from the query.

How can I keep my Distinct Count working in a matrix ?

Is there any way to create the same measure but without the link to a particular dimension. What I have in mind is returning two columns for my distinctcount, one with the detailed count, one for the aggregated total by category. But I don't see how I can achieve this. Any idea ?

|||Create a calculated measure that includes the distinct count measure and the ALL member of all dimensions included in the query.|||Try using the Aggregate() function in the matrix instead of Sum() as exlained in more details here.|||Thanks a lot that solved my problem.

How to count distinct members and keep good performance

Hi all,

I have a simple cube with only one measure : "Amount", I have three dimensions Supplier, Category and Company.

I would like to display a matrix with Categories as rows, company as columns, the amount and the number of distinct suppliers who appaers for each category/company.

I tried to create a calculated measure like this:

Count(Crossjoin({[D Supplier].[Supplier Code].members}, {[Measures].[Amount]}), EXCLUDEEMPTY)

The result is OK, but when I add this calculated Measure to my query the execution time jumps from a few seconds (<10) to a few minutes. Am I missing something ?

Anyone have an idea to do this in a better way ?

Try

Count(NonEmptyCrossjoin({[D Supplier].[Supplier Code].members}, {[Measures].[Amount]})

|||Thanks for the answer, but I still have very long execution time when using this expression.|||How many cells are there in the query? If there are many cells, then the poor performance is likely due to the AS server using a slow cell-by-cell query execution plan. If you see a single CPU pegged during query execution on the AS server then it is an indication of running such a slow query plan. Improving performance for this kind of queries is one of the proposed work items for the next major release of the product.