.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

MDX Query Hitting All Partitions (But Only Sometimes!)

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :Sql Server
 

In my Sales Detail cube, I've partitioned checks by fiscal period (which spans 28 days), going back a few years.

I've got my [Current Day] and [Same Day Last Year] defined as such:

CREATE SET CurrentCube.[Current Day] AS
TAIL(FILTER([Date].[Calendar].[Date].Members, [Measures].[Check Cnt] > 0), 1);
CREATE SET CurrentCube.[Same Day Last Year] AS
ParallelPeriod([Date].[Calendar].[Year], 1, [Current Day](0));

I've got three sets based on [Same Day Last Year]:

CREATE SET CurrentCube.[Next 14 Days of Last Year] AS
([Same Day Last Year](0).Lag(-1) : [Same Day Last Year](0).Lag(-15));
CREATE SET CurrentCube.[Next 28 Days of Last Year] AS
([Same Day Last Year](0).Lag(-1) : [Same Day Last Year](0).Lag(-29));
CREATE SET CurrentCube.[Next 56 Days of Last Year] AS
([Same Day Last Year](0).Lag(-1) : [Same Day Last Year](0).Lag(-57));

Okay, so when I run this MDX:

SELECT
{ [___________________] } ON COLUMNS,
{ [Measures].[Check Cnt] } ON ROWS
FROM [Sales Detail]
WHERE ( [Store].[Store Alignment].members )

Where the blank is filled in with those named sets, here are my results:

[Current Day]: hits one partition
[Same Day Last Year]: hits one partition
[Last 14 Days of Last Year]: hits one partition
[Last 28 Days of Last Year]: HITS ALL PARTITIONS IN TH


View Complete Post


More Related Resource Links

How can I tell *IF* AND *WHAT* partitions are being used in a query?

  

Setup

  • Cost of Threshold for Parallelism : 5
  • Max Degree of Parallelism : 4
  • Number of Processors : 8
  • SQL Server 2008 10.0.2.2757
  • I have a query with many joins, many records. 
  • The design is a star. ( Central table with fks to the reference tables )
  • The central table is partitioned on the relevant date column.  
  • The partition schema is split by days
  • The data is very well split across the partition schema - as judged by comparing the sizes of the files in the filegroups assigned to the partition schema
  • Queries involved have the predicate set over the partitioned column. such as ( cs.dte >= @min_date and cs.dte < @max_date )
  • The values of the date parameters are a day apart @ midnight so, 2010-02-01, 2010-03-01
  • The estimated query plan shows no parallelism

a) This question is in regards to Sql Server 2008 Database Engine.  When a query in the OLTP engine is running, I would like to see / have the sort of insight one gets when profiling an SSAS Query using Progress End event - where one sees something like "Done reading PartititionXYZ". 

b) if the estimated query plan or the actual query plan shows no parallel processing does that mean that all partitions will be / were checked / read?

c) suggestions?

How can I tell *IF* AND *WHAT* partitions are being used in a query?

  

Setup

  • Cost of Threshold for Parallelism : 5
  • Max Degree of Parallelism : 4
  • Number of Processors : 8
  • SQL Server 2008 10.0.2.2757
  • I have a query with many joins, many records. 
  • The design is a star. ( Central table with fks to the reference tables )
  • The central table is partitioned on the relevant date column.  
  • The partition schema is split by days
  • The data is very well split across the partition schema - as judged by comparing the sizes of the files in the filegroups assigned to the partition schema
  • Queries involved have the predicate set over the partitioned column. such as ( cs.dte >= @min_date and cs.dte < @max_date )
  • The values of the date parameters are a day apart @ midnight so, 2010-02-01, 2010-03-01
  • The estimated query plan shows no parallelism

a) This question is in regards to Sql Server 2008 Database Engine.  When a query in the OLTP engine is running, I would like to see / have the sort of insight one gets when profiling an SSAS Query using Progress End event - where one sees something like "Done reading PartititionXYZ". 

b) if the estimated query plan or the actual query plan shows no parallel processing does that mean that all partitions will be / were checked / read?

c) suggestions?

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

  
If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

  
Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

  
If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

debugger is not hitting the breakpoint

  

I made a class library,all dlls of all the helping class library.for debugging it put the breakpoints at specifiv locations.It worked fine for 2 times but at third time it jumped the breakpoints, I dont know why????
I displayed the strings before and after the breakpoints to check that is it actualy going to the breakpoint,!! yes it is going to the breakpoint but not stopping on it. I did'nt made any changes to any settings.
On google I found something in tool-->Options-->general-->check the unhandaled execptions but I m using VS 2005 , I could not found any check box regarding unhandeled execptions or breakpoints.
I tryied these methods also 
1. Using precision guided missiles: Delete the .pdb files in your obj and bin folders. Recompile. Run.
2. Carpet bomb all .dlls: Delete and reload all the referenced .dlls (Like your class projects)
3. Release a WMD: delete the contents of the very obj and bin folders itself so that all  .pdbs and .dlls are annihilated. Reload the .dlls required and give it a shot.
4. VS.Net magic: Close VS.Net and restart. Rebuild. Run.

