STDDEV (standard deviation) MDX (calculated members)
Hi all,
I was wondering if there are any examples in MDX for calculating stddev.
I know there is a function STDDEV that I can use but the scenario below might not work.
Fact table is event based facts.
I have 1 date dimension (date portion only), 1 user dimension, 1 application dimension
there is also a Time dimension (24 records, hour only, 12 AM, 1 AM, etc..)
1. given 2 date ranges (including time), calculate the STDDEV and STDDEV ERR
the formula is:
STDDEV = Square Root ( (Summation of (X - Xavg)^2) / (N-1) )
STDDEV ERR = (Xmax - Xavg) / STDDEV
I was thinking of the following example:
for date range between Date1 and Date2
Count of Events Time
3 8 AM
2 9 AM
5 10 AM
6 11 AM
3 12 PM
8 1 PM
7 2 PM
5 3 PM
5 4 PM
3 5 PM
----------------------------------------
47 events / 10 time intervals = 4.7 Average
STDDEV = SQRT ( ((3 - 4.7)^2 + (2 - 4.7)^2 + (5 - 4.7)^2 + (6 - 4.7) ^2 + (3 - 4.7)^2 +
(8 - 4.7)^2 + (7 - 4.7)^2 + (5 - 4.7)^2 + (5 - 4.7)^2 + (3 - 4.7)^2 ) / (10 - 1) )
= approximately 1.9
STDDEV ERR = (8 (which is the max) - 4.7 (which is the average) ) divides by STDDEV (which is 1.9)
ATTEMP SOLUTION:
1. I created a Count Measure
2. I created Max(TimeKey) Measure to be used as N in the STDDEV formula above
3. I created a Sum Measure of the above Count Measure in #1
4. so Average = Sum in #3 divides by N in #2
so based on these and the formula
STDDEV = Square Root ( (Summation of (X - Xavg)^2) / (N-1) )
STDDEV ERR = (Xmax - Xavg) / STDDEV
QUESTIONS:
1. how do I get sum of (X - Xavg) ?
2. how do I get Xmax ?
3. is this the right way to do this? any resources would be helpful.
The idea is that the user wants to raise a red flag if the Counts of events (fact records) is more than a certain STDDEV threshold.
thank you
- Changed type Ed Price - MSFTMicrosoft employee Friday, April 3, 2015 1:40 AM
How to ignore a dimension in MDX?
SurveyID ( --> Survey Dimesion)
GroupID( --> Group Dimension)
StatusID ( --> Status Dimension ... e.g. Complete, InProgress, New)
GroupCount (the actual measure)
How would I create a calculated member that would return the "overall" count for a specific group in a survey (e.g. ignore Status dimension) regardless if the user is slicing the data by Status or not?
Thanks again - wgpubs
- Changed type Ed Price - MSFTMicrosoft employee Saturday, April 4, 2015 2:07 AM
Reply:
Try this ("ignoring" the status dimension - your exact naming may differ):
CREATE MEMBER CURRENTCUBE.MyCount AS
'([Status].[StatusID].[All StatusID],[Measures].[GroupCount])';
------------------------------------
Reply:
In my slicer I got survey, group and response status ... the kind of report I'm trying to generate in SSRS needs to looks something like this:
Survey Group Count OverallCount
... where Count should use the ResponseStatus dimension (so that users can filter out certain statuses and what not thru a RS parameter) while OverallCount should not.
------------------------------------
Reply:
------------------------------------
Reply:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Overall Group Count]
AS ([Response Status].[Response Status].[All], [Measures].[Group Count]),
VISIBLE = 1 ;
------------------------------------
How to create an age dimension
Hi
I have a time dimension and i'm wondering how to create a dynamic age dimension on SQL AS 2K. It would be very nive to represent an entity by its age or (age interval ).(The age should increase everyday)
Can anyone help
Regards
- Changed type Ed Price - MSFTMicrosoft employee Friday, April 3, 2015 1:41 AM
VS Express free for good and other fun stuff
A whole bunch of exciting new stuff announced around the Visual Studio Express Editions today. VS Express is now free for good, not just 1 year (just like SQL Server!). Also lots of great new content and fun projects to do.
See Dan Fernandez's blog posting at http://blogs.msdn.com/danielfe/archive/2006/04/19/579109.aspx for lots of details.
Best, MJ
Reply:
can i used it for network applications/solutions?
------------------------------------
Teacher Starter Kit: Missing "Microsoft.ReportViewer.Common"
"The referenced component 'Microsoft.ReportViewer.Common' could not be found"
Then you will need to install this first:
Microsoft Report Viewer Redistributable 2005
New Server Registration to External Database
Hi All,
Quick question ...
Is it possible to add an external server registration using SQL Server Management Studio Express CTP?
Reason being I have been doing some work from home and need to port the data back to the office who are on SQL 2000. I have a database with my web hosting company and wanted to export the data to the 'live' database so I could import to the office database - any ideas?
I've tried to connect using my login details and I know the live DB is operational as I can connect through my work system. I just can't seem to be able to do it on my system at home.
Any help would be greatly appreciated as it's been a real pain to get stuff transferred. I've so far had to generate SQL script to create the procs and tables for when I get to work - the proper down side is that I lose the content of the tables which not much right now but will be loads soon!
Many Thanks
--
I have just dicovered that the CTP management studio will not connect to SQL 7 - is there any alternative or does the new version compensate for this?
Reply:
There is now way to connect to SQL Server 7 in Management Studio(Express Edition or otherwise).
If you want to move the data from 2005 to 2000/7 your best bet is BCP or DTS/SSIS
------------------------------------
Reply:
I'm afraid you will have to excuse my ignorance here as I am not a terribly experienced user of SQL Server. I've only be developing for the last year and a half and have always been able to use Enterprise Manager at work as everything is SQL 2000. This fortunately also connects to SQL 7 but SQL 2005 Express rather conveniently appears to have no easy backward compatibility.
So - what is BCP, DTS/SSIS and where might I find them on SQL 2005 Management Studio CTP - if that's where I should be looking?
So far, in order to migrate the structure of a database from 2005 to 2000 I have had to generate SQL scripts to create the tables and procs. A bit of a nightmare as I lose all data this way. Will the above solution resolve this issue?
Many Thanks
------------------------------------
Reply:
First step is to upgrade from the CTP to the production version...
If you have the scripts then you now need the data, there are 2 choices, use whats called a linked server(Books On Line can help you try this out) or you can generate text files using BCP. DTS/SSIS is not available if you are pushing the data from a 2005 server, however you could try pulling it from the 2005 to the 7 server on the 7 server, ie have DTS in 7.0 connect to 2005.
BTW in your statement above you are mixing up the tools and the products...
EM and QA come with SQL 7 and 2000, the 2000 versions work with 7 and 2000.
SSMS comes with SQL 2005, it works with 2005 and 2000.
Once you have a database in a higher version (2000 or 2005) there is no easy way to go back to an older version (7 or 2000)
------------------------------------
Time series calculation
Hi,
is this "best practice" (adding a separate dimension for time series calculation, like described here: http://blogs.msdn.com/bi_systems/articles/187639.aspx) still valid for SQL 2005? Or is there any better solution for that? I simply don't want to write one MDX per time series calculation (YTD, MTD, ...) per measure...
Thanks,
- Changed type Ed Price - MSFTMicrosoft employee Saturday, April 4, 2015 2:08 AM
Reply:
Hi Thomas,
This SQL Server Magazine article discusses the approach to Time Intelligence taken in the AS 2005 Business Intelligence Wizard:
>>
...
First, the wizard creates a new named calculation within the time-dimension table in the Data Source View (DSV). (A named calculation is a calculated column that resides purely within the DSV; no changes are written to the underlying data source.) The named calculation serves as the source for a calculated attribute hierarchy. The calculated attribute will contain a single noncalculated value (based on the named calculation) in addition to all the user-selected calculations. The wizard uses a constant as the basis for the calculation so that it applies to all rows in the dimension table. This column returns the natural values over time, both as the default value and to allow comparison to the calculated views.
Next, after creating the column, the wizard creates a dimension attribute that points to the calculated column. Because this attribute's hierarchy primarily contains calculations, we can't logically aggregate these members. To mark a hierarchy as being nonadditive, we disable the system-generated All level. The IsAggregatable property of a dimension attribute controls the existence of the All level; because we don't want to aggregate the members in the calculated hierarchy, we set it to False.
...
>>
Hopefully, you have SP1, because some Time Intelligence Wizard issues may be fixed there:
http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!379.entry
>>
| Some Time Intelligence Wizard-generated calculations don't work |
| This is an issue I came across a while ago during beta testing, but now I know it is present in the RTM version and because I know this is going to be a major cause for confusion, I thought I'd blog about it. Put simply, the MDX for certain calculations generated by the Time Intelligence Wizard in BIDS doesn't work. ... >> |
------------------------------------
OLAP Survey 6
We would very much welcome your participation in The OLAP Survey 6. This is the largest independent survey of business intelligence/OLAP users worldwide. The Survey will obtain input from a large number of users to better understand their buying decisions, the implementation cycle and the business success achieved. Both business and technical respondents are welcome. The OLAP Survey is strictly independent. While Microsoft and other vendors assist by inviting users to participate in the Survey, the vendors do not sponsor the survey, nor influence the questionnaire design or survey results. As a participant, you will not only have the opportunity to ensure your experiences are included in the analyses, but you will also receive a summary of the results from the full survey. You will also have a chance of winning one of ten $50 Amazon vouchers. Click here to complete the survey on-line: http://www.survey.com/olap6sur10.html . The German version of survey is here: http://www.survey.com/olap6survey6.html
Mosha Pasumansky (http://www.mosha.com/msolap)
Update cube with ADOMD and SSAS 2005 : CellSet value is not refreshed
Hi,
I'm trying to port an existing piece of VB6 code that works fine on SSAS2000 onto SSAS2005.
This code uses ADOMD to perform an MDX select statement by opening an ADOMD.CellSet object.
It then performs an Update Cube statement on the same data and then displays the CellSet updated value again.
Both Select and Update fonctions work fine except that the value in the cellset is not updated. When I close and reopen the connection, I can control that the cube has been correctly updated - the transaction commit works fine - the value is updated but the value in the cellset is not updated.
I enclose here a piece of sample close which illustrates.
Is there any way to get this corrected (eg by setting some connection property) or is this a (un)kown issue/limitation ?
Many thanks in advance
Rgds,
Francois
---------------------------------------------------------------------------------------------
' Form contains the folowing components:
' Label : Label1
' Button cmdLoad : performs the Select statement
' TextBox txtNew : use to type in the new value
' Button cmsSave : performs the Update cube
Private m_CellSet_Cube As ADOMD.Cellset
Private m_ConMdx As ADODB.Connection
Private m_ReportQuery As String
Private Sub cmdLoad_Click()
On Error GoTo gest_err
Label1.Caption = "Initializing"
DoEvents
m_ReportQuery = "..."
Set m_ConMdx = New Connection
m_ConMdx.ConnectionString = "Provider=MSOLAP;Data Source=spantik;"
m_ConMdx.Open
m_ConMdx.DefaultDatabase = "Analysis Services Project1" 'm_DatabaseName
Set m_CellSet_Cube = New ADOMD.Cellset
Set m_CellSet_Cube.ActiveConnection = m_ConMdx
Label1.Caption = "Executing query"
DoEvents
m_CellSet_Cube.Open m_ReportQuery, m_ConMdx
Label1.Caption = CStr(m_CellSet_Cube(0).Value)
DoEvents
Exit Sub
gest_err:
Label1.Caption = "Query Nok"
End Sub
Private Sub cmdUpdate_Click()
Dim Updatecommand As ADODB.Command
Dim cs As ADOMD.Cellset
Dim MdxString As String
Label1.Caption = "Performing update"
Set Updatecommand = New ADODB.Command
MdxString = "Update Cube ... USE_EQUAL_ALLOCATION"
Set Updatecommand.ActiveConnection = m_CellSet_Cube.ActiveConnection
Updatecommand.CommandText = MdxString
Updatecommand.ActiveConnection.BeginTrans
Updatecommand.Execute
Updatecommand.ActiveConnection.CommitTrans
Label1.Caption = "updated local value is :" &
CStr(m_CellSet_Cube(0).Value)
End Sub
- Changed type Ed Price - MSFTMicrosoft employee Saturday, April 4, 2015 2:10 AM
Reply:
Is there any chance to get an answer on this or is ADOMD.Net the one and only answer ?
Thanks & regards,
Francois
------------------------------------
Problem in deployment on SSAS 64 bit with Oracle 10g 32 bit client (as datasource)
Hi,
I installed SQL Server 2005 64 bit and have Oracle 10g 32 bit client,
I am creating a cube based on Oracle datasource by connecting Oracle 10g 32bit client.
when I build and process cube it Done successfully but when I deploye it to server then it gives following error.
Error 1 Errors in the high-level relational engine. The following exception
occurred while the managed IDbConnection interface was being used: Attempt
to load Oracle client libraries threw BadImageFormatException. This problem
will occur when running in 64 bit mode with the 32 bit Oracle client
components installed.. 0 0
Error 2 Errors in the high-level relational engine. A connection could not
be made to the data source with the DataSourceID of 'Oracle DW Call Detail',
Name of 'Oracle DW Call Detail'. 0 0
any suggestion regarding this ?
- Changed type Ed Price - MSFTMicrosoft employee Saturday, April 4, 2015 2:10 AM
Reply:
Hi Kawish,
Did you solve this prolem? I am having the same problemss as you describe here.
look forward to hearing from you..
------------------------------------
Business Object Integration with AS2005 Cubes
Hi All,
I currently have a microsoft client who wants to use Business Objects (latest version) with Analysis Services 2005 but does not know if Business Objects would be able to connect to AS2005. If you know of any information, that would help a great deal.
thank you
PS: of course, I have recommended reporting services and proclarity and the client is also looking into it.
- Changed type Ed Price - MSFTMicrosoft employee Saturday, April 4, 2015 2:11 AM
Unable to connect to Analysis Services as a client
I got SQL SERVER 2005 SP1 installed on a server and is running fine. I've installed BI studio on a client computer and I can access the database engine. On the client computer, while deloying my cube I get the following error :
the project could not be deployed on the server "server_name\instance_name" due to connectivity problems. Verify that the server is running
I've checked the project properties and in the deployment section I have the right server : "server_name\instance_name" and the right database : "database_name".
I can access the server since i've created the datasource using the sqlclient provider.
On the server all services are green (sql browser also) and all tcp ports are opened.
Perhaps it is a configuration problem of analysis services on the server !!
Thanks for an answer.
Reply:
Hi,
You mentioned that the server is started because you created the datasource using the sqlclient provider. The datasource will point to the relational SQL Server (out of which the AS2005 cubes and dimensions will be processed), while your BI project needs to be deployed to the Analysis Services server. "server_name\instance_name" needs to refer to the AS2005 server ("SQL Server Analysis Services" service), not to the relational SQL Server.
I hope this helps,
Adrian Dumitrascu.
------------------------------------
Reply:
Thanks Adrian for your answer.
Where in the BI project should I spécify to refer to Analysis Services and not to the SQL relational server?
In the project properties and in the deployment section I have the right server : "server_name\instance_name". Should we specify to point to analysis services in the datasource (in the connection string)?
Thanks
------------------------------------
Reply:
In the BI project properties (right click on project node -> Properties), in the Deployment tab there are the 'Server' and 'Database' settings. Those are for the AS2005.
The connection string for the DataSource is for the relational SQL Server.
Adrian Dumitrascu.
------------------------------------
Reply:
These are my settings :
In the project properties in the deloyment tab :
server : server_name\instance_name
database : database_name
Datasource connection string : SQL Native Client
connection test : OK
After building my cube, I get the following error while deploying it :
the project could not be deployed on the server "server_name\instance_name" due to the following connectivity problems : A connection cannot be made. Ensure that the server is running...
It seems that I can't connect to AS2005 on the server as a client. I'm trying to connect as administrator.
I've check all services and tcp/ip connections and the firewall is off on the server and I still can't solve the problem. Any ideas?
------------------------------------
Reply:
I've tried to connect to Analysis Services on the server using SQL Server Management Studio as a client, I have the following errors :
A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)
An existing connection was forcibly closed by the remote host (System)
The SQL Server 2005 is installed on the local network and is running on an XP SP2 32bits PC, and the client is a windows 2000 SP4 PC.
I wonder if the server should not be a Windows 2003 for appropriate server/client applications?
------------------------------------
Reply:
This error suggests that you don't have permissions with the user connecting from the client machine.
On the client machine, run the SQL Management Studio under a user that is administrator on the server machine (we don't need it to be a full admin on the server machine, only AS2005 admin, but we'll try this as a test).
If the connection succeeds, right click on the AS2005 server item, use 'Properties', go to the 'Security' tab and add your initial user (for which the connection failed) as an AS2005 administrator. Then you should be able to connect fine. You can also lower the rights for the user, to make it just database(s) administrator, instead of full server administrator.
Adrian Dumitrascu.
------------------------------------
Reply:
Adrian,
I found out that on the server, I cannot log on to analysis services using SQL Management Studio as administrator. The authentification, user and password are greyed. I can only log on using the windows authentification mode and with my windows user name.
When I browse for network servers, still with SQL Management Studio, I have only the database engine as net work server on both server and client.
When I browse for local servers on the server, all the servers are present :
- database engine
- analysis services
- reporting services
- integration services
In the SQL Server Configuration Manager, all services are configured as "Network Service".
How to make available the other 3 services on the network (SSAS, SSRS, SSIS).
The server's firewall is set off for tests.
Thanks Adrian.
------------------------------------
Reply:
Hi:
How I can conecting to analysis services from BI Studio with sql authtentication?
------------------------------------
Reply:
Basic authentication (with user name and password) is only supported for http connections (where the user name and password are passed to IIS for verification and impersonation).
For native connections (native connections = the regular ones, non-http, non-local cubes) only integrated authentication works. That's why the user name and password are greyed out in the connect dialog from SQL Management Studio.
Adrian Dumitrascu.
------------------------------------
Reply:
1. If the AS2005 server is on the same machine where you are opening SQL Management Studio, then login (to Windows) with an Windows local administrator account. Then open SQL Management Studio and connect to AS2005. Integrated authentication is done, so you will have AS2005 admin rights. Then right click on the AS2005 item, use 'Properties' and go to the 'Security' tab. Add your normal user name as an AS2005 admin. Later you can lower this permission to make your user just a database admin, but for the moment we'll go with the full server admin to see if this will work. Then logout from Windows and loging with your user. You should be able to connect now to AS2005. Please note that you can also run SQL Management Studio under a different user (to skip the Windows loging-logout parts).
2. If the AS2005 server is on machine A and SQL Management Studio is on different machine B, then you need to loging on machine B under an account who is admin on machine A (or run SQL Management Studio under that account).
Adrian Dumitrascu.
------------------------------------
Reply:
Hi Adrian,
I think the problem is with win XP and SQL SERVER 2005 Enterprise Edition. It seems that we cannot connect clients to a SQL SERVER having win XP as OS. We need a server OS (win2000 server SP4 or win 2003 server)
Do you confirm that??
Thanks
------------------------------------
Reply:
No, Windows XP Professional is supported. (I use it all the time to connect to AS2005) The problem is caused by something else. The AS2005 server machine and the client machine are both on the same domain ? What I suggested on the previous messages didn't work ? (with logging in on the client machine with an user who is AS2005 admin)
Can you connect to the AS2005 machine from any other machine ? Can you connect to it locally ? When connecting, please try SQL Management Studio and either one of MDX Sample App if you have it (from AS2000), Excell or the data link dialog (.udl file)
Adrian Dumitrascu
------------------------------------
Reply:
Well Adrian I got my OS changed to WIN 2000 Server SP4 and now I can connect clients using either SQL Management Studio and BI studio.
I've tried with XP to connect clients using the AS2005 admin logon, with no results. The machines are on the same domain.
Now it is working fine with WIN 2000 Server SP4. I don't unserstand, normally it should also work under an XP OS!
Thanks a lot Adrian 
------------------------------------
Reply:
Well Adrian I got my OS changed to WIN 2000 Server SP4 and now I can connect clients using either SQL Management Studio or BI studio.
I've tried with XP to connect clients using the AS2005 admin logon, with no results. The machines are on the same domain.
Now it's working fine with WIN 2000 Server SP4. I don't unserstand, normally it should also work under an XP OS!
Thanks a lot for your help Adrian
------------------------------------
ใช้msn
Reply:
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
------------------------------------
XML as Data Source for Reporting Services
XML as Data Source for Reporting Services
Reporting Services Express Edition cannot accept XML as DataSource.
XML is a great technology, and it does not look like a "big deal" for Microsoft to let developers use XML as the DataSource for the Reporting, even in Express Edition
How to convince Microsoft to revoke this limitation?
Thank you very much for attention.
Poll: Followup --> How often do you reboot your server ?
as a followup to the latest post of Louis I created a poll where you an quickly share your experiences with rebooting your server.
The poll can be reached under: http://www.sqlserver2005.de/Polls
Feel free to enter any poll wishes under the feedback site, I will consider to publish them there.
-Jens Suessmeyer
---
http://www.sqlserver2005.de
---
Reply:
If you are interested in, the results can be viewed here http://www.sqlserver2005.de/Polls/Results/Poll1.pdf.
Have fun, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
------------------------------------
Please tell me you are joking - All member multivalue parameter
Reply:
I agree 100%.
The worst part is that I cannot uninstall SP1 by itself.
------------------------------------
"SQL Everywhere" is a Sybase trademark
It's all well and good that Microsoft has finally entered the well-established mobile database market (the market that iAnywhere Solution's SQL Anywhere product current *owns* by a wide margin). However, it should be noted that "SQL Everywhere" is a Sybase trademark. For confirmation of this fact, see http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.mro12.6.mro126ig/html/mro126ig/legal.htm
For confirmation of the fact that SQL Anywhere owns the mobile database market, note that it is the database of choice for the 500,000 mobile devices that will be used in the 2010 US Census; see http://www.internetadsales.com/modules/news/article.php?storyid=7347
Breck
Reply:
The full name of the new mobile db is SQL Server Everywhere Edition, and I don't see that in the list you're linking.
Plus, wouldn't you think that a company like Microsoft has done some research before they decide on their product names?
------------------------------------
Reply:
"Plus, wouldn't you think that a company like Microsoft has done some research before they decide on their product names?"
http://seattletimes.nwsource.com/html/businesstechnology/2002397450_microvista23.html
By naming the next version of Windows "Vista," Microsoft may have stepped on the toes of another software company just down the road in Redmond.
So, no, or: When an 800-lb gorilla has got to go, he doesn't ask where's the restroom, he just goes!
Whois vista.com?
Registrant:
CommunityIQ
11241 Willows Road
Redmond, WA 98052
US
Registrar: DOMAINBANK
Domain Name: VISTA.COM
Created on: 02-NOV-94
Expires on: 01-NOV-14
Last Updated on: 27-OCT-05
Administrative, Technical Contact:
Admin, Domain ***@VISTA.COM
vista.com
11241 Willows Road
Redmond, WA 98052
US
3609208478
'She said the company filed for trademark protection of the words "Windows" and "Vista" used together. ..."The name Vista is commonly used by a variety of companies in a variety of industries," she said. "We are only using the word Vista paired with our trademark Windows so the two together - 'Windows Vista' - ...
If that's MS's stance, then it should have no problem with "Linux Windows". After all,
Windows is used by a variety of ... (yada-yada-yada).
------------------------------------
Connection latency
Reply:
If you want to persist simulation results (both utlization and latency), use the Export to Excel functionality under the File menu.
------------------------------------
Enhancement: SSIS Designer
Not quite sure if this is the right place for this comment...
While using SSIS Designer came across a DUE where the package was setup to:
- SEQ1 - Truncate staging tables
- SEQ2 - Load staging tables (sequence container had been setup to hold multiple data flows)
For some reason one data flow was run before the truncate table sequence.
Looking in the designer it appeared that the dataflow was a member of SEQ2 but looking at the Package Explorer it was outside the sequence container.
It would be good to have some visible indicator to show that a task is a member/not a member of a container
John.
Reply:
You can collapse the container thereby hiding everything that's in it. Everything not in it will still be displayed.
-Jamie
------------------------------------
Reply:
Thanks Jamie, I agree that the container could be collapsed (and the user could also check the Package Explorer... ) - just thinking that the simplest way to avoid this kind of DUE is to have some visible indicator.
------------------------------------
Reply:
Well, implicitly every task/container is a child of another container (http://blogs.conchango.com/jamiethomson/archive/2004/12/13/445.aspx). So, at what point would you draw the line and say "Well its a child of containerA but not containerB, so let's indicate that". I fear its a bit unworkable.
-Jamie
------------------------------------
How to address hierarchies with different # of members in lowest level
I have a cube with 2 hierarchies for Geography dimension
1) Standard Hierarchy: Levels (ALL-> Group -> Division -> Region -> Country)
2) Custom Hierarchy: Levels (ALL -> Country)
The lowest level is the same in both hierarchies (COUNTRY), but I have different # of Countries for each hierarchy.
I will have 3 possibilities for the Country Level:
1) Countries that are in both hierarchies
2) Countries that should be only in Standard Hierarchy
3) Countries that should be only in Custom Hierarchy
Right now, Analysis Services 2005 is creating a "Unknown" parent for the countries that don't have parent and adding them up to the "ALL" level.
I am aware that I could hide the "Unknown" members, but the level "ALL" still aggregates the data.
For example:
Standard Hier Custom Hier
Ctry A 100 100
Ctry B 100 100
Ctry C 50
Ctry D 100
Ctry E 100
ALL LEVEL (Desired Resuts) 250 400
ALL LEVEL (Current Results) 450 450
Right now I'm getting the same value for both hierarchies (ALL Level), and i would like to have different totals, depending on the hierarchy.
Any help would be appreciated.
- Changed type Ed Price - MSFTMicrosoft employee Friday, April 3, 2015 1:41 AM
No comments:
Post a Comment