how to excluded the intersection of 2 dimensions ?

SQL Server Analysis Services

Index ‹ SQL Server

- Previous
  - 1
    - Error Codes? My data flow component is throwing an error and the only help I get is the following: error code: -1071607694 error column: 257 What in the world does this mean Can it get more cryptic than this Tag: how to excluded the intersection of 2 dimensions ?
  - 2
    - Does SQL 2008 still support DB-LIB? (Umachandar Jayachandran - MS@discussions.microsoft.com) writes: > Yes, SQL Server 2008 will still support connections from DB-LIB. Also, > if you are accessing new data types / features from DBLIB applications > you may get errors or undefined behavior. > > See the deprecated database engine features list in BOL more details. To say it more directly: the version of SQL Server following SQL 2008 will *not* accept connections from DB-Library. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Tag: how to excluded the intersection of 2 dimensions ?
  - 3
    - Global Groups, Schema and MS Acess Hi, We have a MS Access .adp app which is having some security problems. We have a windows global group which is a user - which is added to a role. The developers are having problems with unqualified sql statements trying to find user.table instead of dbo.table. It seems we are not able to specify a default schema for the global 'user' - the option is greyed out and a sql statement fails. MS says: Default Schemas A default schema is used to resolve the names of securables that are referred to without their fully qualified names. In SQL Server 2000, name resolution first checks the schema owned by the calling database user, followed by the schema owned by dbo. In SQL Server 2005, each user has a default schema. The default schema can be set and changed by using the DEFAULT_SCHEMA option of CREATE USER or ALTER USER. If DEFAULT_SCHEMA is left undefined, the database user will have dbo as its default schema. Tag: how to excluded the intersection of 2 dimensions ?
  - 4
    - SQL Express Evaluation Expired - Help ! hi, bbain007 wrote: Hi All> I need to save the work I did during my SL Server 2005 Express evaluation which has now expired. I am afraid, very afraid of losing the databases if I uninstall the eval version and upgrade to the newest Express version. Am I ok, or am i toast lease clue me in- thanks! AFAIK, SQLExpress does not have an "expiration time"... SQL Server Trial edition does... so... as long as your database(s)'s size is not bigger than 4gb (each), stop the SQL Server service, copy the mdf + ldf files in another folder.. uninstall the SQL Server Trial Edition.. install SQLExpress and it's tool(s) (SQL Server Management Studio Express).. copy the old mdf + ldf files to the SQLExpress data folder.. run SQL Server Management Studio Express and re-attach the database(s).. regards Tag: how to excluded the intersection of 2 dimensions ?
  - 5
    - Slow report generation Try to avoid the use of cursor. I had exprienced using cursor in my stored procesure which take 2 hour..after remove the cursor its only takes less than 1 minutes. Tag: how to excluded the intersection of 2 dimensions ?
  - 6
    - Timeout expired on view I keep getting the following error on several views on a new Sql Server 2005 database: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." The views run fine on an Oracle database with the same number of rows. Further when I cut out the underlying query within the view and run it, it works fine. Secondly, is there a way to prievent this error from appearing. I don't care how long the query runs I just want it to work. Tag: how to excluded the intersection of 2 dimensions ?
  - 7
    - How to check if a column is exist? You can also use function COLUMNPROPERTY. use northwind go if columnproperty ( object_id ( 'dbo.orders' ), 'orderid' , 'AllowsNull' ) is null print 'not exists' else print 'exists' go AMB Tag: how to excluded the intersection of 2 dimensions ?
  - 8
    - Snapshot Drilldown Question What is the goal To have a dashboard with jump to links to subreports. But I want the reports to run fast and pop up quickly. If I pass parameters and run each report from the dashboard it takes just too long. What is the problem Is it possible to have a jump to a "Snapshot". I would think that I would want to use the URL jump to feature and call the URL for the snapshot. But I have a snapshot that updates each day so the URL changes. I took a look into the dbo.SpapsshotDate table in ReportServer database and notice a record. I guess I would like to somehow call the record and updated URL on a daily basis. Has anyone done anything like this Or do you have another recommendation Outline: Dashboard (summary of high level info) - Jump to Link to SnapShot (summary by region) - Jump to Link to SnapShot (summary by product) - etc.. Tag: how to excluded the intersection of 2 dimensions ?
  - 9
    - SUM returns incorrect value in SQL Server Mobile Query ... Bug?? This might be a bug in our code. I have trouble reproducing the problem. Can you share the database (or just the schema) so that I can take a look. Thanks Pragya Tag: how to excluded the intersection of 2 dimensions ?
  - 10
    - ForEach loop with Excel Kevin wrote: I have a connection to an Excel file created in my package. Using the ForEach loop I'm attempting to change the connection string on that connection for each excel file located in the directory. I've created a user variable in the ForEach loop that's supposed to be populated with the fully qualified location of each excel file. The data flow component has the error on it before I even execute the package. That component is linked to an OLE DB destination which is a SQL table. You're saying the OLE DB Destination component is giving you an error I would expect the error to be on the Excel Source. Usually this is because whatever variables are used in the expression to control Source have not been initialized with default values. The Source needs design-time access to one of the files so it can read the metadata. Your For Each loop should be placing the fully qualified name of your Excel files into a package-level variable. This package-level variable should have a valid path to an existing file as a default value. You should set up an expression on the Excel connection manager to set the ExcelFilePath property with your variable containing the filename. Tag: how to excluded the intersection of 2 dimensions ?
  - 11
    - Column Naming Rules Hi All, Can someone plz let me know what are the column naming rules in sql2000 Tag: how to excluded the intersection of 2 dimensions ?
  - 12
    - Connection Problem Is "SQL-DEV.sql_dev" the name of a new connection or one of the ones you deleted Tag: how to excluded the intersection of 2 dimensions ?
  - 13
    - Role Playing dimensions Currency Conversion issue Hi, Having issues with Currency Conversion in SSAS when using role playing dimensions of Time. When the Dim Usage in the cube is set for the role playing dimensions, i.e. defining the relation between the Exchange rate fact and the role playing time dims. the base $ measures (no calculations) return a null value. The generated conversion script in Calculations has leaves(Time), not the leaves for each role playing dim. With leaves for each along with leaves(time), the base measures return null. Regards Tag: how to excluded the intersection of 2 dimensions ?
  - 14
    - deletescript for cleanup Hi twinsen We also have databases with more than 100 GB (one > 1 TB). Due to the size of your databases you should work in chunks. If you want to delete 1 mio records in one step you work into risk to get huge LOCKS on your database. My solution for cleanup scripts in our system was to work with two temp tables. First get a big chunk (e.g. 500.000) records which shuold be deleted into a temp table to avoid always to search from main tables by many criteria. After that you can delete in 10.000 record steps with a second temp table which gets its IDs from the main temp table. You should avoid big LOCKING problems and can work with transactions. Here a small pseudy example: Code Block WHILE EXISTS ( SELECT TOP 1 * FROM MainTable WHERE HasToBeDeleted = 1 ) BEGIN INSERT INTO #ManyToBeDeleted -- The value 500.000 is just an excample, you have to evaluate -- the highest value to avoid locks SELECT TOP 500000 priceid FROM MainTable WHERE HasToBeDeleted = 1 WHILE EXISTS ( SELECT TOP 1 * FROM #ManyToBeDeleted ) BEGIN BEGIN TRANSACTION -- 10.000 is just an example, evaluate your best chunk size INSERT INTO #ToBeDeletedChunk SELECT TOP 10000 priceid FROM #ManyToBeDeleted DELETE OT FROM OtherTables OT JOIN #ToBeDeletedChunk TBDC ON OT . priceid = TBDC . priceid DELETE MT FROM MainTable MT JOIN #ToBeDeletedChunk TBDC ON MT . priceid = TBDC . priceid DELETE MTBD FROM #ManyToBeDeleted MTBD JOIN #ToBeDeletedChunk TBDC ON MTBD . priceid = TBDC . priceid DELETE FROM #ToBeDeletedChunk COMMIT TRANSACTION END END You should also add indexes to the both temp tables. Regards Flo Tag: how to excluded the intersection of 2 dimensions ?
  - 15
    - extract distinct information and order the results The possible query, Assumption : The tran_nr are inserted in sequence. Suggestion, instead of using the id use the timestamp. Code Snippet Create Table #data ( [tran_nr] int , [ret_ref_no] int , [msg_type] int , [description] Varchar ( 100 ) ); Insert Into #data Values ( '5111' , '12345' , '420' , 'reversal' ); Insert Into #data Values ( '5112' , '12345' , '200' , 'auths' ); Insert Into #data Values ( '5113' , '15236' , '200' , 'auths' ); Insert Into #data Values ( '5114' , '46587' , '200' , 'auths' ); Insert Into #data Values ( '5115' , '46587' , '420' , 'reversal' ); select * from #data main where exists ( select l . ret_ref_no from #data l inner join #data r on l . ret_ref_no = r . ret_ref_no and l . tran_nr < r . tran_nr where l . msg_type = '420' and r . msg_type = '200' and main . ret_ref_no = l . ret_ref_no ) Tag: how to excluded the intersection of 2 dimensions ?
  - 16
    - How to use a dialog retry timer for doing a web service call Timers are per dialog. Why are messages A and B sent on the same dialog in the first place Is there some correlation between them you left out of the explanation HTH, ~ Remus Tag: how to excluded the intersection of 2 dimensions ?