but all is fruitless
Please help me!!!!!!!!!!!!!!!!!!!!!!!!


sql query that don't run

  

Hello....

I'm going crazy... i've got a piece of code that don't update the database... Could somebody tel me why it doesn't run??

Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdstring As String = "Update Corsi Set [Contenuto]='" & TxtText.Content & "' Where [ID]='" & Request.QueryString("Id").Replace("'", "") & "'"
        conn = New SqlConnection(SqlAnni.ConnectionString)
        cmd = New SqlCommand(cmdstring, conn)

Need help with a linq to sql query

  

I want to display some stats in a listview contril.  The table contains a record for every impression and for every click, and every record has a datetime field for the time and date of the impression of click.  I want to be able to display how many impressions and clicks there were in a given day and hour, something like below:


Date              Time                              Impressions    Clicks
8/20/2010                                               450              14            
                     Midnight -1:00am               150               5
&n

How to send an email with information in query string

  

First I will say I googled (Binged to make Bill happy) for this but could not find anything, perhaps I am not stating what I need correctly;

I have a website that a customer creates orders (for a printing company) and after the order, I would like to email the customer a link to view the status of their order;

I need to send the CustomerID in the link so on the returning page if a CustomerID is detected in the querystring, I "auto login" the client and open the "Dashboard" for the order page for that customer.

Can anyone point me in the right direction as to how to send the link and how to parse the querystring to get the values from the link;

Thank you if you can point me in the right direction.


MDX Query parameter from SSRS

  

Hello,
I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )

 

 


Get ServerReport selected Parameter value to use in vb query

  

I'm using ReportViewer in Asp.net 2.0 to view a SSRS report. I need the value of the selected parameter to use in a vb query. The parameters are populated on the server and its a single selection. Doing searches I've come across  ReportViewer1.ServerReport.GetParameters() but I can't figure out if I can use this to determine which value the user has selected in the parameter dropdownlist. Any help would be appreciated.


Need Help in LINQ query for group By with chunks of record

  

I am assigning and unique id [strShipperIdSequence] on my List on bases of some properties which are grouped together uniquely.
Now what i needed is that my group should be further break down to some maximum amount of chunks.[Let say 10]
that mean's even i am having Same value in 12 records i should get 2 groups[I of 10 items and other of 2 items]

var uniqueGroups = objMdbContentInfoList.GroupBy(p => new
{
p.CON_ENTRY_POINT,
p.APPTType,
p.CON_CONTAINER_ISSUE_DATE
}).Select(g => g.First()).ToList();
foreach (var objUnique in uniqueGroups)
{
string strShipperIdSequence = APIGlobalMethods.GetShipperRequestID();
foreach (MdbContentInfo obj in objMdbContentInfoList.FindAll(h => (h.CON_ENTRY_POINT == objUnique.CON_ENTRY_POINT &&
h.APPTType == objUnique.APPTType &&
h.CON_CONTAINER_ISSUE_DATE == objUnique.CON_CONTAINER_ISSUE_DATE)))
{
obj.Shipper

Query an arraylist of array list using linq

  

I have an array list called Customer. I t holds a list of array list which has the account details.

proj.customer -> collection of arraylist of type customer

proj.customer.account -> collection of arraylist of type account


Account array list holds type account and it has a poperty account balance. How can I query all accounts which has account balance  >100


I tried the following one

var Accountlist= from Account accountdetails in proj.customer.account

where accountid.balance >100

select accountdetails.id

But it is always throwing an error saying cannot cast type customer to type account

Please help


Basic Instincts: Increase LINQ Query Performance

  

Jared Parsons demonstrates a technique to achieve maximum performance from LINQ queries on large sets of data in order to create a responsive user interface.

Jared Parsons

MSDN Magazine August 2008


Data Points: Standard Query Operators with LINQ

  

LINQ to Objects and LINQ to Entities have an arsenal of Standard Query Operators that operate on sequences to perform a wide variety of operations.

John Papa

MSDN Magazine March 2008


Basic Instincts: Query Comprehensions

  

Scott Wisniewski digs into LINQ and builds a custom query provider to create queries in real-world apps.

Scott Wisniewski

MSDN Magazine August 2007


VISUAL BASIC: Unleash The Power Of Query In Visual Studio "Orcas"

  

LINQ, coming in the next version Visual Studio, codename "Orcas", adds type-safe data querying to .NET languages.

Ting Liang and Kit George

MSDN Magazine June 2007


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend