Analysis :: Partitioning Sets Of Data Dynamically
Jul 17, 2015
I have a situation where i have a transactional fact table which consists of date, row type, order number and value. Â Simple example below
Date, RowType, OrderNo, Value
01-May, New, A1, 100
01-Jun, Change, A1, -10
01-Jul, Invoiced, A1, -90
What I need to be able to do is somehow select based on a day, the total value of open orders. Â I have tried to do this in the database but it becomes fixed and quite cumbersome (this is a simplified example in reality i have line information and line component information).I am not hugely skilled with MDX and SSAS but know there are some semi-additive functions i want somebody to be able to pick a day and have the total value of only open orders.
View 2 Replies
ADVERTISEMENT
Nov 3, 2004
All,
Is there a way within analysis services to perform a partition on an automated basis? Not sure if this is necessarily the best forum for my question. Apologies if it falls outside the scope of SQL server.
Any help is appreciated.
Isaac
View 1 Replies
View Related
May 19, 2011
I have some Named Sets that I want to translate. I have done exactly the same way as with the Calculated Members but the Sets don´t use the translations? Any ide why?
View 2 Replies
View Related
May 14, 2015
I am using following Query to get data from Cube for some dates and Quarters at single time, but I am getting an error "Two sets Specified in the UNION function have different dimensional".
WITHÂ
MEMBER [Measures].[Data Value1]Â
AS [Measures].[Average Value]
SELECTÂ
{UNION(
([DimPeriodType].[Period Type Key].&[1],[DimDate].[Date Key].&[20150428],[DimEnergyCalculated Ratio].[Calc Ratio Key].&[1],[Measures].[Data
[code]....
View 3 Replies
View Related
Oct 26, 2015
I have the following MDX Query:
Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
[Cost Centres].[Cost Centre].[All].Children
[Locations].[Locations].[All].Children
on 1
From MyCube
which produced the following table:
Division
 Cost Centre
 Location
 Dollars
AA
1
X
$30.00
[code]....
What I am hoping to do is create a set out of the Union of specific values in the [Cost Centres].[Cost Centre] and [Locations].[Locations] hierarchies into a single set and use that new set in my MDX query across the columns.
Using the table and query from above, I have the following conditions that would determine the value in the set (lets call the new set 'NewSet')
When Cost Centre = 1 and Location = X Then "CustomType1"
When Cost Centre = 1 and Location = Y Then "CustomType2"
When Cost Centre = 1 and Location = Z Then "CustomType3"
When Cost Centre = 2 and Location = Y Then "CustomType4"
When Cost Centre = 2 and Location = Z Then "CustomType5"Else "Default"
Then, if I was to execute the new query:
with
set NewSet as "Some Unknown Magic Here"
Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
{NewSet}
on 1
From MyCube
I would end up withÂ
Division
 NewSet
Dollars
AA
CustomType1 Â
$166.64
AA
CustomType3Â
$64.24
BB
[code]....
View 2 Replies
View Related
May 25, 2015
Is it possible to add, remove or modify a named set in an already deployed cube? I don't want to modify anything except the sets. If it is possible at all, are there any example of doing it?Second is technical. I have redefined set "myRowsSet" to "myRowsSet2". What I did is I removed the calculated members and placed their code directly inside the set. Why isn't it working?
with
-- road bikes in france
member [Product].[Product Line].myMemberRoad as aggregate(
existing(
intersect(
[Product].[Product Line].[Product Line].members
,[Product].[Product Line].&[R] -- road
[code]...
View 2 Replies
View Related
May 4, 2015
Actually I want to do distinct sum on a measure group, please find the below table as sample
XL Measure group
LKÂ Â Â Â OKÂ Â Â Â Â Amount
1Â Â Â Â Â Â Â 10Â Â Â Â Â Â Â Â 100
1Â Â Â Â Â Â Â Â 11Â Â Â Â Â Â Â Â 100
3Â Â Â Â Â Â Â Â Â 30Â Â Â Â Â Â Â 250
3Â Â Â Â Â Â Â Â Â 31Â Â Â Â Â Â Â 250
3Â Â Â Â Â Â Â Â Â 32Â Â Â Â Â Â Â 250
For the above measure group two dimensions have relationships, One is L dimension which is having relationship with XL on LK and One is O dimension which is having relationship with XL on OK. If I drag L dimension attributes  it should show results as below
LK LName Amount
1Â Â Â Â AÂ Â Â Â Â Â Â Â 100
3Â Â Â Â CÂ Â Â Â Â Â Â Â Â 250
But above results are coming as below
LK LName Amount
1Â Â Â Â AÂ Â Â Â Â Â Â Â 200
3Â Â Â Â CÂ Â Â Â Â Â Â Â Â 750
If I drag O dimension attributes along with L dimension, it should show results as below.
LK  LName  OK     OKName  Amount
1Â Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â 10Â Â Â Â Â Â XYZÂ Â Â Â Â Â Â Â 100
1Â Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â 11Â Â Â Â Â Â UVWÂ Â Â Â Â Â Â 100
3Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â Â Â 30Â Â Â Â Â Â PQRÂ Â Â Â Â Â Â Â Â 250
3Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â Â Â Â 31Â Â Â Â Â KLMÂ Â Â Â Â Â Â Â 250
3Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â Â Â Â 32Â Â Â Â Â TUVÂ Â Â Â Â Â Â Â 250
I used formula Measures.Amount/Measures.Count, this formula is not showing correct results when I don't drag any dimensions, it is showing results for All member as 425, but it should show as 350.
So I made a same change ([L].[LK].Currentmember, Measures.Amount)/([L].[LK].Currentmember,Measures.Count), this worked fine but performance is very low and so stopped working on this.
Atlast I did the measure group like this
LK    OK     LAmount  OAmount
1Â Â Â Â Â Â Â 10Â Â Â Â Â Â Â Â 100Â Â Â Â Â Â Â 100
1Â Â Â Â Â Â Â Â 11Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â 100
3Â Â Â Â Â Â Â Â Â 30Â Â Â Â Â Â Â 300Â Â Â Â Â Â Â 300
3Â Â Â Â Â Â Â Â Â 31Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â 300
I want to show Measures.LAmount when only L dimension is querying and want to show OAmount when both L dimension and O dimension are querying. Is this possible ?
View 3 Replies
View Related
Jul 7, 2015
We have a date dimension which spans till 2099 and there are future projection numbers (under measures). I want to limit the data for Future projections only to 5 years from today by default. Is there a way to do this with in the cube. I understand that this can be done using MDX but since we use excel to view the data from the cube it needs to be controlled with in the cube.
View 4 Replies
View Related
May 22, 2008
Hi,
Pls advice on how to partition an existing non partitioned table having a fair amount of data.
Regards
RT
View 3 Replies
View Related
Apr 23, 2015
I'm currently working on a BI architecture for a customer, and consider to propose the Power BI data catalog as a data distribution layer. The customer will use Power BI, but also has other BI tools.
Are data sets in the data catalog available to other clients than Power Query alone? E.g. are there OData feed endpoints available? If not, what would be the best way to give other tools access to the data?
View 3 Replies
View Related
Jan 30, 2007
Hello,
I have a table containing 3 columns Department Name, RiskScenario and Cost. I am trying to create a data extract that contains the top 3 Risk Scenarios (sorted by Cost) per Department.
I tried using this sql statement in MSQuery but it doesn't work. Any ideas where I'm going wrong or if there is a simpler way to do this?
Select * from (
Select DepartmentName, `Risk Scenario`, Cost, row_number() OVER (PARTITION BY DepartmentName order by Cost) rn
FROM 'Departmental Risks`) where rn <=3
Please help. Just can't figure this out!
Meera
View 7 Replies
View Related
Jun 15, 2007
Could I ask how to spit the data into training and validation sets when doing data mining?
Thanks
View 1 Replies
View Related
Feb 18, 2013
It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?
View 5 Replies
View Related
Mar 9, 2008
Hi, I was wondering how it is posible to join three data sets from different data flows into one txt file.
Let's explain a little more:
I have 3 dataflows. Each of them connect to sql server and and by a SQL command, they bring data into SSIS.
Each SQL command differ between them. So each data set have different columns (they dont have the same format). Also the amount of columns differ between each one.
What I need is to join the three data sets into one txt file. How can I do this? It is posible to join them with different data set formats into a txt file?
Is this the best way to join different data? It is better to use as many OLE DB Sources are needed instead of different data flows?
Thanks for your help!
View 7 Replies
View Related
Jul 14, 2015
We have a requirment, we have different databases in different servers, we need to syncronize the data in data bases in different server. created Partitioned on these tables.Â
We tried some options:
1) Snapshot replication
2)CDC (Change Data Capture)Â
What are the best approaches to achieve the same.
View 2 Replies
View Related
Mar 20, 2008
Hi,
I'm currently trying to retrieve results from a large dataset, there are over 45000 records and I need to use them all to peform counts etc. I have set up views, but my page is still being returned slowly, is there anything I can do to speed this up?
Thanks
Gemma
View 2 Replies
View Related
May 7, 2008
I am trying to query one table and get two different timeperiods of data, I am summing monthly totals to provide a running year total, but I also need last month's total in a seperate column. This is what I have so far but the subquery makes me group it which provides duplicate grouping.DECLARE @LASTPD AS INT
SET @LASTPD = (SELECT MAX(LASTPERIOD) FROM TABLE)
SELECT NAME,
POST_PD AS [MONTH],SUM(CHARGE_AMOUNT) AS MONTHLY_$,
LASTMONTH.LAST_MONTH,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLE INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME
WHERE POST_PD = @LASTPD
AND TABLE2.NUM= 539
GROUP BY NAME) AS LASTMONTH
INTO #TEMP_SAFROM TABLE
INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLEWHERE TABLE2.NUM = 539
GROUP BY NAME, POST_PDORDER BY NAME, POST_PD
SELECT NAME,
LAST_MONTH,
CAST(SUM(MONTHLY_$)AS DECIMAL(20,2)) AS YEARLY_$
FROM #TEMP_SA
GROUP BY NAME
ORDER BY NAME
View 13 Replies
View Related
Mar 12, 2008
Hi All,
I would like to match two sets of data. I have setup a view of data that contains a group of customers and their details. I want to view this data, but also find these customers in another table based on matching their surname and date of birth, then retreive the information stored on these customers from the second table.
Does anyone have any suggestions how i would go about doing this?
Thanks in advance
Humate
quote:Originally posted by Michael Valentine Jones
It takes real skill to produce something good out of a giant mess.
View 2 Replies
View Related
Jul 23, 2005
I have the following situation. One set of data has 274 rows (set2)and anther has 264 (set1). Both data sets are similar in structure aswell as values for both of them were extracts from the same parenttable. Hope the info would substitute DDL. I need to find the "gap"rows between these two sets.Attempted to run a query likeselect count(*)from set2where not exists(select *from set1)did not yield what I desired. What else to try?TIA.
View 12 Replies
View Related
Mar 13, 2007
Hi
I have a matrix whos colunm group is filed by Dataset1,
now i want to add naother colunm group,but using the Dataset2
can I use two different dataset for a matrix,
for differnt colunm group
please help me in this regards
thanks
View 2 Replies
View Related
Apr 28, 2015
I have two tables - one with sales and another with payments against those. The payment may not match the exact amount of sales and I have to use FIFO method to apply payments. The payment month must >= sales month.
How can i write a query to do this? Examples are as below.
Table 1
Sales Sale DateSale Amt
1Jun-141200
2Oct-142400
3Dec-14600
4Feb-1512000
Table 2
Pay Month Pay YearPay Amount
5 2014 300
6 2014 1000
10 2014 500
11 2014 2000
12 2014 300
1 2015 900
create table tbl1
(
saleNo int
,saleDate date
,saleAmt float
)
insert into tbl1 (saleNo, saleDate, saleAmt)
[Code] ....
View 5 Replies
View Related
Feb 8, 2007
I've seen that sometimes is better to split the table into a test dataset and a training dataset, and I'll appreciate if anyone can explain why is this...
thanks
Santiago Aceñolaza
Argentina
View 4 Replies
View Related
Mar 24, 2008
Is there a way to put more then one data set in a list.
I have a report that has three data sets with three tables. Now i want to show each report by Region, per page. So you can view the same stuff for each region seperately, instead of all together. Is there a way to do this. Where i dont have to go back in my code, and find a way to link everything together, so its in one data set .
View 3 Replies
View Related
Sep 3, 2007
Hi,
I'm using a matrix report where in i want to use two datasets in the same report. How can i make the dataset dynamic for a single report.
Regards
View 1 Replies
View Related
May 12, 2008
Hi,
I'm trying to created a report.
Final report looks like this.
Total Loans/Lines (#)
13,283
Total Commitments ($ MM)
$1,703
Total Outstandings ($ MM)
$1,175
A
B
C
D
F
Bankruptcy
0
$0
$0
0.00%
0.00%
Charge Off
0
$0
$0
0.00%
0.00%
Source table looks like this;
Bankruptcy
0
Charge Off
0
CLTV
131
DSR
102
Exc Total
265
FICO
7
Foreclosure/Repossession
Grand Total
13283
Loan Amount
32
Column D = A Bankrupcy(0) / Total Loans/Lines #(13283)
But it does not let me to use report expression as its not in the same scope.
Can anyone tell me how to do this calculation ?I was trying to use a report expression but it seems like not working.
Thanks
View 15 Replies
View Related
Feb 8, 2008
Hi,
I have designed a contact manager with Data Grid Control bound to a Data Set.
When the application closes, data from Data Set is written to XML file and when application opens, data from XML file is loded into Data Set and is show in Data Grid control.
Contacts in my application can exceed over 1,000
So, Is Data Set capable of handling lot of data very efficiently in memory?
Please advise
View 3 Replies
View Related
May 25, 2006
Hello,
I am using existing code, which I am trying to convert from using MS Access to SQL Server 2005...
The data set works fine with MS Access database, however when executing with SQL Server 2005 as data source, it generates the following error:
"..The data types ntext and nvarchar are incompatible in the equal to operator..."
in this line:
count = adapter.Update(dataset);
Not sure what should I look for since data sets are new to me.. Where should I check to fix this problem? I have noticed that the table has two columns with nvarchar...
View 11 Replies
View Related
Aug 27, 2013
I have two queries that generate two different datasets. One is a count of memebers, and the other is count of admits. I need to generate a calculated field from the two data sets called admits per 1000, which is essential the count of admits/counts of members *12000 I was able to calculte admits per 1000 easily in excel, however I need some insight on how to do is SQL.
Below are my queries from the two datasets.
MemberMonths dataset:
Select
factMembership.BusinessUnitCode,
EffectiveCCYYMM,
ISNULL(count(Distinct MemberId),0) As MemberCount
From factMembership
[Code] ....
Admits dataset:
SELECT
Factadmissions.BusinessUnitCode,
factAdmissions.AdmitCCYYMM,
ISNULL(Count(AdmitNum),0)As [Count of Admits]
FROM factAdmissions
[Code] ...
View 6 Replies
View Related
Jun 19, 2015
I created Data Collection in wrong DB, how can I change the DB or return to default(as it came with clean version of SS) ?
View 3 Replies
View Related
Jul 26, 2007
Hello and thanks in advance.
I was wondering if anyone has ever written a chart with multiple datasets.
I need to be able to show sales dollars inflow by order date on one line and on the other needs to be sales dollars delivered by delivery date. So the all sections Values, Category groups, and Series Groups in the chart will be from 2 different datasets.
I have tried but it will not allow aggreates in the series groups.
Any Ideas would be greatly appreciated.
Thanks, Leo
View 1 Replies
View Related
Mar 9, 2007
I need to copy data from TableA to TableB (>5 millions rows). The two are in the same database.
What is the best way of doing this?
I was thinking about using a simple INSERT INTO ... SELECT statement. Is there a faster way to do it with SSIS?
Thanks
View 13 Replies
View Related
Oct 1, 2007
Can I make a calculated field by using two fields from different data sets?(I'm talking about SSRS data sets)
I tried to do that. But I got a error message.
"Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."
Please can some one help me out?
View 5 Replies
View Related
Mar 6, 2007
Hi there everyone. I have a stored procedure called “PagingTable� that I use for performing searches and specifying how many results to show per ‘page’ and which page I want to see. This allows me to do my paging on the server-side (the database tier) and only the results that actually get shown on the webpage fly across from my database server to my web server. The code might look something like this:
strSQL = "EXECUTE PagingTable " & _
"@ItemsPerPage = 10, " & _
"@CurrentPage = " & CStr(intCurrentPage) & ", " & _
"@TableName = 'Products', " & _
"@UniqueColumn = 'ItemNumber', " & _
"@Columns = 'ItemNumber, Description, ListPrice, QtyOnHand', " & _
"@WhereClause = '" & strSQLWhere & "'"
The problem is the stored procedure actually returns two result sets. The first result set contains information regarding the total number of results founds, the number of pages and the current page. The second result set contains the data to be shown (the columns specified). In ‘classic’ ASP I did this like this.
'Open the recordset
rsItems.Open strSQL, conn, 0, 1
'Get the values required for drawing the paging table
intCurrentPage = rsItems.Fields("CurrentPage").Value
intTotalPages = rsItems.Fields("TotalPages").Value
intTotalRows = rsItems.Fields("TotalRows").Value
'Advance to the next recordset
Set rsItems = rsItems.NextRecordset
I am trying to do this now in ASP.NET 2.0 using the datasource control and the repeater control. Any idea how I can accomplish two things:
A) Bind the repeater control to the second resultset
B) Build a “pager� of some sort using the values from the first resultset
View 3 Replies
View Related
