InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 11:51:35 GMT20http://www.sqlservercentral.com/Forums/Topic1305090-17-1.aspxHi All,I am trying to create a new OLAP database using the following tables in my OLTP database.[b]tblIndustry[/b]IndustryID Int Primary KeyName Varchar(100) (Ex: Accountancy or Sales etc)[b]tblRole[/b]RoleID Int Primary KeyName Varchar(100) (Ex: Developer or DBA etc)[b]tblVacancy[/b]VacancyID Int Primary KeyTitle Varchar(100) ----------------[b]tblVacancyIndustry[/b]VacancyID Int IndustryID Int [b]tblVacancyRole[/b]VacancyID Int RoleID Int As you can see from above, each vacancy has multiple industries and multiple roles and this data is stored in tblVacancyIndustry & tblVacancyRole.My goal is to quickly calculate the vacancy counts by industry & by role. In my OLAP database I have created the dimension tables DimIndustry & DimRole. While creating the fact table FactVacancy I got stuck. My question is..........Should I create one fact table which is a CROSS PRODUCT of tblVacancy, tblVacancyIndustry & tblVacancyRole? If this is not correct, what is the correct way to design the fact table.Thanks Wed, 23 May 2012 09:56:18 GMTu2kotahttp://www.sqlservercentral.com/Forums/Topic1305627-17-1.aspxHi. I'm wondering if someone can help me with the following issue.I have 2 data sources DS1 and DS2 which are both databases on the same server.DS1 contains my measure group MG1DS2 contains my 2 dimensions Dim1 and Dim2Now when I set the realtionship on MG1 to Dim1 everything is fine.But when I set the relationship between MG1 and Dim2 (referenced relationship through Dim 1) then I get errors. Namely that it doesn't recognise Dim1 [b](Invalid object name 'Dim1'.; 42S02).[/b]I'm sure this has something to do with the measure group being on another database/datasource because if I move that measure group to the same database as the dimensions (DS2) then everything works fine. *Note that I can't have this solution on the live system due to other design constraints.Can someone please help?ThanksThu, 24 May 2012 03:21:44 GMTBunxhttp://www.sqlservercentral.com/Forums/Topic1305696-17-1.aspxHi Would anyone have any links or recommendations in specifying the hardware requirements for a standalone SSAS server. This server will be part of a BI solution where the SSRS and warehouse database are hosted on separate servers. Was wondering if anyone had a set of baselines to follow with regards to the types and size of hard disk which should be used and how much memory should be allocated etc Any help would be greatly appreciated ThanksThu, 24 May 2012 04:58:02 GMTthomasbrennan10http://www.sqlservercentral.com/Forums/Topic1305000-17-1.aspxI need some help with this script:--------------------------------# Load AMO assembly.[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null;[Microsoft.AnalysisServices.Server] $srv = New-Object Microsoft.AnalysisServices.Server;$Servers = Get-SqlData 'Server\Instance' Database "SELECT name FROM [table]";foreach ($Server In $Servers){ # Connect to the server. $srv.Connect($Server); [string]$DataDir = $srv.ServerProperties.Item("DataDir").Value + "\"; Write-Output "Server '$Server' has DataDir Path name '$DataDir'"; $srv.Disconnect();}Running it procduces this error message:---------------------------------------Exception calling "Connect" with "1" argument(s): "A connection cannot be made. Ensure that the server is running."At C:\Users\user\Desktop\Get-SSASDataDir.ps1:26 char:17+ $srv.Connect <<<< ($Server); + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodExceptionException calling "get_Item" with "1" argument(s): "The 'ServerProperty' with 'Name' = 'DataDir' doesn't exist in the collection."At C:\Users\user\Desktop\Get-SSASDataDir.ps1:29 char:50+ [string]$DataDir = $srv.ServerProperties.Item <<<< ("DataDir").Value + "\"; + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodExceptionThanks-------Wed, 23 May 2012 08:28:55 GMTkwe477http://www.sqlservercentral.com/Forums/Topic1304787-17-1.aspxHi ,The performance of pivot table in the excel sheet is extremely slow and some times it freezes.The cube size is around 60 gigs and iam using excel 2010.What might be cause to slow down the performance of the pivot table.Please help me.Thanks in advanceRaviWed, 23 May 2012 02:51:06 GMTmirravihttp://www.sqlservercentral.com/Forums/Topic1304009-17-1.aspxHi All,Sql Server 2005 SP2.I have a dimension which when I process it says that the number of rows read is different from those in the underlying table that it is reading from. As an example the table it reads from has 1427308 rows in it yet when the dimension is processed it can say it has read 1179649 rows. If I run the sql shown during processing it returns the correct number of rows, 1427308. If I process the dimension again it may say it has read 1179649 or 1427308 or 1674967 rows.I have ran profiler against the database during the dimension processing and it always shows a rowcount being returned of 1427308.If the rows read is less than the number of rows in the table then I am actually missing data from the dimension and cube it feeds.Now I know the underlying data is not changing during these process runs, so has anyone seen activity like this, or could suggest how to troubleshoot the issue.ThanksRonnieTue, 22 May 2012 04:01:47 GMTRonnie_Doggarthttp://www.sqlservercentral.com/Forums/Topic1303865-17-1.aspxHi,I have a SSAS 2005 database where 2 cubes exist now I want to move one cube from source databse to another database on same server or another server. Remember I have to move only one cube not whole database.Thaks in advance.Mon, 21 May 2012 23:33:37 GMTbeejug1983http://www.sqlservercentral.com/Forums/Topic1303930-17-1.aspxIt is very annoying that my OLAP system gets the following error occasionally during the execution of my ETL process :Description: File system error: The background thread running lazy writer encountered an I/O error. Physical file: \\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\CK_034_PRD.0.db\DIM VIP Card.0.dim\201.Attr0.khstore.Logical file: . End Error Error: 2012-05-21 03:20:16.66 Code: 0xC1020004 Description: File system error: The following error occurred while writing to the file 'LazyWriter Stream': The process cannot access the file because another process has locked a portion of the file. . End Error Error: 2012-05-21 Then I would re-execute my ETL manually. It completed successfully but I don't know when it will appear again.Microsoft SQL Server 2005 Analysis Services Performance Guide says :"With large cubes, using a 64-bit version of the Microsoft Windows Server 2003 family increases the amount of memory that the operating system can use to cache Analysis Services requests. With sufficient memory, much of the cube can be stored in the file system cache. "Does the problem relate to the SQL Server's Minimum server memory ?Should I increase it ?Or there is another reasons or parameter caused this error ?Tue, 22 May 2012 01:46:40 GMTonlohttp://www.sqlservercentral.com/Forums/Topic1303922-17-1.aspxHey all,I am quite new to SSAS (the only part of SQL i have never really used - thought i really should!)I am slowly getting to grips with it. I have made a simple SSAS package in 2008. All seems to make at least some vague sense.Until i got to mining structures. I have built a structure, and it works (time prediction).The question is - what do i do with this now i have got it? I was expecting to be able to find it somewhere within reporting services like the KPI or the cube elements.What do i do next?TIADanTue, 22 May 2012 01:29:11 GMTdanielfountainhttp://www.sqlservercentral.com/Forums/Topic1302283-17-1.aspxHi, I'm an SSAS noob trying to work my way through a book, and in the lesson about deploying a sample cube, it says to open "Adventure Works DW 2008.dwproj" in the folder "C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks 2008 Analysis Services Project\standard", but my problem is that once I get to the Samples folder, it's empty except for an htm file linking me to the codeplex website.I don't know where to find the files I need on codeplex - can anyone help me out? I've already set up both the AdventureWorks2008R2 and the AdventureWorksDW2008R2 databases. Thanks!Thu, 17 May 2012 23:26:44 GMTtengen1010http://www.sqlservercentral.com/Forums/Topic1302369-17-1.aspxHello I have been stuck on this for the last 3 days I am beginning to think that Analysis services is incredibly flaky and not really useable. Very worried about this situation.This all happened after a LazyWriter error. There wasnt enough space in the db. Analysis Services on the server has since been restarted by the dba team. This usually works when there is an issue.So now to the problem.The dimensions process fineI have reprocessed my cubes again. The process is succesful. it says there are 30000+ records read. I have checked the source data and there are measures in there. Everything is fineI then go to the browser and drag the measures in. Every one empty.I then open up the cubes in SSMS and go into Partition Properties - Estimated Rows 0So no matter what I do I cant add data into the cubes. I then decided to create a brand new cube using the same data but exactly the same thing happens. This new cube doesnt contain data either, even though its been successfully processed. Process FullIm now completely out of ideas. My users have been told they cant access the reports for the time being but I cant give them an ETA because I genuinely dont know what to do.I would appreciate any adviceThanksDebbie Fri, 18 May 2012 03:57:49 GMTDebbie Edwardshttp://www.sqlservercentral.com/Forums/Topic1299267-17-1.aspxI have a cube database and I am trying to process it via SSIS package but getting error :"Internal error: The operation terminated unsuccessfully.". Looks like time out, if so then how to get and increase timeout option without using ssms. Would like to achive this by script.same replica of cube is working on other systems with same configuration. Any idea?Sun, 13 May 2012 10:15:24 GMTscottichrosaviakosmoshttp://www.sqlservercentral.com/Forums/Topic1299904-17-1.aspxHello All,I am new in AS and this task just some how got to me and I am totally stuck. Hoping some one can help me out it here.I have two AS databases with the same name but on different servers and need to copy dimensions A from server A to dimension A on server B.name wise the dimensions are same but residing on different server. what is the best way to do that.Thanks in advance.BobbyMon, 14 May 2012 15:12:16 GMTqur7http://www.sqlservercentral.com/Forums/Topic1300366-17-1.aspxHi All,We are trying to migrate a SQL 2005 DW database and its related cubes to SQL 2008 environment. It turns out that there also a quite number MDX queries written against 2005 cube.We haven't made any changes to the cube but after the migration mdx queries stopped working. I have had a quick look at the queries that 2008 generates and I can see some differences. Please see the example below:2005SELECT { { { [Time Standard].[Acc Prd].&[201112] } * { [SCC].[SCC].&[CAD], [SCC].[SCC].&[EUR], [SCC].[SCC].&[GBP], [SCC].[SCC].&[JPY], [SCC].[SCC].&[USD], [<#SharedMember.RESERVED.PWH.Conv(GBP) 2011 Quarter 4 (USD1 55 CAD1 58 EUR1 20 JPY 119 57#>] } * { [YAC].[YAC].&[1993], [YAC].[YAC].&[1994], [YAC].[YAC].&[1995], [YAC].[YAC].&[1996], [YAC].[YAC].&[1997], [YAC].[YAC].&[1998], [YAC].[YAC].&[1999], [YAC].[YAC].&[2000], [YAC].[YAC].&[2001], [YAC].[YAC].&[2002], [YAC].[YAC].&[2003], [YAC].[YAC].&[2004], [YAC].[YAC].&[2005], [YAC].[YAC].&[2006], [YAC].[YAC].&[2007], [YAC].[YAC].&[2008], [YAC].[YAC].&[2009], [YAC].[YAC].&[2010], [YAC].[YAC].&[2011] } } } ON COLUMNS ,{ { { [Syndicate].[Syndicate].&[0382], [Syndicate].[Syndicate].&[1441], [Syndicate].[Syndicate].&[1481], [Syndicate].[Syndicate].&[3820] } * { [Measures].[EPIGrCalc], [Measures].[EPIGrSgnd], [Measures].[EPIGrWtn], [Measures].[EPIMktGr], [Measures].[EPINetCalc], [Measures].[EPINetSgnd], [Measures].[EPINetWtn], [Measures].[PdCm], [Measures].[PdPm], [Measures].[PdRiCm], [Measures].[PdRiPm], [Measures].[OsCm], [Measures].[OsCmAdjd], [Measures].[OsCmNet], [Measures].[OsCmNetAdjd], [Measures].[AccrCm], [Measures].[AccrPm] } } } ON ROWS FROM [vALL] WHERE ( [AdviceID].[AdviceID].[All AdviceID], [Advice Status].[Advice Status].[All Advice Status], [UnitPsu].[All UnitPsu], [PeriodType Standard].[Cum] )CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE2008SELECT { { { [b][Time Standard].[/b] [Time Standard].[AccPrd].&[201112] } * { [SCC].[SCC].&[CAD], [SCC].[SCC].&[EUR], [SCC].[SCC].&[GBP], [SCC].[SCC].&[JPY], [SCC].[SCC].&[USD]} * { [YAC].[YAC].&[1993], [YAC].[YAC].&[1994], [YAC].[YAC].&[1995], [YAC].[YAC].&[1996], [YAC].[YAC].&[1997], [YAC].[YAC].&[1998], [YAC].[YAC].&[1999], [YAC].[YAC].&[2000], [YAC].[YAC].&[2001], [YAC].[YAC].&[2002], [YAC].[YAC].&[2003], [YAC].[YAC].&[2004], [YAC].[YAC].&[2005], [YAC].[YAC].&[2006], [YAC].[YAC].&[2007], [YAC].[YAC].&[2008], [YAC].[YAC].&[2009], [YAC].[YAC].&[2010], [YAC].[YAC].&[2011] } } } ON COLUMNS ,{ { { [b][Syndicate][/b].[ Syndicate].[Syndicate].&[0382], [b][Syndicate][/b]. [Syndicate].[Syndicate].&[1441],[b][Syndicate][/b]. [Syndicate].[Syndicate].&[1481], [b][Syndicate][/b]. [Syndicate].[Syndicate].&[3820] } * { [Measures].[EPIGrCalc], [Measures].[EPIGrSgnd], [Measures].[EPIGrWtn], [Measures].[EPIMktGr], [Measures].[EPINetCalc], [Measures].[EPINetSgnd], [Measures].[EPINetWtn], [Measures].[PdCm], [Measures].[PdPm], [Measures].[PdRiCm], [Measures].[PdRiPm], [Measures].[OsCm], [Measures].[OsCmAdjd], [Measures].[OsCmNet], [Measures].[OsCmNetAdjd], [Measures].[AccrCm], [Measures].[AccrPm] } } } ON ROWS FROM [vALL] WHERE ( [Adv Id].[AdviceID].[All AdviceID],[Advice Status].[Advice Status].[All Advice Status],[b][Unit Psu][/b]. [UnitPsu].[All UnitPsu], [b] [PeriodType Standard][/b].[ PeriodType Standard].[Cum] )CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE As you can see above, a new hierarchy level is added in 2008 to produce the output. The question is that whether there is a way to update these queries, without recreating each query. Or third party tool perhaps?Appreciate if you have any comment on this.RegardsEnisTue, 15 May 2012 09:31:33 GMTertemenhttp://www.sqlservercentral.com/Forums/Topic1300103-17-1.aspxHi,We have a cube that processes fine, even though we have some data that contains non printable characters (non synchronous idle).However, when we use the column in an MDX query, it dies.Is there a way to get SSAS to ignore the presence of these characters? Cleaning them out of the data is very much a last resort as it will affect things further down the line.Any ideas gratefully received.Thanks,Mark.Tue, 15 May 2012 03:17:56 GMTevolution_markhttp://www.sqlservercentral.com/Forums/Topic1289107-17-1.aspxI have the following code that I am using on the Adventureworks 2008 R2 database but it just hangs when using the Customer baseWITH MEMBER [measures].[total] AS [Measures].[Internet Sales Amount] + [Measures].[Internet Tax Amount] MEMBER [Measures].[Avg Total] AS IIF(ISEMPTY([measures].[total]),null,Avg ( [Customer].[Customer].[Customer].Members ,[measures].[total] )) MEMBER [measures].[standard deviation] AS IIF(ISEMPTY([measures].[total],null,StdDev ( { [Customer].[Customer].[Customer].Members } ,[measures].[total] )) MEMBER [Measures].[StatusValue] AS IIF(ISEMPTY([measures].[total]),null,CASE WHEN [Measures].[Total] > [Measures].[Avg Total] + [measures].[standard deviation] THEN 'Good' WHEN [Measures].[Total] < [Measures].[Avg Total] - [measures].[standard deviation] THEN 'Low' ELSE 'Normal' END) SELECT NON EMPTY { ,[measures].[total] ,[Measures].[Avg Total] ,[measures].[standard deviation] ,[Measures].[StatusValue] } ON COLUMNS ,NON EMPTY [Customer].[Customer].[Customer].Members ON ROWSFROM [Adventure Works];Is there a way of speeding this up?The calulcations are correct but it just doesn't like being applied to across the whole customer base which practically makes it not useableAny help would be really helpfulI have read this link but I am not sure of how I can apply it to this queryhttp://sqlblog.com/blogs/mosha/archive/2008/03/28/take-advantage-of-fe-caching-to-optimize-mdx-performance.aspxTue, 24 Apr 2012 09:39:22 GMTaivoryukhttp://www.sqlservercentral.com/Forums/Topic1297519-17-1.aspxHi All, I have a issue, where i can able to connect SSAS from Server. But if iam trying to connect from my local its failing with below error. SQL Browser is up and running. Can any one please let me know the fix.SQL Server Version: 2005, Entp edition.Error:=================================== Cannot connect to XXXX\olap. =================================== The connection timed out. (Microsoft.AnalysisServices.AdomdClient) ------------------------------Program Location: at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo) at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession) at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetInstancePort(ConnectionInfo connectionInfo) at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo) at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo) at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla() at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open() at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server) at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()Thanks,JaiWed, 09 May 2012 18:39:59 GMTjai_422http://www.sqlservercentral.com/Forums/Topic1298473-17-1.aspxHi All,I new on SSAS, Is this possible to give a user to access only cube? If yes please explain how can I achieve this. I am using SSAS 2005.Thanks in advance.Fri, 11 May 2012 05:25:38 GMTbeejug1983http://www.sqlservercentral.com/Forums/Topic1299410-17-1.aspxhi allusing adventure works cube i want to find how many distinct orders share 2 (the sales reason key is selected by the user and can be from 2 reasons to 4) sales reasonfor exampleSO65048, SO68906 share sales reason 1 and 10SO53825, SO73496 share sales reason 1, 2 and 10thank youMon, 14 May 2012 02:38:14 GMTshemreaderhttp://www.sqlservercentral.com/Forums/Topic1270778-17-1.aspxHi,I've built a cube which seems to function perfectly well except for one issue. When my Customer dimension is used with the Time dimension, everything slows to a crawl. It can take upwards of 10 minutes to display the data in the Cube Browser. There are only 1087 rows in the DimTime table and under 400,000 rows in the DimCustomer table. Using either of the two dimensions separately with any of the remaining dimensions works perfectly. It is only when both these dimensions are used in the Cube Browser at the same time that it turns to mud.The Atrribute Primary Key for the DimTime table is a standard DateTime field. The Attribute Primary Key for the DimClient table is an Integer (being a Surrogate Key).Does anyone have any suggestions as to why the Cube Browser slows down so much when these dimensions are used together and/or how to resolve the issue please?Thank you.KevinThu, 22 Mar 2012 06:27:50 GMTdomcicconehttp://www.sqlservercentral.com/Forums/Topic1297247-17-1.aspxI know this is probably a simple issue, but:I have a column of Serial Numbers (Nvarchar(255)) that when I copy the data and dump it into Excel, it gets converted to Scientific notation. The S/N's have (mostly) 2 leading 0's which are being dropped after I've manually converted the field to text, or done something like use the UPPER function to trick Excel.any ideas how to prevent this from happening, short of changing the data type on the column?ThanksCrustyWed, 09 May 2012 10:12:15 GMTCptCrusty1http://www.sqlservercentral.com/Forums/Topic1291348-17-1.aspxHello everyone,Given you already have a survey data model in place, with an SSAS cube in place, is it possible to create analysis such, for example, who are the persons who answered Yes in question 1, and answered No in question 5? Is it possible to create this type of analysis in SSAS?Thanks in advance!JonFri, 27 Apr 2012 03:12:33 GMTJonathan Malliahttp://www.sqlservercentral.com/Forums/Topic1296324-17-1.aspx[code="other"]CREATE MEMBER CURRENTCUBE.[Measures].[Areal] AS([Measures].[Areal Square]) <-- Current squares areal - sum([Dim Square Objekt].[Square].[All], ([Measures].[Areal Square]) / [Measures].[Square - Count]) <-- Average Of all Squares , VISIBLE = 1 , DISPLAY_FOLDER = 'x'; [/code]ex[Areal Square] = 100;[Measures].[Square - Count] = 5My result will be something like Square 1 -20 2 -20 3 180 4 -20 5 -20totals 180But i do want the sum of all squares so the result should be 100,I've tried for 2 days now but i cant get it to work someone know what im doing wrong?Tue, 08 May 2012 03:44:39 GMTtony 9186http://www.sqlservercentral.com/Forums/Topic1294251-17-1.aspxHi everyone!I've been fighting with SSAS scope and MDX for some days...I have a cube that takes data from 2 fact tables.I need something very simple:If date > '01/06/2011', take measure from fact table 1If it is not, take measure from fact table 2.As you can see, so simple is the problem, but i haven't could do it in MDX. I've tried the SCOPE statement:[code="sql"]scope({NULL:[Fecha].[Fecha].[Día].[01/06/2011]},[Measures].[Cantidad Ventas]); [Measures].[Cantidad Ventas] = -1;end scope;[/code]But i get an error "arbitrary shapes are not allowed in this context"...I've tried the IIF sentence, but neither i could.Someone knows how to do this? ... or another way to do? .. or a way to change the 'context'...?Thanks in advance.Wed, 02 May 2012 16:17:23 GMTchachansinhttp://www.sqlservercentral.com/Forums/Topic1291028-17-1.aspxA new thing i came to know about SSAS. I have an Analytical server database with many cubes.I have made some changes to the roles and now i want to deploy it to some other server which i don't have access.I made some role changes in SSMS and then i imported it in BIDS and built. Then I extracted script from deployment wizard.Now when i am running the script on other servers the roles are not deploying properly.Example. I set role country with allowing users of US to access US states. its fine for the server from where i was extrating script and working fine but when deploying it to other servers its showing other countries also in accessrights. I thought scripts are best option but now what.?Is that some id or what i don't know. Any comments.?Thu, 26 Apr 2012 12:43:19 GMTscottichrosaviakosmoshttp://www.sqlservercentral.com/Forums/Topic1289826-17-1.aspxHow can I get a column SubTotal and GrandTotal instead of line SubTotal and GrandTotal on Calculated Member formulae fields?eg.SO SalesOrder_Line DiscountMerchandise0001 4 205.38 5 -24.63 6 400.00 Total 107.54 (This should be 580.75) but a calculated field with diff disc on each line)Cal Mem:[Measures].[Disc Value]+[Measures].[LineDiscValue1]+[Measures].[LineDiscValue2]+[Measures].[LineDiscValue3]Any suggestions?Wed, 25 Apr 2012 07:08:02 GMTsue.scheepershttp://www.sqlservercentral.com/Forums/Topic1287905-17-1.aspxHi Friends,may i know what is documentation? what are the uses of it?pls give me sample of document for my clarifications?friends i need know this, pls help me in this.thanks in advanceNihaSun, 22 Apr 2012 23:31:00 GMTniha.736http://www.sqlservercentral.com/Forums/Topic1288899-17-1.aspxHi,I'm experiencing something bizar (but explaineable) in Excel 2010.I've got a Measure 'Total Sales Amount' and a calculated measure 'Totals Sales Amount -1'If I filter my fact table with a PivotTable filter ( added Date Hierarchy to Report Filter of Pivot ) to only first 3 months of 2010, the results are correct.I have Year/Month in my columns for the PivotTable, nothing in the rowsI have a total of 14mil for 'Total Sales Amount' and 12mil for 'Total Sales Amount -1''Total Sales Amount -1' corresponds correctly with the same period of last year.Then I change my Report Filter to the full year of 2012 and I have 61mil 'Total Sales Amount' and 51mil 'Total Sales Amount -1'These results are also correct.Now I add an excel Date filter on the month column, to take only the first 3 months.With the month's expanded I once again have the correct numbers and exactly the same as using the Report Filter instead of the Excel date filter.My total for the year 2010 for 'Total Sales Amount' is 14mil, which is correct. But my total for year 2010 for 'Totals Sales Amount -1' is 51mil instead of 12mil.So if you use an excel filter and not the report filter of the PivotTable itself, your measures calculate correctly (also totals correctly).But any calculated measures do not total correctly as if he makes the sum of them by adding all the rows/columns in excel including the invisible ones (set invisible by the excel filter).Is this normal behavior or should I report this to Microsoft as a bug?Regards,Sven PeetersTue, 24 Apr 2012 07:13:14 GMTspeetershttp://www.sqlservercentral.com/Forums/Topic1288725-17-1.aspxHi. I have a problem setting dimension security for a particular client through Analysis Services 2000. I have restricted users to particular dimension members. Tested the role in Analysis Manager and has the correct behaviour. But when the users access the cube through excel they can see all members. Only administrator user is part of the OLAP administrators group. However, the users are all local administrators on their machines. Does this supersede the roles set in Analysis Services?Tue, 24 Apr 2012 02:41:14 GMTsean graham-463717http://www.sqlservercentral.com/Forums/Topic1287669-17-1.aspxI have 5 cubes, and hierachy defined for all cubes. for example:geography database with 5 continents as cubes and contries as dimensions.Now when i am doing security restrictions on my dimension ex: In USAdimension if i want only to give access totexas region then i should be able to see only texas cities. But i cansee all the states under USA even after selecting only Texas region under Dimension data tab inside ROlessection in SSMS.I have tried security at database ,cube level as well as dimensionlevel.But still not working.is that because of some wrong design of cubes or something related todatabase design.?I am not able to undersand that except roles everything in my cubes ordatawarehouse is working fine without anddefect in data.Any suggesions....Sat, 21 Apr 2012 13:50:02 GMTscottichrosaviakosmoshttp://www.sqlservercentral.com/Forums/Topic1288052-17-1.aspxHi there.I recently setup analysis services to enable the query log for optimisation and it seems to be doing this fine (by checking the 'OlapQueryLog' table and there's plenty of data going in). However when I go and try to design an aggregation via the wizard it tells me that there are no queries for the measure groups I'm trying to do this for!Is there another setting that defines where SSAS should be looking for this table containing the queries? Or should SSAS know this from the properties I initially used to enable the logging (which I'd expect it to do)?Anyone had trouble with this before?Thanks.Mon, 23 Apr 2012 04:54:18 GMTBunxhttp://www.sqlservercentral.com/Forums/Topic1287654-17-1.aspxhi alli have following dimensions DimDate -> DateKey, Minute, Hour, Day, Week, Month, Year --> Example ("2011-01-01 00:15:00", 15, 0, 1, 1, 2011)DimXYZ -> ID, Name --> Example (222, "Test")my Fact-Table looks like thisIDXYZ, Date, VALUE --> Example (222, "2011-01-01 00:15:00", 333.45)i would like to have a NamedSet, which shows me the Top 5 Values from a Year for one specified DimXYZ-Id (i have to set the Id as a Filter). How can i do that ?following NamedSet[quote]TopCount([Dim Date].[Year].[Year].Members,5,([Measures].[VALUE]))[/quote]doesn't work. It shows me more then 5 Values.Sat, 21 Apr 2012 10:56:16 GMTphil_bhttp://www.sqlservercentral.com/Forums/Topic1287151-17-1.aspxAll,I have default values setup in a sql server table per user. These values ARE in MDX format. In SSRS, I have a parameter @Param1 that lists the values from the table for the user to utilize and then it calls a SSAS report within SSRS.An example of a value is:[Dim Time].[Monthly Time Hierarchy].[Month].&[201201]When the user selects this value the report runs and returns correctly. However, if the user had saved multiple values then the parameter would be passing this value:[Dim Time].[Monthly Time Hierarchy].[Month].&[201201];[Dim Time].[Monthly Time Hierarchy].[Month].&[201202]this fails when trying to pass to SSAS report. It gives me an error An MDX Expression was used while a full statement was specified. If I change it to be comma separated I then get a syntax error on the comma.I have been racking my brain on this, any help would be greatly appreciated.Fri, 20 Apr 2012 07:56:03 GMTmk111221http://www.sqlservercentral.com/Forums/Topic1287141-17-1.aspxI keep having the same problems in Analysis services even though Im sure it should be OK as Ive put in the groundwork.I have the following[code="other"]Local Authority CodeLocal Authority DescriptionSchool CodeSchool Name[/code]And this is also a hierarchy. I have also set this up in Attribute relationships to be…Warehouse Key > School Name> School Code> Local Authority Description > Local Authority Code(I had originally set the Names as Name for the codes and set the codes in not show but the user likes having everything separate)School Codes can be the same in different Authorities ,so you could have[code="other"]111 112Derbyshire Derby1000 1000School Name A School Name B[/code]I have set the Key column for School Name as Local Authority Code, School Code and School Name. School Name is in a hierarchy as seen above but it’s also visible alone so the user can quickly filter on just the school Name. The filter shows a distinct list of school names.School Code has a key column of Local Authority Code and School Code. Again its in the hierarchy but the user wants to be able to just go through the list of codes. Unlike School name through School Code drop down shows duplicates.For example in the filters drop down list3311331133113311[code="other"]Looking at the key columns in SQL ShowsLocal Authority Code School Code331 3311666 3311131 3311111 3311[/code]So it is correct. There are 4 unique items for it, but Usually setting the key to Local Authority Code and School Code ensures its uniqueness and therefore only gives unique values in the drop down list when not using the value from the hierarchy.Basically…… Help???? Where and I going wrong? Any help would be greatly appreciated. DebbieFri, 20 Apr 2012 07:46:19 GMTDebbie Edwardshttp://www.sqlservercentral.com/Forums/Topic1286995-17-1.aspxHi Guys,AS is not my strong suite so bear with me if I am talking nonsense.If I have a cube tht was originally built, processed and deployed where a field was a VARCHAR 50 and that source field gets changed, either to varchar 20 or varchar 100, what will the result be? will it fail on either oor both when next being processed?thanksIanFri, 20 Apr 2012 04:28:51 GMTIan C0ckcrofthttp://www.sqlservercentral.com/Forums/Topic1183489-17-1.aspxHi all, I'm new to MDX, I want to create a query to get on Rows every end of each month.The basic query on AW gets all days,[code="plain"]SELECTNON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,NON EMPTY { [Date].[Calendar].[Date] } ON ROWSFROM [Adventure Works][/code]But I want to get only month's ends:[font="Courier New"].................... Sales AmountJuly 31, 2005....... 15,012.18 $August 31, 2005..... 20,859.78 $September 30, 2005.. 35,782.70 $October 31, 2005.... 6,749.98 $November 30, 2005... 18,590.45 $December 31, 2005... 22,168.72 $[/font]Thanks in advanceTitoThu, 29 Sep 2011 17:51:42 GMTTito Luyo Muratahttp://www.sqlservercentral.com/Forums/Topic1284811-17-1.aspxI have two branches in my Fact Table ,Booking Branch and Contracting Branch, I want to see Amount on the three basis1. When both branches are same , 2. When Booking branch is own and contracting is other 3. When Contracting is own and Booking is other For this Should I use multiple Fact table or Single Fact Table and handle with MDX.Tue, 17 Apr 2012 04:32:25 GMTsachdeva.purnimahttp://www.sqlservercentral.com/Forums/Topic1285873-17-1.aspxThe cube developed is for an origanization that manages and conducts huge projects.All project calculations are based on the life of a project, in other words beginning of time to date.In other words:Revenue Recognition to Date = Actual Cost to Date / Budget Cost to Date * Budget Revenue To DateRevenue Recognition for Current Period = ([Time].[Year - Month].CurrentMember,[Measures].[Revenue Recognition to Date])-([Year - Month].PrevMember,[Measures].[Revenue Recognition to Date])This gives current period accurately, but when a user selects inidividual periods, say January 2012, March 2012, April 2012, the sum (Grand Total) of the report does not properly add up. It still reflects the total as the "To Date" value.Any ideas how this behaviour can be changed?Wed, 18 Apr 2012 11:32:48 GMTbattery_acid_hhttp://www.sqlservercentral.com/Forums/Topic1032175-17-1.aspxI am new bie trying to learn SSAS. I have these errors in trying to process my cube after it has shown to have deployed successfully. I need help. 1. OLE DB error: OLE DB or ODBC error: login failed for user 'NT AUTHORITY\NETWORK SERVICE.; 28000; cannot open database "AdventureWorksDW2008R2" requested by the login. The login failed.2. Errors in the high-level relational engine. A connection could not be made to the Data source with the Data source ID of 'AdventureWorksDW2008R2', Name of 'AdventureWorksDW2008R2'.3. Errors in the OLAP storage engine: An error occured while the dimension with the ID of 'Dim Date', Name of 'Dim Date' was being processed.4.Errors in the OLAP storage engine: An error occured while the 'Date key' attribute of the 'Dim Date' dimension from the 'SSAS Project' database was being processed.5. Internal error: The operation terminated unsuccessfully.6. Server: The operation has been cancelled.Wed, 08 Dec 2010 14:40:29 GMTeobiki10http://www.sqlservercentral.com/Forums/Topic1284216-17-1.aspxHi All , I have created DimDate dimension and able to setup Calendar Hierarchy. Now to the Calendar Hierarchy if i add week of month and week of year to Calendar Hierarchy this will not give correct result. Is my approach in setting the week Hierarchy is Correct ?. Please help me i am attaching images for references. Let me know any other information is required here !~VineetMon, 16 Apr 2012 09:44:44 GMTvineet_dubey1975