- Next
  - 1
    - Error while BAM deployment on SQl server express 2005 C:\Program Files\Microsoft BizTalk Server 2006\Tracking>bm.exe deploy-all -Defin itionFile:testtype.bam.xml Microsoft (R) Business Activity Monitoring Utility Version 3.5.1602.0 Copyright (C) 2006 Microsoft Corporation. All rights reserved. Using 'BAMPrimaryImport' BAM Primary Import database on server 'MOHSASLPT13719\S QLEXPRESS'... Deploying Activity... ERROR: The BAM deployment failed. The ExistsOnSQLServer method has encountered OLE DB error code 0x80004005 (Clien t unable to establish connection). The SQL statement issued has failed. The ExistsOnSQLServer method has encountered OLE DB error code 0x80004005 (Clien t unable to establish connection). The SQL statement issued has failed. Tag: how to excluded the intersection of 2 dimensions ?
  - 2
    - how to write this query? If you can create an inline table function to do the job the functon is a reasonable to make the code more readable; however, I would avoid a scalar function because scalar functions can put a lot of drag on a query. Something like this might work: create function dbo.discount ( @cLineDiscTag char(1), @cReceiptsMultiDivFlag char(1), @nTrnsQty integer, @nLineDisc1 integer, @nReceiptsCurrConRate numeric (9,2), @nUnitPrice numeric (9,2) ) returns table as return ( select CASE WHEN @cLineDiscTag = '2' THEN (CASE WHEN @cReceiptsMultiDivFlag = 'D' THEN (@nTrnsQty * @nLineDisc1) / @nReceiptsCurrConRate ELSE (@nTrnsQty * @nLineDisc1) * @nReceiptsCurrConRate END) ELSE (CASE WHEN @cReceiptsMultiDivFlag = 'D' THEN (@nTrnsQty * @nUnitPrice * @nLineDisc1 / 100) / @nReceiptsCurrConRate ELSE (@nTrnsQty * @nUnitPrice * @nLineDisc1 / 100) * @nReceiptsCurrConRate END) END as Discount1 ) Thanks for nudging me Arnie; I'm afraid I kind of fell asleep at the wheel. You have picked me up a couple of times today and I really appreciated it. I am just not fully together today; I don't know why, but I really appreciate you nudging me a couple of times today. :-) Kent Tag: how to excluded the intersection of 2 dimensions ?
  - 3
    - Out of Memory Error when running a Select Statement Hi all, In the object Explorer in SQL Server 2005 Management Studio, if I right click on the table I want to open and choose open, I eventually get an out of memory error with an error source: MSCORLIB. This table contains 40m records, but when it opens the table it goes past the 40m and wants to open 83m records, and then eventually comes up with an out of memory error. The properties of the table indicates that there are 40m records, but when you open the table, at the bottom, where it says retrieving data, it is counting of numbe rof records that surpass the 40m total. Is it because the database is too big, or is there a way to configure SQL Server 2005 to use X amount of memory Regards Tag: how to excluded the intersection of 2 dimensions ?
  - 4
    - Change filename for flat file connection manager at runtime Hi Rishi, I did something to fix it then, but I would't solve it the same way today. The following example gets data from a file with a date in it. The date is always the current date so the Connection string needs to be updated to be able to read from the file. I hope this gives you an idea of how to solve your problem! Regards, Sara ----------------------------------------------------------------------------------------- Script Task Connection Manager Name: L:\VPKBA\Laddning\EDBKjerne\konto_YYYYMMDD.txt File Name: L:\VPKBA\Laddning\EDBKjerne\konto.txt Imports System Imports System.IO Imports System.Data Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main () Dim theDate As String Dim i As Integer strPath = "L:\vpkba\laddning\EDBkjerne\" theDate = Now.Year & IIf( CInt (Now.Month) < 10, "0" & Now.Month, Now.Month).ToString & IIf( CInt (Now.Day) < 10, "0" & Now.Day, Now.Day).ToString For i = 0 To Dts.Connections.Count - 1 If Dts.Connections(i).Name.Contains( "YYYYMMDD" ) Then Dts.Connections.Item(i).ConnectionString = Dts.Connections.Item(i).Name.Replace( "YYYYMMDD" , theDate) End If Next Dts.TaskResult = Dts.Results.Success End Sub End Class Tag: how to excluded the intersection of 2 dimensions ?
  - 5
    - Get previous sale for a customer's sale? Sorry, but I'm afraid I don't follow what you are trying to do here. Could you provide a little more detail and/or provide a table of expected results Thanks, Bryan Tag: how to excluded the intersection of 2 dimensions ?
  - 6
    - Trigger to insert multiple records into related table You might be able to get by with something like this: create trigger dbo.iTr_cat_control on dbo.Cat_Control for insert as begin create trigger dbo.iTr_cat_control on dbo.Cat_Control for insert as begin -- ------------------------------------------------------------ -- This trigger works by taking the cross product of each -- row inserted into the cat_control table with each of the -- 4 possible answers. This avoids a looping or 2-level -- level looping process. -- ------------------------------------------------------------ insert into dbo.det_control ( cat_control_id, cat_control_name, det_control_answer ) select i.cat_control_id, i.cat_control_name, a.answer from inserted i cross join ( -- replace the 'Case' with whatever it should be select 1 as caseId, 'First Answer' as answer union all select 2 as caseId, 'Second Answer' as answer union all select 3 as caseId, 'Third Answer' as answer union all select 4 as caseId, 'Fourth Answer' as answer ) a end go insert into cat_control values (1, 'This is a test.') select * from det_control where cat_control_id = 1 -- ----------- Sample Output: ------------ -- det_control_id cat_control_id cat_control_name det_control_answer -- -------------- -------------- ------------------------------ ------------------------------ -- 1 1 This is a test. First Answer -- 2 1 This is a test. Second Answer -- 3 1 This is a test. Third Answer -- 4 1 This is a test. Fourth Answer Tag: how to excluded the intersection of 2 dimensions ?
  - 7
    - Split - Comma seperaed values Try: Code Snippet create table dbo . t1 ( ID int not null unique , DepartmentNames varchar ( 128 ) ) go insert into dbo . t1 values ( 1 , 'IT,Finance' ) insert into dbo . t1 values ( 2 , 'Accounting, Finance, IT' ) go -- Idea from Itzik Ben-Gan to generate a table of numbers on the fly WITH L0 AS ( SELECT 1 AS c UNION ALL SELECT 1 ), L1 AS ( SELECT 1 AS c FROM L0 AS A , L0 AS B ), L2 AS ( SELECT 1 AS c FROM L1 AS A , L1 AS B ), L3 AS ( SELECT 1 AS c FROM L2 AS A , L2 AS B ), Nums AS ( SELECT ROW_NUMBER () OVER ( ORDER BY c ) AS n FROM L3 ) -- Idea from SQL Server MVP Anith Sen (using table of numbers to split the string) SELECT t . [ID] , row_number () over ( partition by [ID] order by n . n ) as position_in_list , ltrim ( rtrim ( substring ( t . DepartmentNames , n . n , charindex ( N ',' , t . DepartmentNames + N ',' , n . n ) - n . n ))) AS [Value] FROM Nums as n inner join dbo . t1 as t on n . n <= convert ( int , len ( t . DepartmentNames )) AND substring ( N ',' + t . DepartmentNames , n , 1 ) = N ',' order by t . [ID] go drop table dbo . t1 go Arrays and Lists in SQL Server 2005 http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum AMB Tag: how to excluded the intersection of 2 dimensions ?
  - 8
    - Cannot Pull table with RDA Change your OLEDB connectionstring: Dim rdaOleDbConnectionString As String = "Provider=SQLOLEDB;Data Source=SKYWALKER;Initial_ Catalog=ristobar;User ID=MobileUser;Password=pippo;" or Dim rdaOleDbConnectionString As String = "Provider=SQLOLEDB;Data Source=192.168.0.199;Initial_ Catalog=ristobar;User ID=MobileUser;Password=pippo;" Tag: how to excluded the intersection of 2 dimensions ?
  - 9
    - How to connect to remote server Very few hosting providers will allow you to connect to their server using SSMS; rather they provide their own set of management tools accessable through their site. If you log into your account you will likely find a set of links that take you to the management console for your hosted database. From there you should be able to load the script file that you saved from the DPW and run it to create your objects in the hosted database. As others mention, contact your hosting provider if you have questions about managing your hosted database. Mike Tag: how to excluded the intersection of 2 dimensions ?
  - 10
    - SQL 2005 Standard on Vista RTM Samuel Lester - MSFT wrote: Can you read through this thread and see if it applies It suggests that there is a WMI configuration issue. http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=124864&SiteID=1 Thanks, Sam Hi, Yes I went through the steps with the WMI batch etc yesterday without any difference to the outcome. Thanks. Tag: how to excluded the intersection of 2 dimensions ?
  - 11
    - Performance-Issue with variables If you are interested, here is a more 'indepth' exclication of what happens, and how to avoid 'parameter sniffing'. Stored Procedure �CParameter Sniffing http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx http://tinyurl.com/f9r2 http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/17/444.aspx Tag: how to excluded the intersection of 2 dimensions ?
  - 12
    - cannot connect to server This is a security related problem of the Windows Server or the IIS Worker process, see more details here: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=789797&SiteID=1 Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: how to excluded the intersection of 2 dimensions ?
  - 13
    - using encryption via an asp site. how is it done You can provide access to data via a view that automatically decrypts using decryptbykeyautocert, for example. You can then group your Admin users in a role and grant permissions to that role to use the key. Admin users would then be able to decrypt the data and see it; regular users would not be able to. Thanks Laurentiu Tag: how to excluded the intersection of 2 dimensions ?
  - 14
    - The '[CDbl]' function does not exist. I once had a weird situation on an IA64 machine where I could only get the VBA functions working by prefixing them with VBAMDX instead of VBA (VBAMDX is the name of the assembly on the server). I found that this worked on both IA64 and 32bit editions. Not sure why this is, as far as I understand the VBA prefix is meant to work on IA64, but I don't have any other IA64 servers that I can check this against. I'm not sure if IsNull() exists as a function or not, but you will find that the IsEmpty() function is equivalent. Tag: how to excluded the intersection of 2 dimensions ?
  - 15
    - How to acces report of other system from my system. Mahender, Your report server URL should be http://<ServerName>/ReportServer . In order to view your report, the user will need to be in the 'Browser' role. Hope this helps. Jarret Tag: how to excluded the intersection of 2 dimensions ?
  - 16
    - FTP Connection problem with "The Password was not allowed" error message I had set the PackageProtection to EncryptSensitiveWithPassword, but that didn't work either. As for an incorrect password, this can't be correct, because when I test the FTP connection it works fine Any other thoughts Thanks D Tag: how to excluded the intersection of 2 dimensions ?

Constantijn Enders

Hi,

I have the following simplified problem.

I have a fact table and two dimension tables, colors and weekdays.

I can make a query to select all except red and all except on monday but I like to make a query to select all except red mondays

They query tool doesn't give you really the options.

Any suggestions

Constantijn Enders

Top

Re: how to excluded the intersection of 2 dimensions ?

Christopher Webb

You can do it in MDX, but only a few query tools will allow you to construct queries like this. I think vendors tend to refer to this capability as 'asymmetic sets'; I can only think of one tool I've seen recently that did this (Intelligencia - http://www.it-workplace.co.uk/i4wfeatures.aspx) but I'm sure that if you hunt around there will be others. Does anyone else know of one

Here's an MDX query to prove it's possible:

select

measures.[internet sales amount] on 0,

except(

[Date].[Day Name].[Day Name].members

[Product].[Color].[Color].members

,{([Date].[Day Name].&[2], [Product].[Color].&[Red])}

) on 1

from [Adventure Works]

HTH,

Chris

Top